If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Struggling with append query
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. |
#2
|
|||
|
|||
Struggling with append query
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 "KJ" wrote in message ... 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. |
#3
|
|||
|
|||
Struggling with append query
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. KJ wrote: 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. |
Thread Tools | |
Display Modes | |
|
|