INSERT INTO Syntax Error - Date help

[dolfin13]

I'm appending data from a form into a table. The code works fine without trying to add the date, but when I try to add the date, I get the syntax error.

CODE:

Private Sub cmdTarget_Click()

Dim strSQL As String
Dim strSQL2 As String
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim rs2 As DAO.Recordset
Dim maxnum As Integer
Dim OpId As Integer
Dim OwnId As Integer

Set db = CurrentDb

strSQL = "SELECT CSP_T_Opportunities.[Opportunity Name], CSP_T_Opportunities.ID, CSP_T_Opportunities.OwnerNew FROM CSP_T_Opportunities WHERE (((CSP_T_Opportunities.Flag4)=True));"

Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)

With rs
If Not rs.EOF Then
rs.MoveFirst
Do While Not rs.EOF
OwnId = rs("OwnerNew")
CurrentProject.Connection.Execute ("Insert INTO CSP_T_TeamActivityLog (ActivitySchedType, ActivityStatus, OwnerNew, Begin) VALUES ('" & cboMeeting & "', '" & cboTargeted & "', '" & OwnId & "', #" & DatePicker & "#)")
OpId = rs("ID")
strSQL2 = "SELECT Max(ID) AS ActAuto FROM CSP_T_TeamActivityLog;"
Set rs2 = db.OpenRecordset(strSQL2)
maxnum = rs2("ActAuto")
CurrentProject.Connection.Execute ("Insert INTO CSP_T_TaskDetail (ActivityID, OpportunityNew) VALUES ('" & maxnum & "', '" & OpId & "')")
rs2.Close
rs.MoveNext
Loop
End If
End With
rs.Close
Set rs = Nothing
Set rs2 = Nothing
MsgBox ("Done")
End Sub


This is the line I get the error for:

CurrentProject.Connection.Execute ("Insert INTO CSP_T_TeamActivityLog (ActivitySchedType, ActivityStatus, OwnerNew, Begin) VALUES ('" & cboMeeting & "', '" & cboTargeted & "', '" & OwnId & "', #" & DatePicker & "#)")


It works fine like this, without the date:

CurrentProject.Connection.Execute ("Insert INTO CSP_T_TeamActivityLog (ActivitySchedType, ActivityStatus, OwnerNew) VALUES ('" & cboMeeting & "', '" & cboTargeted & "', '" & OwnId & "')")

I'm afraid its probably something simple, but I need some fresh eyes on this.

Any help is greatly appreciated!

I almost forgot. I'm usig Access 2003.

Thanks!

 

[PHV]

Why using CurrentProject.Connection.Execute instead of db.Execute ?

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

 

[dolfin13]

I know, I shouldn't mix ADO and DAO. It really just stems from me piecing this together from other modules and I havn't gotten to the point where I go back and "clean-up" yet.

Thanks for pointing that out.

I should also say that the "DatePicker" control is the MS Date and Timer Picker. Would I need to refer to ActiveX controls differently?

Thanks again for the help!

 

[dolfin13]

I found the solution to my problem. And, as I suspected, I'm an idiot!

Turns out the table had long date format and the Date Picker control had short date format.

I guess I just needed a little food to spur my brain!

Sorry if I wasted anyone's time.