DAO Help

[Acropolis]

I am having trouble with the below returning the correct number of records, and can't see why.


I have one table, tblDevice, which has 4 columns, ID | DeviceRecNo | ExcludeFromCheck | StockLocationID


ID - Autonumber
DeviceRecNo - Number
ExcludeFromCheck - Number (1 = yes, 0 - No)
StockLocationID - Number


I have the following running as part of some code, but it is not returning the correct number of records, and I cant see why not. I have tried creating this in a query in Access itself, and copy the SQL into VBA and it still doesn't return the correct number records, any help would be appreciated.


I have 4 records in there for testing, all with StockLocationID = 3, all with different DeviceRecNo, and two each of ExcludeFromCheck, 2x0 and 2x1

Public Function test()
Dim db As dao.Database
Dim rs1 As dao.Recordset
Set db = CurrentDb
Set rs1 = db.OpenRecordset("SELECT DeviceRecNo FROM tblDevice WHERE StockLocationID = 3 AND ExcludeFromCheck = 'No'")
Debug.Print rs1.RecordCount
End Function

 

[CJ_London]

try modifying your code to

Set rs1 = db.OpenRecordset("SELECT DeviceRecNo FROM tblDevice WHERE StockLocationID = 3 AND ExcludeFromCheck = 'No'")
[COLOR=red]rs1.movelast[/COLOR]
Debug.Print rs1.RecordCount

 

[Acropolis]

That's got it thanks.


As I want to carry on and do some other things after this, if I do the rs1.MoveLast to get the count, then rs1.MoveFirst and carry on, that would work as it has been?

 

[CJ_London]

Yes it will - the only thing you should do is check for EOF and/or BOF - if your recordset returns nothing the EOF will be true so your movelast will generate an error

Set rs1 = db.OpenRecordset("SELECT DeviceRecNo FROM tblDevice WHERE StockLocationID = 3 AND ExcludeFromCheck = 'No'")
if not rs1.eof then
    rs1.movelast
    Debug.Print rs1.RecordCount
else
    msgbox "no records
end if