Using unbound control value in an SQL statement

Question

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!

Answer 1

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