Insert query syntax error 1

[cathy68]

I am trying to set up some code to add records to a linking table based on a couple of parameters from the form, using VB in Access Form, SQL backend.
I created the linking table (Diary_tb) from SQL server as:
NUMBER nvarchar 8 primary key
DETAILS nvarchar 50 allowed null
STARTDATE smalldatetime 4 primary key
FINISHDATE smalldatetime 4 allowed null
TYPE nvarchar 20 primary key

My code is:
Private Sub RNKINDX_AfterUpdate()
Dim strMsg As String
Dim strInput As String
Dim dteStart As Date
Dim strDetail
Dim STARTDATE As Date
strMsg = "Do you want to promote the new rank ?"
If MsgBox(strMsg, vbOKCancel, "Error!&quot = vbOK Then
newRank = Me![RANK]
strInput = InputBox("Please input promotion date&quot

STARTDATE = CDate(strInput)
strDetail = oldRank + newRank

Set dbs = CurrentDb
strSQL = "INSERT INTO DIARY_tb (NUMBER, DETAILS, STARTDATE, TYPE) VALUES ('" & Trim(Me![NUMBER]) & "','" & strDetail & "', #" & STARTDATE & "#, 'PROMOTION');"
DoCmd. RunSQL strSQL
Else
Exit Sub
End If
End Sub

After I run this code I got error message:
Run-time Error 3134
Syntax error in INSERT INTO statement.

Many thanks for any help.

Cathy

 

[RoyVidar]

Hi!

Couple of suggestions:
Number and Type are reserved words in Access/VBA and should be avoided as names of fields, controls (Details is a method of Outlook's AddresEntry Object, too)... Sometimes one might get lucky with surrounding them with [brackets].
The date, if your date settings differ from the US date format, you might consider converting it, using for instance Ansi format.

[tt]format$(STARTDATE, "yyyy-mm-dd&quot[/tt]

Do some checking on the values of the different form controls, to verify that they have valid values (IsNull, NZ, IsDate...)

Hope some of this might work (know little of the SQL backend thingie, but think since you use docmd.runsql this should apply)

Roy-Vidar

 

[ancb]

cathy

An easy way to build the code is to create a saved action query ( in your case, I think, append) from the table that you want to add to and then view SQL and copy and paste it into your vba. When you CR lines use: " & _

example:
strSQL = "INSERT INTO Table var1, " & _
"var2....."


I hope this helps.

 

[cathy68]

Hi Roy-Vidar
Thanks for these suggestion. I changed these reserved words as Number and Type. It works fine now.