Insert query syntax error (1 Viewer)

Mr_Si

Registered User.
Local time
Today, 06:39
Joined
Dec 8, 2007
Messages
163
Hi All,

I'm trying to do an insert into query via VBA that takes the values of some controls in a form and puts them in to another table.

Basically frmPurchaseHistory, if a new record, will insert a new entry in to tblComponentHistory.

My Code is as follows but it is getting a syntax error:


Code:
Private Sub btnClose_Click()
    Dim strINSERT As String
        
    strINSERT = "INSERT INTO tblComponent (dtmDate, Component, History, Quantity, Update) " _
                & "VALUES (#" & Format(Me.PurchaseDate.Value, "mm/dd/yyyy") & "#, " & Me.Component.Value & ", 2, " & Me.Qty & ", True);"
    
    If OpenArgs = 1 Then
        MsgBox strINSERT ' for debugging purposes
        CurrentDb.Execute strINSERT ' run the insert query
    End If
    
    DoCmd.Close
End Sub

When I print it to a message Box, it looks like this, and to me that's fine:




I would really value some help if possible.
Many thanks,
Simon
 

pr2-eugin

Super Moderator
Local time
Today, 06:39
Joined
Nov 30, 2011
Messages
8,494
What are the DataTypes for dtmDate, Component, History, Quantity, Update?
 

Mr_Si

Registered User.
Local time
Today, 06:39
Joined
Dec 8, 2007
Messages
163
DtmDate is date/time
component is number
history is number
quantity is number
update is yes/no

Sorry, should have said.
 
Last edited:

Mr_Si

Registered User.
Local time
Today, 06:39
Joined
Dec 8, 2007
Messages
163
Have it sorted, by not having the last value in as I don't really need it there.
However, knowing how to do the boolean thing would be great
 

pr2-eugin

Super Moderator
Local time
Today, 06:39
Joined
Nov 30, 2011
Messages
8,494
Try -1 instead of True and see if it works.
 

pr2-eugin

Super Moderator
Local time
Today, 06:39
Joined
Nov 30, 2011
Messages
8,494
Probably because Update is a reserved word? Try enclosing Update in Square brackets like [Update] and try again.
 

Mr_Si

Registered User.
Local time
Today, 06:39
Joined
Dec 8, 2007
Messages
163
Aha! You're amazing. That worked. Thank you so much.
 

Users who are viewing this thread

Top Bottom