Re: Retrieving data from user input.

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

J_Goddard via AccessMonster.com wrote:
I don't know what version of Access you have, but in A2000 your code won't
work because of the if rs.recordcount > 0 line. The recordcount for a
recordset is not accurate until all the records in the recordset have been
accessed. Since in your IF statement you have not accessed any records yet,
the Recordcount property is still 0.

To check if the retrieval returned any records, you can use

if not rs.BOF then....

I suspect this might have been the problem when you stated that the code
compiled and ran without errors, but no fields were filled in.


Saying the RecordCount is not accurate is not the same as
saying it is totally inaccurate. While it may not give the
total number of records in the recordset, it is guaranteed
to return a value greater than 0 if the recordset returns at
least one record. The key to thinking about this issue is
that "the number of records accessed so far" refers to
Access accessing the records, which is not necessarily the
same as the number of records the VBA code has accessed.

Checking RecordCount>0 on a newly opened recordset is just
as valid as checking BOF And EOF. This is true because if
the recordset returns any records, then Access has retrieved
at least one record. I.e. If neither BOF nor EOF is True,
then there must be a current record (which is guaranteed to
be the first record in a newly opened recordset).

--
Marsh
MVP [MS Access]
.

Relevant Pages

  • Re: Retrieving data from user input.
    ... Marshall Barton wrote: ... Saying the RecordCount is not accurate is not the same as ... If neither BOF nor EOF is True, ...
    (microsoft.public.access.formscoding)
  • Re: Looping through Table records.
    ... the first record - meaning that you have navigated past the first record. ... When BOF and EOF are both true the recordset is empty. ...
    (microsoft.public.access.formscoding)
  • Re: Empty "If Me.Recordset Is Nothing Then" clause causes runtime error 3021 (Access 2003)
    ... "If you delete the last remaining record in the Recordset ... I have some unresolved issues with requery/BOF/EOF, ... >> BOF and EOF are set by a MOVE command. ...
    (microsoft.public.access.forms)
  • Re: Struggling with current record
    ... > "not(.eof and .bof) that was commented out below it. ... Not (.EOF AND .BOF) is equivalent to ... I want to execute the ... > Insert first row into empty form. ...
    (microsoft.public.access.gettingstarted)
  • Re: .BOF and .EOF are lying!
    ... > you can't trust .recordcount until you've done a .movelast. ... > Before moving to the last record, sometimes the record count is 0. ... What are you testing for BOF and EOF? ...
    (microsoft.public.access.gettingstarted)