run time error 2447

[Lindsey M]

Hi everyone, hope you are well

I have the following code for my command button, but when I click the "OK"
button, an error comes up with the following

Run time error 2447

Here is my code:

Private Sub cmdOK_Click()

Dim strSQL As String
Dim sUserID As String
Dim sRoomID As String
Dim dDate As Date
Dim StartTime As String
Dim EndTime As String

strSQL = "Select * from tblSchedule"
sUserID = Me.txtUser
sRoomID = Me.cmbRoomReq
StartTime = Me.lstStart
EndTime = Me.lstEnd

Me.lstConflict.Requery

If Me.lstConflict.ListCount > 0 Then
MsgBox "Sorry, no can do!"
Else

DoCmd.RunSQL "INSERT INTO tblSchedule " & _
"(UserID, RoomID, Date, StartTime, EndTime) " & _
"VALUES (" & sUserID & ", " & sRoomID & ", #" & _
dDate & "#, #" & StartTime & "#, #" & EndTime & "#)"

End If

End Sub

Can anyone tell me where I might be going wrong here?

Cheers
Lindsey

p.s., when i click on the Help button on the error box that appears, it
doesn't seem to be working so i can't figure out where the problem lays

 

[David Seeto via AccessMonster.com]

Hi Lindsey,

I think error 2447 refers to invalid syntax in your INSERT query - looking
at it, it's probably having problems with "Date": this is sort of a
reserved word in Access but not quite, so although you've been able to
create a table with this column name, SQL is interpreting it differently.
I'd recommend changing it to "EntryDate" or something.

Even if you don't want to do this, you can probably fix the problem by
placing [ and ] around the field names, like so:
DoCmd.RunSQL "INSERT INTO tblSchedule " & _
"([UserID], [RoomID], [Date], [StartTime], [EndTime]) " & _
"VALUES (" & sUserID & ", " & sRoomID & ", #" & _
dDate & "#, #" & StartTime & "#, #" & EndTime & "#)"

 

[Ofer]

Hi Lindsey

You declared sUserID and sRoomID As String
but in you query you treat it as Number
If the field in the table is number then declare it as
Number, if not then you should write the query as follow:

DoCmd.RunSQL "INSERT INTO tblSchedule " & _
"(UserID, RoomID, Date, StartTime, EndTime) " & _
"VALUES ('" & sUserID & "', '" & sRoomID & "', #" & _
dDate & "#, #" & StartTime & "#, #" & EndTime & "#)"