Struggling with append query

  • Thread starter Thread starter KJ
  • Start date Start date
K

KJ

I was trying to execute an append query that gets input
parameters from a form using a macro but I think what Help
just told me is that I need to use the DoCmd method of
RUNSQL. I am very new to VBA so how would I convert the
following SQL code to VBA?
INSERT INTO tblAppend ( CustomerID, EmployeeID, OrderID,
OrderDate )
SELECT Orders.CustomerID, Orders.EmployeeID,
Orders.OrderID, Orders.OrderDate
FROM Orders
WHERE (((Orders.CustomerID)=[Forms]![frmAppendwSubform]!
[frmAppendTest]![WhatCustomer]) AND ((Orders.EmployeeID)=
[Forms]![frmAppendwSubform]![frmAppendTest]![WhatEmployee].
[form]))
ORDER BY Orders.CustomerID, Orders.EmployeeID;
Thanks in advance for any help you can provide.
 
assign the following to a click of a button
'convert the query to a text string
'eg
strSQL = "INSERT INTO tblAppend ( CustomerID, EmployeeID, OrderID,
OrderDate )"
strSQL = strSQL & " SELECT Orders.CustomerID, Orders.EmployeeID,
Orders.OrderID, Orders.OrderDate"
strSQL = strSQL & " FROM Orders"
strSQL = strSQL & " WHERE Orders.CustomerID = '" &
[Forms]![frmAppendwSubform]![frmAppendTest]![WhatCustomer] & "'"
strSQL = strSQL & " AND Orders.EmployeeID )= '" &
[Forms]![frmAppendwSubform]![frmAppendTest]![WhatEmployee] "'"
strSQL = strSQL & " ORDER BY Orders.CustomerID, Orders.EmployeeID"

'and then run the SQL

DoCmd.runSQL strSQL

Hope this helps
 
Add a string variable to your code. This will make it easier to debug errors.

You need to concatenate the values of the controls into your query string and
not a reference to the control.

Assuming that CustomerID and EmployeeID are text fields you need to surround the
values from controls with quote marks. If they aren't text fields then drop the
Chr(34) & in the where clause.

Dim strSQL as String

strSQL = "INSERT INTO tblAppend " & _
" ( CustomerID, EmployeeID, OrderID, OrderDate )" & _
" SELECT Orders.CustomerID, Orders.EmployeeID, " & _
" Orders.OrderID, Orders.OrderDate" & _
" FROM Orders" & _
" WHERE Orders.CustomerID=" & Chr(34) & _
[Forms]![frmAppendwSubform].[Form]![WhatCustomer] & Chr(34) & _
" AND Orders.EmployeeID=" & Chr(34) & _
[Forms]![frmAppendwSubform].[Form]![WhatEmployee] & Chr(34)

Debug.Print strSQL 'While debugging to see if you get a valid SQL statement.

Also, check out the reference string to the subform control.
 
Back
Top