Query works when run manually but not VBA

[Nathan87]

I have a query that works as expected when I just open it and type a number for the parameter field, but when it is called through VBA it seems to be functioning differently.

Here is the query:

SELECT AttachmentTable.ID, AttachmentTable.ItemID, AttachmentTable.Type, 
AttachmentTable.AttachmentName, AttachmentTable.Default, AttachmentTable.Deleted

FROM AttachmentTable

WHERE (
(AttachmentTable.ItemID=[IDfilter]) And 
(AttachmentTable.Type=1) And 
(AttachmentTable.Default=True) And 
(AttachmentTable.Deleted=False));

When run through vba the criteria for default being true and deleted being false don't seem to work. If I have 3 pictures and only one is selected as default only one should be match, but when run through VBA if I have 3 pictures and one is selected as Default (meaning value of True) then all 3 pictures are returned by the query. When ran manually though only 1 record is returned.

Here is how I'm calling the query in the script and how I'm checking the number of returned records:

Sub LoadDefaultPicture()

    Dim db As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim prm As DAO.Parameter
    Dim rs As DAO.Recordset
    Dim strFolderPath As String
    Dim strDefaultPictureName As String
    Dim intRecordCount As Integer
    
    Set db = CurrentDb
    
    Set qdf = db.QueryDefs("DefaultPictureQry")
    qdf.Parameters("IDfilter").Value = Forms![ItemDetailTable]![ID]
    'qdf.Parameters("IDfilter").Value = 1
    Set rs = qdf.OpenRecordset
    intRecordCount = rs.RecordCount

Any ideas of what could be going wrong here?

 

[JHB]

Try replacing the True by -1 and the False by 0

...
(AttachmentTable.Default=-1) And  (AttachmentTable.Deleted=0));

 

[Nathan87]

That gives the same results. I even tried using Yes/No and got the same results as well.

 

[CJ_London]

Type is a reserved word and using it as a field name can produce unexpected results. Suggest change the name or at the very least surround with square brackets wherever used.

Also you have not set parameters in your query which should look something like

PARAMETERS IDFilter Long;
SELECT AttachmentTable.ID, AttachmentTable.ItemID, AttachmentTable.Type, 
....

 

[Nathan87]

Thanks for the suggestion. Tried below and still no luck.

PARAMETERS IDFilter Long;

SELECT AttachmentTable.ID, AttachmentTable.ItemID, AttachmentTable.AttachmentType, 
AttachmentTable.AttachmentName, AttachmentTable.Default, AttachmentTable.Deleted

FROM AttachmentTable

WHERE (((AttachmentTable.ItemID)=[IDfilter]) 
AND ((AttachmentTable.AttachmentType)=1) 
AND ((AttachmentTable.Default)=-1) 
AND ((AttachmentTable.Deleted)=0));

This is the same SQL as above, but copied exactly from Access. In the above I tried to break up the lines to make it more readable. I don't think I'm changing the way it works, but just in case.

PARAMETERS IDFilter Long;
SELECT AttachmentTable.ID, AttachmentTable.ItemID, AttachmentTable.AttachmentType, AttachmentTable.AttachmentName, AttachmentTable.Default, AttachmentTable.Deleted
FROM AttachmentTable
WHERE (((AttachmentTable.ItemID)=[IDfilter]) AND ((AttachmentTable.AttachmentType)=1) AND ((AttachmentTable.Default)=-1) AND ((AttachmentTable.Deleted)=0));

Still works as expected when just opening the query and running it in the Access window just not when run through the vba code.

 

[CJ_London]

how are you deciding it is not working?

if it is on the basis of recordcount you need to move to the last record first

rs.movelast
debug.print rs.recordcount

 

[JHB]

If you can't get it post a stripped version of the database with some sample data + info where/how to run the code.

 

[Nathan87]

Thanks a lot CJ!

You are right in that I was deciding it wasn't working based on the record count. I just tried the movelast as you suggested and that worked. So I guess the query has been working all this time and I was just thinking it was wrong because of the record count. I definitely spent a lot of time trying to solve that "problem"... that didn't actually exist!

So what is the reasoning behind the needing the movelast? I would assume the count would always equal the count.

 

[CJ_London]

So I guess the query has been working all this time

Possibly - take out the parameters line from your query and see if it still works.

So what is the reasoning behind the needing the movelast? I would assume the count would always equal the count.

When you open a query takes a bit of time to get to the last record - with a query it won't 'know' the recordcount until it has been fully processed. Using movelast effectively stops 'vba' processing (I'm sure someone will pick me up on that) until it has reached the last record. With small recordsets it is hardly noticeable but if you have every run a big query you will have noticed it sometime takes a bit of time before the recordcount appears at the bottom of the query window - even tho' it may be displaying the first few records.

 

[CJ_London]

If you want to know whether there are any records in a recordset, check for EOF instead

And you can use processing of the recordset using the same property (don't forget to use movefirst if you have used movelast)

while not rs.EOF
    do something
    rs.movenext
wend

 

[Nathan87]

Thanks a lot for all the great information. That definitely clears it up (at least this problem).