Need help with VBA SQL sytax

[cyd44]

Could someone kindly advise me on a syntax problem using a VBA strSQL statement

My Code is

StrSQL = "INSERT INTO tblInvoices (BRef, Cost, Paid, PaymentDate) VALUES  ( gID , gTotal, gPaid, gPaydate )"

The values are variables collected and I can see that they have been populated. However, when I run the SQL I am getting an access box asking me for each of the values in turn. The SQL is obviously trying but is not accept the Values. I suspect syntax and am not very skilled on VBA/SQL syntax requirements I am afraid,

Thanks in advance

 

[jdraw]

Please show all of the code in your procedure.
Do you have a recordset, or a form?

 

[cyd44]

I am using a Form and the coding is as follows:-

BRef = Me.ID
BAmount = Me.Total_Cost
BPaid = False
BDue = Me.Payment_Date
StrSQL = "INSERT INTO tblInvoice (Book_Ref, Amount, Paid, PaymentDate) VALUES  ( " & BRef & " , " & BAmount & ", " & BPaid & ", " & BDue & " )"
DoCmd.RunSQL (StrSQL)

I have changed the code from original to try out syntax. The above code does work (in a fashion) but only creates a new invoice putting in the ID and a date of 31/12/1899.

I realise that we need a specific syntax for different variables and the variables above are
Bref = Integer
BAmount - Currency
Paid - Boolean
BDue = Date

Hope this helps