MS Access and Visual Basic - syntax error in insert into statement

Question

I get this error when I want to send data from form into table. This is Visual Basic code I wrote in MS Access:

Private Sub btnAdd_Click()
    CurrentDb.Execute "INSERT INTO Book(book_id,title,language_id,author_id,year_published,num_pages,publisher_id,num_copies)" & _
                      "Values (" & Null & ",'" & Me.txtTitle & "','" & Me.cbLanguage.Column(0) & "','" & Me.cbAuthor.Column(0) & "','" & Me.txtYearPublished & "','" & _
                      Me.txtNumPages & "','" & Me.cbPublisher.Column(0) & "','" & Me.txtNumCopies & "')"
End Sub

book_id is the primary key and auto-number.

Arrow indicating error appears on the 4th line of code.

Please update the original post. Is bbokID Auto Number type? Or a Primary Key, either case it should not be Null. — PaulFrancis
– PaulFrancis, Commented Feb 16, 2015 at 13:06
How about author_ID, yearPublished, publisherID, numberCopies.. you have included them as Text, are they actually text? Or Numbers? — PaulFrancis
– PaulFrancis, Commented Feb 16, 2015 at 13:13
Yes, they are all numbers. But how to convert them to numbers? — Gavrilo Adamovic
– Gavrilo Adamovic, Commented Feb 16, 2015 at 13:15
The & operator does the trick and converts your numbers to strings, no need to worry about it. — Las Ten
– Las Ten, Commented Feb 16, 2015 at 13:24

PaulFrancis · Accepted Answer · 2015-02-16 13:20:47Z

1

Your main issues are because Book_ID is set to Auto Number type; it does not need to be passed, Access JET engine will take care of it for you. Then all your values are Numeric, but when you add a ' to the variable you treat it as String/Text. So your code needs to change as,

Private Sub btnAdd_Click()
    CurrentDb.Execute "INSERT INTO Book (title, language_id, author_id, year_published, " & _
                      "num_pages, publisher_id, num_copies) VALUES (" & Me.txtTitle & ", " & _
                      Me.cbLanguage.Column(0) & ", " & Me.cbAuthor.Column(0) & ", " & Me.txtYearPublished & _
                      ", " & Me.txtNumPages & ", " & Me.cbPublisher.Column(0) & ", " & Me.txtNumCopies & ")"
End Sub

answered Feb 16, 2015 at 13:20

PaulFrancis

5,8191 gold badge23 silver badges38 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Las Ten · Accepted Answer · 2015-02-16 13:22:00Z

1

Just don't include book_id anywhere in the insert statement if it's PK and auto-incremented.

answered Feb 16, 2015 at 13:22

Las Ten

1,17510 silver badges22 bronze badges

Collectives™ on Stack Overflow

MS Access and Visual Basic - syntax error in insert into statement

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related