1

I would just like to use an MS Access unbound control as criteria in my VBA SQL command.

The unbound control is named AssignInvoice

Here is what I have:

Private Sub Command64_Click()
    Dim InvNum As String
    
    InvNum = Me.AssignInvoice.Value
    
    DoCmd.RunSQL "INSERT INTO Orders (InvoiceNumber, CompanyName, PurchaseOrderNumber) values (AssignInvoice.Value, CompanyName.Value, PurchaseOrderNumber.Value)"
    DoCmd.RunSQL "INSERT INTO [Order Details] ( InvoiceNumber, ItemNumber, [Product Description], [Size], Quantity, UnitPrice )SELECT [SalesOrder Details].AssignedInvNum, [SalesOrder Details].ItemNumber, [SalesOrder Details].[Product Description], [SalesOrder Details].Size, [SalesOrder Details].Quantity, [SalesOrder Details].UnitPrice FROM [SalesOrder Details] WHERE [SalesOrder Details].AssignedInvNum = InvNum"
End Sub

The problem is when I click the button, Access asks for the InvNum parameter. Apparently I am not saving the form text box to the variable InvNum correctly, or syntax error?

When I enter the parameter the SQL statement ends up working correctly.

Hoping someone can clear this up. Thanks!

Lee Mac
  • 15,615
  • 6
  • 32
  • 80
BWSsteve
  • 15
  • 3

1 Answers1

0

You need to concatenate the variable and control values to the SQL string

Private Sub Command64_Click()

Dim InvNum As String

InvNum = Me.AssignInvoice.Value

DoCmd.RunSQL "INSERT INTO Orders (InvoiceNumber, CompanyName, PurchaseOrderNumber) values ('" & Me.AssignInvoice.Value & "', '" & Me.CompanyName.Value & "', '" & Me.PurchaseOrderNumber.Value & "')"
DoCmd.RunSQL "INSERT INTO [Order Details] ( InvoiceNumber, ItemNumber, [Product Description], [Size], Quantity, UnitPrice )SELECT [SalesOrder Details].AssignedInvNum, [SalesOrder Details].ItemNumber, [SalesOrder Details].[Product Description], [SalesOrder Details].Size, [SalesOrder Details].Quantity, [SalesOrder Details].UnitPrice FROM [SalesOrder Details] WHERE [SalesOrder Details].AssignedInvNum = '" & InvNum & "'"
End Sub

Number values can be directly concatenated to the string

String values need to be enclosed in single quotes', or double quotes"

Date values should be enclosed in #

Generic example

INSERT INTO TABLE (NumberField, StringField, DateField) VALUES (NumberValue, 'StringValue', #DateValue#)

You can store the SQL string in a string variable and use Debug.Print to see the resulting string in the Immediate Window for debugging / troubleshooting purposes.

Dim strSQL as String

strSQL = "INSERT INTO Orders (InvoiceNumber, CompanyName, PurchaseOrderNumber) values ('" & Me.AssignInvoice.Value & "', '" & Me.CompanyName.Value & "', '" & Me.PurchaseOrderNumber.Value & "')"

Debug.Print strSQL
jbud
  • 694
  • 5
  • 7
  • Syntax error on run... DoCmd.RunSQL "INSERT INTO [Order Details] ( InvoiceNumber, ItemNumber, [Product Description], [Size], Quantity, UnitPrice )SELECT [SalesOrder Details].AssignedInvNum, [SalesOrder Details].ItemNumber, [SalesOrder Details].[Product Description], [SalesOrder Details].Size, [SalesOrder Details].Quantity, [SalesOrder Details].UnitPrice FROM [SalesOrder Details] WHERE [SalesOrder Details].AssignedInvNum = " & InvNum Here is my error (0test was the form value btw): syntax error (missing operator) in query expression '[SalesOrder Details].AssignedInvNum =0test' – BWSsteve Nov 15 '20 at 00:58
  • I've edited the answer. Since your `InvNum` is a string, you need to add the quotes around the value while concatenating the string. – jbud Nov 15 '20 at 01:57
  • What are the data types of three controls values, which you're passing to the SQL string? From your comments `AssignInvoice` control contains a string. What are the data types of the other two controls: `CompanyName` and `PurchaseOrderNumber`? – jbud Nov 15 '20 at 02:00
  • They are all strings – BWSsteve Nov 15 '20 at 02:05
  • Help is much appreciated BTW – BWSsteve Nov 15 '20 at 02:05
  • I've updated the answer to reflect all control to contain strings. You just need to make sure that the resulting control value is enclosed in quotes `'`. – jbud Nov 15 '20 at 02:09
  • SOLVED! I replaced the back for the statement with your solution: WHERE [SalesOrder Details].AssignedInvNum = '" & InvNum & "'" Many thanks to you! – BWSsteve Nov 15 '20 at 02:11