Code Keeps Running

[khwaja]

I used to use following code quite successfully but now using it after a long time, I noticed that code simply keeps running between While Not rst.EOF and Wend lines. This lifts the email address from a table where I have users listed and true false in front of their names. So it is designed to address email to selected users. Will appreciate some help in finding out the issue.

Sub EmailLevel1()
Dim db As Database
Dim SQLStmt As String
Dim rst As Recordset
Dim TheRecipients As String

On Error Resume Next

Set db = CurrentDb()
SQLStmt = "SELECT loginid FROM tblDistribution WHERE Level1Schedule=True;"
Set rst = db.OpenRecordset(SQLStmt, dbOpenDynaset)

While Not rst.EOF
If TheRecipients = "" Then
TheRecipients = rst.Fields(0) & ";"
Else
TheRecipients = TheRecipients & rst.Fields(0) & ";"
End If
rst.MoveNext
Wend

If Not Len(TheRecipients) = 0 Then
DoCmd.SendObject acSendReport, "Construction Schedule L1", acFormatSNP, TheRecipients, , , "Construction Schedule - Level 1 (Selected Regions)", "Attached is the Level 1 Construction Schedule for your info." & _
vbCrLf _
& vbCrLf & "Cheers" & vbCrLf _
& vbCrLf & "Issued By: " & vbCrLf _
& vbCrLf & "Construction Team", True
End If
rst.Close
Set rst = Nothing
Set db = Nothing
End Sub

 

[Galaxiom]

Remove On Error Resume Next line.

Then any errors will be raised instead of being ignored.

 

[khwaja]

Thanks. After commenting it out, I am getting runtime error '13' Type mismatch stopping at

Set rst = db.OpenRecordset(SQLStmt, dbOpenDynaset)

 

[khwaja]

Hoping to get some help if I could.

Thanks in advance.

 

[Galaxiom]

Thanks. After commenting it out, I am getting runtime error '13' Type mismatch stopping at

Set rst = db.OpenRecordset(SQLStmt, dbOpenDynaset)

This error would indicate that Level1Schedule is not a Boolean (Yes/No) field type.

 

[khwaja]

Thanks. I have checked it, it is yes/no field. Could be any other issue?

 

[khwaja]

Just to be sure, I have attached a screenshot FYI.

 

[RainLover]

What information are you expecting us to see from your pic that will help.

There does not appear to be anything special.

 

[khwaja]

I was merely trying to advise Galaxion of the field type in relation to his contact. If you could have a better explanation of the error I am getting, that would be great. I just cannot explain the reason for not being able to use the code if I am using it after a long time.

 

[Galaxiom]

Everything looks right. Run the SQl as an ordinary query to make sure it works.

The only other place there could be a mismatch is in parameters of OpenRecordset. Maybe the enums are corrupted.

What is returned if you type the following into the Immediate Window and hit return:

? dbOpenDynaset

 

[RainLover]

Do you know which line it fails on.

Use a bunch of Message Boxes to see where it stops.

 

[Galaxiom]

Do you know which line it fails on.

See Post #3

 

[RainLover]

Galaxiom

Thanks for that.

Will leave this with you.

 

[khwaja]

Thank Galaxion for the tip. I recreated a sql statement and that does not look any different than what I was using. But I over wrote the statement in the code and I still have the same. error.

When I run ? dbOpenDynaset, I get '2' as the number.

 

[Galaxiom]

Everything looks right then.

The only other thing I can think of is that ADODB has taken precedence over DAO in your references.

Change:
Dim rst As Recordset

to:
Dim rst as DAO.Recordset

It is a good policy to always specify this even if you are not using ADODB in your project.

 

[khwaja]

Thanks so much. That did the trick and it is all good. So very grateful.