[AccessD] Checking for Records in Recordsets

David Emerson newsgrps at dalyn.co.nz
Mon Apr 14 13:51:52 CDT 2014


Bill, one of the recordsets happened to be empty because the filter meant
that no records were returned in the select statement.

Regards

David

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Bill Benson
Sent: Monday, 14 April 2014 8:58 p.m.
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Checking for Records in Recordsets

I was going to suggest setting it equal to a recordset with no record which
seems like what you did.

When you say "the empty recordset" is than an actual property or do you mean
one of the recordsets that just happened to be empty?

Thanks.

And thanks for explaining why me! Recordset was being set.
On Apr 14, 2014 3:32 AM, "David Emerson" <newsgrps at dalyn.co.nz> wrote:

> Solved it.  Instead of trying to delete the recordset property if 
> there were no records I allowed it to be set to the empty recordset.  
> This of course cleared the old list and filled it with no records.
>
>
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of David 
> Emerson
> Sent: Monday, 14 April 2014 7:09 p.m.
> To: 'Access Developers discussion and problem solving'
> Subject: Re: [AccessD] Checking for Records in Recordsets
>
> Thanks Darryl,
>
> Tried variations such as
>         Me!cboSearch.RowSource = ""
>         Me!cboSearch.RowSource = vbNullString
>         Set Me!cboSearch.Recordset = vbNullString
>
> Tried adding Me!cboSearch.Requery after the above line.
>
> None changed the combo list.
>
> Still scratching my head.
>
> Regards
>
> David Emerson
> Dalyn Software Ltd
> Wellington, New Zealand
>
>
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Darryl 
> Collins
> Sent: Monday, 14 April 2014 6:41 p.m.
> To: Access Developers discussion and problem solving
> Subject: Re: [AccessD] Checking for Records in Recordsets
>
> Off the top of my head you can drop the "SET".  Did you try using 
> "Rowsource" for the combo instead?
>
> Me!cboSearch.Rowsource = ""
>
> Should work.
> Or try
>
> Me!cboSearch.Rowsource =vbnullstring
>
>
> Hope that helps
> Cheers
> Darryl.
>
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of David 
> Emerson
> Sent: Monday, 14 April 2014 3:58 PM
> To: 'Access Developers discussion and problem solving'
> Subject: Re: [AccessD] Checking for Records in Recordsets
>
> It is a code idea from this set of articles:
>
>
> http://www.linkedin.com/groupAnswers?viewQuestionAndAnswers=&discussio
> nID=24
> 7036278&gid=4843954&commentID=-1&trk=view_disc&fromEmail=&ut=0zZtrmMKO
> SoSc1
>
>
> http://www.linkedin.com/groupAnswers?viewQuestionAndAnswers=&discussio
> nID=24
> 7036474&gid=4843954&commentID=-1&trk=view_disc&fromEmail=&ut=3WCtwx3gC
> ToSc1
>
>
> http://www.linkedin.com/groups/Access-ADO-SQL-Server-perfect-4843954.S
> .24703
>
> 6822?view=&gid=4843954&type=member&item=247036822&commentID=-1&trk=vie
> w_disc
>
>
> http://www.linkedin.com/groupAnswers?viewQuestionAndAnswers=&discussio
> nID=24
> 7037047&gid=4843954&commentID=-1&trk=view_disc&fromEmail=&ut=3A1HOttOq
> ToSc1
>
>
> Basically the stored procedure has a number of select statements used 
> by the main form and any combo boxes on the form.  When the form is 
> opened then the form and combo box sources are set by moving through 
> the stored procedure's recordsets.
>
> The error is not on the first recordset which is used for the form, 
> but for one of the subsequent recordsets.  Because a parameter is 
> being passed into the stored procedure which is used to filter the 
> combobox records, there are situations when no records are returned 
> for that combobox.  This is what is causing the problem.
>
> I have narrowed the problem further.  The actual problem line is:
>
>         Set Me!cboSearch.Recordset = ""
>
> I have also tried
>
>         Set Me!cboSearch.Recordset = NULL
>
> So it was not that the recordset had no records but the resetting of 
> the field's source that produces the error 424 (Object Required).  How 
> do I clear the recordset for the combo box so that it doesn't show the 
> previous filtered list?
>
> Regards
>
> David Emerson
> Dalyn Software Ltd
> Wellington, New Zealand
>
>
>
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Darryl 
> Collins
> Sent: Monday, 14 April 2014 3:43 p.m.
> To: Access Developers discussion and problem solving
> Subject: Re: [AccessD] Checking for Records in Recordsets
>
> I had similar questions on the code logic / syntax, but I figured I 
> was only seeing part of the deal and there might be good / other 
> reasons for the choice of code.
> But it did seem a bit odd prima facie.
>
> Regards
> Darryl.
>
>
>
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Bill Benson
> Sent: Monday, 14 April 2014 1:14 PM
> To: Access Developers discussion and problem solving
> Subject: Re: [AccessD] Checking for Records in Recordsets
>
> A question: why are you setting Me.recordset = rstTemp?
>
> Personally I don't see how rstTemp.EOF I'd giving an error when the 
> Set statement just prior to that does not.
>
> I could see if the Set statement was failing how the assignment might 
> not replace the prior existing definition of rstTemp, as happens when 
> setting other objects equal to something undefined will do (I think).
>
> Anyway, no help here, sorry, just questions!
> On Apr 13, 2014 9:17 PM, "David Emerson" <newsgrps at dalyn.co.nz> wrote:
>
> > Listers,
> >
> >
> >
> > I have some code that gets recordsets and applies them to the 
> > sources of a number of combobox controls.
> >
> >
> >
> > The code looks like this:
> >
> >
> >
> >     Set rstTemp = basRunDataObject("dbo.spfrmFunderServiceCode " & 
> > Nz(Me!cboFilter, 0), adCmdText)
> >
> >     Set Me.Recordset = rstTemp
> >
> >
> >
> >     Set rstTemp = rstTemp.NextRecordset
> >
> >
> >
> >     If rstTemp.EOF <> True Then 'Record exists
> >
> >         Set Me!cboSearch.Recordset = rstTemp
> >
> >     Else
> >
> >         Set Me!cboSearch.Recordset = Null
> >
> >     End If
> >
> >
> >
> > The stored Procedure has a number of select statements to get a 
> > number of record sets.
> >
> >
> >
> > When a recordset has no records then an error 424 (Object Required) 
> > appears when the "If rstTemp.EOF <> True" line is processed.  This 
> > seems to be telling me that if a recordset has no records then it 
> > doesn't even get returned as an empty recordset.  However, when I 
> > have multiple recordsets I can use the "Set rstTemp = 
> > rstTemp.NextRecordset"  line to move to the next expected recordset 
> > and it
> is the correct one for the next control.
> >
> >
> >
> > How can I check if there are any records in a returned recordset and 
> > clear the control source?
> >
> >
> >
> > Regards
> >
> > David Emerson
> > Dalyn Software Ltd
> > Wellington, New Zealand
> >
> >
> >
> >
> >
> > --
> > AccessD mailing list
> > AccessD at databaseadvisors.com
> > http://databaseadvisors.com/mailman/listinfo/accessd
> > Website: http://www.databaseadvisors.com
> >
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com



More information about the AccessD mailing list