Syntax error with INSERT statement and sub form

[Gavx]

I am getting a syntax error on my SQL statement.

On a form I have a sub form containing the field txtGuestID - whose control source is GuestID.

On the main form I have a button that fires the code below.

I am sure I am not referring to the control txtGuestID correctly.

Can someone please give me some direction on where I am going wrong.

thanks

Private Sub cmdInbound_Transport_Click()
Dim iProductID As Integer
Dim sSQL As String

On Error GoTo cmdInbound_Transport_Err

iProductID = Nz(DLookup("[DefaultProductID]", "tblProductType", "[ProductTypeID] = 1"))


sSQL=INSERT INTO tblGuestProduct(ProductID, GuestID) VALUES (iProductID,[sbfGuest].Form![txtGuestID]);


DoCmd.RunSQL (sSQL)
DoCmd.Close

    
End Sub

 

[JHB]

When you use the keyword Values then it need to be values which you don't do.
I suppose [sbfGuest] is a subform to the form where you have the button and also that [txtGuestID] contains a text value, (if not a textvalue then remove the two ').

sSQL="INSERT INTO tblGuestProduct(ProductID, GuestID) VALUES (" & iProductID & ", '" & [sbfGuest].Form![txtGuestID] & "')";

 

[Gavx]

When you use the keyword Values then it need to be values ...

Yes, txtGuestID is an integer. And for that matter so is the other value in the expression (iProductID).

I suppose [sbfGuest] is a subform to the form...

Correct - this is the subform that contains the field txtGuestID. And as a reminder the button that fires this code is located on the main form.

 

[spikepl]

For SQL in VBA, follow this recipe to the letter: http://www.baldyweb.com/ImmediateWindow.htm

 

[JHB]

Yes, txtGuestID is an integer. And for that matter so is the other value in the expression (iProductID).

Did you remove the two ' ?

sSQL="INSERT INTO tblGuestProduct(ProductID, GuestID) VALUES (" & iProductID & ", " & [sbfGuest].Form![txtGuestID] & ")";

 

[Gavx]

Your solution did work.

Can you please tell me what the "& and the ' do in an SQL statement.

 

[JHB]

Your solution did work.

Can you please tell me what the "& and the ' do in an SQL statement.

It has nothing to do with the SQL statement, it is the method you use to get the values from the variables and controls into the sSQL string.