Using If then to set recordset

[shaggles]

Is this possible? When I try to run the code below I get
an Oject Required error message.

Private Sub Command23_Click()
On Error GoTo Err_Command23_Click
Dim db As DAO.Database
Dim rst As DAO.Recordset
Set db = CurrentDb
If Me!Group Is Null Then
Set rst = db.OpenRecordset("SELECT * FROM (tblResponse
INNER JOIN tblUsers " & _
"ON tblResponse.DeptID=tblUsers.DeptID) " & _
"WHERE tblResponse.Issue_Num = " & Me!Issue_Num)
Else
Set rst = db.OpenRecordset("SELECT tblUsers.Email FROM
tblUsers INNER JOIN tblGroups " & _
"ON tblUsers.UserID=tblGroups.UserID " & _
"WHERE Group_Name = " & "'" & Me!Group & "'" & ";")
End If
While Not rst.EOF
Dim Session, dbs, MailDoc As Object
Dim RTItem As Variant
Dim EMailAddress, MailSubject, FileLocation, strLink
As String
strLink = Me!Notice_Hyperlink
EMailAddress = rst!Email
MailSubject = "Notice " & rst!Issue_Num
Set Session = CreateObject("Notes.NotesSession")
Set dbs = Session.getdatabase("my server", "my mail")
Set MailDoc = dbs.createdocument()
Set RTItem = MailDoc.createrichtextitem("Body")
MailDoc.Form = "Memo"
MailDoc.SendTo = EMailAddress
MailDoc.Subject = MailSubject
MailDoc.send (False)
rst.MoveNext
Wend
Exit_Command23_Click:
Exit Sub
Err_Command23_Click:
MsgBox Err.Description
Resume Exit_Command23_Click
End Sub

 

[Dirk Goldgar]

Is this possible? When I try to run the code below I get
an Oject Required error message.

Private Sub Command23_Click()
On Error GoTo Err_Command23_Click
Dim db As DAO.Database
Dim rst As DAO.Recordset
Set db = CurrentDb
If Me!Group Is Null Then
Set rst = db.OpenRecordset("SELECT * FROM (tblResponse
INNER JOIN tblUsers " & _
"ON tblResponse.DeptID=tblUsers.DeptID) " & _
"WHERE tblResponse.Issue_Num = " & Me!Issue_Num)
Else
Set rst = db.OpenRecordset("SELECT tblUsers.Email FROM
tblUsers INNER JOIN tblGroups " & _
"ON tblUsers.UserID=tblGroups.UserID " & _
"WHERE Group_Name = " & "'" & Me!Group & "'" & ";")
End If
While Not rst.EOF
Dim Session, dbs, MailDoc As Object
Dim RTItem As Variant
Dim EMailAddress, MailSubject, FileLocation, strLink
As String
strLink = Me!Notice_Hyperlink
EMailAddress = rst!Email
MailSubject = "Notice " & rst!Issue_Num
Set Session = CreateObject("Notes.NotesSession")
Set dbs = Session.getdatabase("my server", "my mail")
Set MailDoc = dbs.createdocument()
Set RTItem = MailDoc.createrichtextitem("Body")
MailDoc.Form = "Memo"
MailDoc.SendTo = EMailAddress
MailDoc.Subject = MailSubject
MailDoc.send (False)
rst.MoveNext
Wend
Exit_Command23_Click:
Exit Sub
Err_Command23_Click:
MsgBox Err.Description
Resume Exit_Command23_Click
End Sub

You don't say what line the error is raised on. That would help narrow
down the specific problem.

The following syntax:

If Me!Group Is Null Then

is incorrect in VBA code, though valid in SQL. Instead use:

If IsNull(Me!Group) Then

That may be the cause of your error. Further, this line:

Dim Session, dbs, MailDoc As Object

declares only MailDoc as an Object; the others are all Variants. Use
this:

Dim Session As Object, dbs As Object, MailDoc As Object

(or make them separate statements). Similarly, this line:

Dim EMailAddress, MailSubject, FileLocation, strLink As String

declares only strLink as a String. The others are all Variants. You
need to specify the type for each variable you declare:

Dim EMailAddress As String
Dim MailSubject As String
Dim FileLocation As String
Dim strLinkAs String