DoCmd.RunSQL Woes

[curlydog]

Can anyone tell me why this sql won't work?

I'm trying to append to a table using values from a form. this is the code;

Dim strSQL As String
strSQL = "INSERT INTO tblOTStatement (caseNum,officerPK,otSourcePK,hours,date) VALUES (""" & cmboReason & """," & cmboSurname & "," & cmboSource & "," & txtHours & ",#" & Format(txtDate, "mm/dd/yyyy") & "#);"
Debug.Print strSQL
DoCmd.RunSQL (strSQL)

By using debug.print I can examine strSQL's value, which in my test is;
INSERT INTO tblOTStatement (caseNum,officerPK,otSourcePK,hours,date) VALUES ("testing",13,7,23,#08/19/2004#);

I can't see anything wrong with it. I've checked that the various columns are getting the correct type of value. The value shown as "testing", I've tried as 'testing' and I've tried a formatted and non-formatted date.

The error that I get is "Run-Time error 3134, Syntax error in Insert into statement"

The only thing that I can think of that may be causing problems is the fact that the table has an autonumber primary key, which is not being addressed in my SQL. Is this an issue, I was under the impression that access would automatically allocate a value. The table has another three columns (including the primary key) which are not addressed in this query. None are required (other than the PK obviously).

 

[SiJP]

Task the code and dump it into the QBE, then open the query up in design mode.. see what errors it throws up.

I'll have a look at the code too just now..

------------------------
Hit any User to continue

 

[curlydog]

Just figured it out after finding a post in another forum.
"date" is a reserved word in SQL. I changed date to adate and it works fine.

 

[curlydog]

One thing that Access does when I insert the data is throw up a message box telling me that I'm about to append data to the table, and do I really want to do this.

This will get very annoying if inputting a lot of records one after the other.

Can I stop this message box.

Ta
Jason

 

[SiJP]

wrap date in square brackets:

INSERT INTO tblOTStatement (caseNum,officerPK,otSourcePK,hours,[date]) VALUES ("testing",13,7,23,#08/19/2004#);

------------------------
Hit any User to continue

 

[SiJP]

ah, you've seen it.. nm

------------------------
Hit any User to continue

 

[PHV]

Can I stop this message box.
Take a look at the DoCmd.SetWarnings method.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244