INSERT INTO Syntax

[ScrmingWhisprs]

Hi there. I have a volunteer sign in form that has an unbound textbox (txtBarcode) and a Command Button (cmdSignIn). Our workers have ID badges with barcodes that are linked to their records. When a worker scans his/her barcode, the command button should do the following:

Private Sub cmdSignIn_Click()

'Using these declarations, I should be able to return the worker's ID number.
    
Dim Barcode As String
Dim WorkerNumber As Integer
Dim TodaysDate As String

Barcode = Me.txtBarcode
WorkerNumber = DLookup("vID", "tblVolunteers", "[Barcode]='" & Barcode & "'")
TransactionDate = Me.Date

'This INSERT INTO statement should insert a new record into tblTransactions with the Worker's ID number that was retrieved above, the transaction date, and the value "1" for the DateType

DoCmd.RunSQL ("INSERT INTO tblTransactions (WorkerID,Date,DateType)" & _
            "VALUES (WorkerNumber,TransactionDate,1)")
    
End Sub

However, I get an INSERT INTO syntax error. Can you see what's wrong with this?

Thanks.
SW

 

[pbaldy]

A couple of things. For starters, you won't end up with a space between the 2 clauses. Also, you have to concatenate the variables in the same way you did in the DLookup (with # around date values and ' around text values).

As a side issue, you shouldn't have a field named date, as it conflicts with the Date() function.

 

[georgedwilkinson]

And the database probably doesn't know what "WorkerNumber" or "TransactionDate" are since they appear to be VB variables, even though TransactionDate is not declared locally (though it probably should be).

How about:

DoCmd.RunSQL ("INSERT INTO tblTransactions (WorkerID, Date, DateType) " & _
            "VALUES (" & WorkerNumber & ", #" & TransactionDate & "#, 1)")

This assumes that "DateType" is an integer.

Also, this is air code, off the top of my head and untested, with no knowledge of the application.

 

[EraserveAP]

One way to help you track your errors is to debug.print your SQL Query.

You can then copy/paste it into the Access query builder. From there you try to view in design mode. The method is not a 100% but I have solved more than one error, without seeking help, using this method.