recordset and checkbox

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

The following code is only returning the first record in the recordset:

Set rsEstToUse = db.OpenRecordset("SELECT * " & _
"FROM qryEstToUse " & _
"WHERE [CurrentEstimate] = " & True)

[CurrentEstimate] is a yes/no field associated to a checkbox. Is there
something additional that needs to be done when filtering a yes/no field?
Thanks,Frank
 
Frank,

If you immediatly check the record count after opening the recordset, the
result will be 0 (zero) if there are no records in the recordset or 1 if
there are 1 or more records.

When you open a recordset, the record pointer can be at the BOF, EOF or any
where in between. To get the true record count you have go to the last record
(useing ".MoveLast"), then check the recordcount property.

But if there were 0 (zero) records returned, the code will bomb because
there is no record to move to. So you have to check for ".EOF". And I always
check for ".BOF" also.

Try this bit of code:

'***snip******
Set rsEstToUse = db.OpenRecordset("SELECT * " & _
"FROM qryEstToUse " & _
"WHERE [CurrentEstimate] = True")

If rsEstToUse.BOF Or rsEstToUse.EOF Then
MsgBox "BOF or EOF - No Records"
Else
rsEstToUse.MoveLast
End If

MsgBox rsEstToUse.RecordCount

rs.Close
Set rs = Nothing

' remove next line after debugging
Exit Sub
'***snip****

This should give you a different record count.

Also note I changed this line

"WHERE [CurrentEstimate] = " & True)

to this

"WHERE [CurrentEstimate] = True")


I only use the "&" when there are variables. But both should work.

HTH
 
That worked, thanks!

SteveS said:
Frank,

If you immediatly check the record count after opening the recordset, the
result will be 0 (zero) if there are no records in the recordset or 1 if
there are 1 or more records.

When you open a recordset, the record pointer can be at the BOF, EOF or any
where in between. To get the true record count you have go to the last record
(useing ".MoveLast"), then check the recordcount property.

But if there were 0 (zero) records returned, the code will bomb because
there is no record to move to. So you have to check for ".EOF". And I always
check for ".BOF" also.

Try this bit of code:

'***snip******
Set rsEstToUse = db.OpenRecordset("SELECT * " & _
"FROM qryEstToUse " & _
"WHERE [CurrentEstimate] = True")

If rsEstToUse.BOF Or rsEstToUse.EOF Then
MsgBox "BOF or EOF - No Records"
Else
rsEstToUse.MoveLast
End If

MsgBox rsEstToUse.RecordCount

rs.Close
Set rs = Nothing

' remove next line after debugging
Exit Sub
'***snip****

This should give you a different record count.

Also note I changed this line

"WHERE [CurrentEstimate] = " & True)

to this

"WHERE [CurrentEstimate] = True")


I only use the "&" when there are variables. But both should work.

HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


fgibbcollins said:
The following code is only returning the first record in the recordset:

Set rsEstToUse = db.OpenRecordset("SELECT * " & _
"FROM qryEstToUse " & _
"WHERE [CurrentEstimate] = " & True)

[CurrentEstimate] is a yes/no field associated to a checkbox. Is there
something additional that needs to be done when filtering a yes/no field?
Thanks,Frank
Click to expand...
Click to expand...
 
Here's a small improvement:

If rsEstToUse.BOF AND rsEstToUse.EOF Then
' empty.

BOF and EOF are *both* true when the recordset is empty. That's the
only case where both are true at the same time.

HTH,
TC (MVP Access)
http://tc2.atspace.com
 
Yes, it should have been AND instead of OR.

Sometimes the fingers type on their own. :D


Thanks TC
 
Back
Top