Keeps asking for variable value previously declared

[Gavx]

At run time this code asks for iProductID despite the fact that this variable has been correctly declared.
I know this because I have utilised the watch window for watching the value of iProductID right through to the end of the code. I have also used the intermediate window to determine its (correct) value.

Anyone know why it does this?

thanks

Option Compare Database
Option Explicit

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

On Error GoTo cmdInbound_Transport_Err

Me.sbfGuestProduct.SetFocus

[COLOR="Red"]iProductID[/COLOR] = DLookup("DefaultProductID", "tblProductType", "ProductTypeID = 1")

sSQL = "INSERT INTO tblGuestProduct (ProductID, GuestID) "
sSQL = sSQL & "VALUES ([COLOR="red"]iProductID[/COLOR],forms![frmBooking]![sbfGuest].form![txtGuestID]);"

DoCmd.RunSQL (sSQL)

cmdInbound_Transport_Exit:
   Exit Sub

cmdInbound_Transport_Err:
    MsgBox Error$
    Resume cmdInbound_Transport_Exit
    
End Sub

 

[JHB]

As mention in your thread http://www.access-programmers.co.uk/forums/showthread.php?t=275743
when you use the keyword Values then it need to be values which you still not have. The values from your variable iProductID and the references to a control on your form, must be inserted in the sSQL string.

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