SQL in VBA recordset returns 0 records, in query returns 2

[SteveC24]

Hello all,

When I run a piece of SQL in a query from the access query design view, I get 2 records appear, as I would expect.

When I try and run the same thing as a recordset in VBA, I don't get any records.

I can't for the life of me figure (or find) out why.

The code (including the SQL) is as below - can anyone see what is wrong?

Dim dteSFDeliveryDate As Date
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL
Dim strFiles

dteSFDeliveryDate = OpenArgs

DoCmd.RunCommand acCmdSaveRecord
DoCmd.SetWarnings False
DoCmd.OpenQuery "qryUpdateBoxItemOrderID"
DoCmd.SetWarnings True

strSQL = "SELECT Description, Surname, Forename, BoxItemID, tblBoxItem.OrderID, TempSelect From tblBoxItem, qryTodaysOrder WHERE TempSelect=-1;"
Set db = CurrentDb
Set rs = db.OpenRecordset(strSQL)

MsgBox rs.RecordCount
rs.MoveLast
rs.MoveFirst
Do Until rs.EOF
 strFiles = strFiles & vbCrLf & rs.Fields("description").Value & "  (" & rs.Fields("surname").Value & ", " & rs.Fields("forename").Value & ")"
 rs.MoveNext
Loop

MsgBox "Box requested successfully." & vbCrLf & vbCrLf & "Delivery Expected: " & dteSFDeliveryDate, vbInformation + vbOKOnly, "Box Request"

DoCmd.Close acForm, "frmSelectFiles"
Forms!frmbox!BoxLocation.Requery
Forms!frmbox.Visible = True

rs.Close

 

[KeithG]

strSQL = "SELECT Description, Surname, Forename, BoxItemID, tblBoxItem.OrderID, TempSelect From tblBoxItem, qryTodaysOrder WHERE TempSelect=-1;"

try this

strSQL = "SELECT Description, Surname, Forename, BoxItemID, tblBoxItem.OrderID, TempSelect From tblBoxItem, qryTodaysOrder WHERE TempSelect=" & -1

 

[KeithG]

Also in a DAO recordset the RecordCount property displays the count of how many records you have accessed in the recordset not the number of records in the recordset.

 

[SteveC24]

Thanks for your speedy reply - just tried that new code, and I get the same error, about "No current record".

Also - what would I use instead of the RecordCount property? At present, that line is in there only for troubleshooting purposes - but it would be useful to know in the future.

Thanks!

 

[KeithG]

Can you post an example DB and I can have a look. Basically you are using the record count property to determine if there is a record, correct? If a recordset dosen't have any records the BOF and EOF properties will both be set to true. Is tempSelect a Boolean data type field?

 

[KeithG]

Also is the tempSelect field in qryTodaysOrder and tblItem? I am curious as to why there is no relationship between the two recordset this is also going to cause you problems.

 

[gemma-the-husky]

despite the fact yuo think the sql is right, it cant be the same as the stored query, or you would get the same answer.

i would check the sql in that query very carefully.

 

[SteveC24]

OK - attached you should find a copy of the database, I have cut it down to keep the size down, and have had to remove all but our DUMMY record, for confidentiality purposes - but it will allow you to test it.

If you fire up the form "frmSelectFiles" - enter the boxid of 1773 - this is the only record in this version of the db.

You will get a list of items within that archive box - select a couple using the checkboxes, and click "Continue".

You will get the message box performing the recordcount, and then you should receive the error.

Thank you very much for your help.

---

gemma-the-husky:

Thank you for your input - but I am afraid you are wrong!
Unless my computer is managing to change the SQL when I copy and paste it into a new query window, it is exactly the same.
I have seen other posts on the web with the same issue, but they all seem to have a root cause of something to do with wildcard searching - which my query does not have.


Thanks for your help.

 

[KeithG]

What is the purpose of qryDate? You are not using any fields from the recordset and you are not enforcing any relationships.

 

[gemma-the-husky]

i really don't understand what you are doing in your code

however, after you click continue, the first thing your code does is runs an update query which modifies the order no in the boxitem ,

BUT ALSO SETS THE CHECKED FILE FLAG BACK TO FALSE [not shouting, just emphasis]

so presumably this query is the source of your problem, and not the SQL code at all.

 

[SteveC24]

Sorry for the delay - saw your messages over the weekend, but needed to get back into work to look at the code myself.

I had written the reply to KeithG before I tried out what gemma-the-husky said, but I will include the reply to KeithG anyway!

KeithG:
Not sure where you are getting 'qryDate' from? I didn't think I recognised it, and can't find any reference to it in any of my code?
The fields from the recordset are being used to build the text in the variable 'strFiles'.
As for enforcing relationships - I am afraid you have lost me! Recordsets (as you may have guessed) are not one of my best subjects!!!


gemma-the-husky:
I had a look at that query, and sure enough, if I take out the portion of that query that unchecks the file flag again, everything works just fine (the recordcount property only reports 1 record, when there are 3 in my example, but never mind! - I believe what KeithG was saying about that property may fix that.)

So - thank you to both of you - I am now back on track somewhat again.

KeithG - if you could let me know what I can use instead of the RecordCount property, to get that number coming up right, it would be useful in future!

Thank you very much indeed!

 

[gemma-the-husky]

i think what keith was saying is that with an rst you need to do

rst.movelast

then
rst.recordcount will work correctly, because the rst has seen all the records