MS Access query not receiving parameter from VBA

0
votes

I have an append query that copies values from one table (tbl_LSI) to another (tbl_LSI_USD):

qry_Append_to_LTI_USD:

INSERT INTO tbl_LTI_USD
SELECT *
FROM tbl_LTI AS lti
WHERE lti.LTI_ID=[Forms]![frm_LTI]![LTI_ID];

I call this query on the AfterUpdate event of the form frm_LTI, where it takes the LTI_ID from the field LTI_ID. The VBA I use is:

db.Execute "qry_Append_to_LTI_USD", dbFailOnError

I have this exact same code working for a different form & table combination, but for some reason when I try and execute this one it fails and asks for a parameter:

Run-time error '3061'
Too few parameters. Expected 1

The only difference between the forms is that this one (frm_LTI) is modal & popup = Yes, whereas the working form modal & popup = No

The VBA code can see and debug.print the value [frm_LTI]![LTI_ID], but it doesn't get passed to teh query. (I have a screenshot showing this, but not enough reputation points to upload it).

Is this anything to do with the form's modal/popup property, or is there something else I'm missing?

1
sqlms-accessvba
Simple thing to do is instead of using a query, use docmd.setwarnings false; Docmd.RunSQL "INSERT INTO tbl_LTI_USD SELECT * FROM tbl_LTI WHERE LTI_ID = " & LTI_ID; docmd.Setwarnings true – John Bingham
Thanks, that seems to work. Do you know why the method I was trying to use failed? – tospig
No, but I recommended this, as it means that the logic is local to just the form, (since you're not using an external object, ie the query) which simplifies the problem and makes it easier to resolve any difficulties if a problem still occurs – John Bingham

1 Answers

0
votes

When you have such problems with Execute, a specially with pivot queries with sub queries. Try to replace WHERE lti.LTI_ID=[Forms]![frm_LTI]![LTI_ID]; with this WHERE lti.LTI_ID=eval("[Forms]![frm_LTI]![LTI_ID]");. But sometimes type cast also needed(for dates as example): WHERE lti.LTI_ID=cDbl(eval("[Forms]![frm_LTI]![LTI_ID]"));