Append Query missing perameters

[crossy5575]

Hi there,

I have a form (purchaseorder) which holds order details.
When pushing button i want to convert the temporary order into a confirmed order by an append query.
This is done by moving the items in (tblorder1) (itemno) and (quantity)
and the form order no which is a text box on the form Forms![Purchaseorder]![Purchaseorder]

the append query is as follows:

INSERT INTO tblorderprodsdetail ( purchaseorder, purchaseitem, purchasequantity )
SELECT Forms![Purchaseorder]![Purchaseorder] AS Expr1, tblorder1.itemno, tblorder1.quantity
FROM tblorder1;

this works when the form is open, and i just runt he append query from the objects box. When i run it from a button on the form using the code

Private Sub convertorder_Click()

Dim dbs As DAO.Database
Dim qd As DAO.QueryDef
Dim lngRowsAffected As Long
Dim strng As String

strng = "tblorder1 Query"
Set dbs = CurrentDb
Set qd = dbs.QueryDefs(strng)

Dim db As DAO.Database ' do use a database variable rather than CurrentDb every time

dbs.Execute (strng), dbFailOnError

'XXX OR using

qd.Execute , dbFailOnError


End Sub
I get errors.

I dont understand why i get too few perameters using dbs.Execute (strng), dbFailOnError

and why qd.Execute , dbFailOnError doesnt work at all

can someone explain why it is not working (as well as a solution please?!)
tblorderprodsdetail has 2 more fields in it which are yes no boxes if this matters?

thanks

Simon

 

[pbaldy]

Simplest solution is replacing all that with

DoCmd.OpenQuery "tblorder1 Query"

Optionally surrounding by SetWarnings.

 

[pbaldy]

I should have added that the error comes because the Execute method can't resolve the form reference inside the query. You can build the SQL in code and run it with the Execute method if you want.