error 2465 problem

[epkdnb]

Been trying to get some auto-emailing code to work. I've been able to get the actual email code to work, but now that I've been making the code more complex I've been running into some issues. I'm fairly certain the 2465 is located somewhere is this block of code, I use this block quite a few times with just a few variations.


Dim strTo As String
Dim strSubject As String
Dim strMessage As String
Dim strCourse As String
Dim strLCourse As String
Dim strComplete As String
Dim db As DAO.Database
Dim rs As DAO.Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset("qryTraining", dbOpenSnapshot)
strCourse = DLookup([Course], tblCourse, [LCourse] = [cmbLCourse])
strLCourse = cmbLCourse
strCompleted = strCourse & "Completed"

Do Until rs.EOF
Select Case [cmbLCourse] = "Information Protection"
Case [lstType] = "Expires in 15" And Date <= DateAdd("m", DLookup("[Valid]", "tblCourse", "[Course] = '" & strCourse & _
"'"), "'[" & strCompleted & "]'") And Date >= DateAdd(d, -15, DateAdd(m, DLookup("[Valid]", "tblCourse", "[Course] = '" _
& strCourse & "'"), "'[" & strCompleted & "]'")) And [Ignore] = "False"
With rs
strTo = strTo & rs!Email & "; "
strSubject = "blah "
strMessage = "blah "
.MoveNext
End With

......................... more of similiar blocks

Case Else
With rs
.MoveNext
End With
End Select
Loop


..........

cmbLCourse is a combobox containing the Long Course Name
lstType is a listbox containing Overdue, Expires in 15, and Expires in 30 - all hard coded into listbox
tblCourse is a table with all the different types of training available and how long the training is valid - contains the fields [Course], [LCourse], [Valid]
qryTraining is a query based off the main tblPersonnel containing everyone's info, including the <trainingtype>Completed which is when that person last completed that type of training

If any more info is needed just ask, and yes I do have all the closing code, just didnt post it.

 

[ck1999]

Have you used f8 step through the code to determine which line is causing the error?

ck1999

 

[epkdnb]

kind of, when i hit f8 or use the menu command, all i get is a window's ding

 

[epkdnb]

I have however commented out almost everything but the select case and case

 

[ck1999]

this "'[" & strCompleted & "]'") looks funny to me

ck1999

 

[ck1999]

replace this
Case [lstType] = "Expires in 15" And Date <= DateAdd("m", DLookup("[Valid]", "tblCourse", "[Course] = '" & strCourse & _
"'"), "'[" & strCompleted & "]'") And Date >= DateAdd(d, -15, DateAdd(m, DLookup("[Valid]", "tblCourse", "[Course] = '" _
& strCourse & "'"), "'[" & strCompleted & "]'")) And [Ignore] = "False"

with

Case [lstType] = "Expires in 15" And Date <= DateAdd("m", DLookup("[Valid]", "tblCourse", "[Course] = '" & strCourse & _
"'"), "'[" & strCompleted & "]'")

for the time being and replace "'[" & strCompleted & "]'" with the actual date

StrCompleted is a string not a date


ck1999

 

[epkdnb]

well i made strCompleted to correlate to a field on tblPersonnel. ie: when checking someones date for the last time they completed xyz training the name of the field in tblPersonnel is xyzCompleted, so I thought making a string that would become xyzCompleted would work. Whether or not it works, even if I replace the string with a hardcoded date, I still get the error.