Help with rs code...

  • Thread starter Thread starter Rick's News
  • Start date Start date
R

Rick's News

How do I loop to get all the e-mail addresses in the TO: line instead of one
at a time? I want to send this report to everyone in the report. Not one
record at a time...

Dim rs As DAO.Recordset
Dim strSQL As String
strSQL = "SELECT CAMSid, Email FROM [WorkcenterRoster] WHERE camsid Is Not
Null;"
Set rs = DBEngine(0)(0).OpenRecordset(strSQL)
Do While Not rs.EOF
gstrReportFilter = "CAMSid = " & rs!CamsID
DoCmd.SendObject acSendReport, "CAMS DUE", acFormatRTF, rs!Email,
"Recurring Training Forecast For the Next 14 Days. ", "If you are in the To
line of the e-mail, you have training due.", EditMessage:=True
rs.MoveNext
Loop
rs.Close
Set rs = Nothing
 
Rick's News said:
How do I loop to get all the e-mail addresses in the TO: line instead
of one at a time? I want to send this report to everyone in the
report. Not one record at a time...

Dim rs As DAO.Recordset
Dim strSQL As String
strSQL = "SELECT CAMSid, Email FROM [WorkcenterRoster] WHERE camsid
Is Not Null;"
Set rs = DBEngine(0)(0).OpenRecordset(strSQL)
Do While Not rs.EOF
gstrReportFilter = "CAMSid = " & rs!CamsID
DoCmd.SendObject acSendReport, "CAMS DUE", acFormatRTF, rs!Email,
"Recurring Training Forecast For the Next 14 Days. ", "If you are in
the To line of the e-mail, you have training due.", EditMessage:=True
rs.MoveNext
Loop
rs.Close
Set rs = Nothing
Click to expand...

Use the loop just to build the filter list and the list of e-mail
addresses; then send the report to that list:

Dim rs As DAO.Recordset
Dim strSQL As String
Dim strFilter As String
Dim strTo As String

strSQL = "SELECT CAMSid, Email FROM [WorkcenterRoster] " & _
"WHERE camsid Is Not Null;"

Set rs = DBEngine(0)(0).OpenRecordset(strSQL)

Do Until rs.EOF
strFilter = strFilter & ", " & rs!CamsID
strTo = strTo & "; " & rs!Email
rs.MoveNext
Loop
rs.Close
Set rs = Nothing

If Len(strFilter) = 0 Then
MsgBox "No data for the report!"
Else
gstrReportFilter = "CAMSid In (" & Mid$(rs!CamsID, 3) & ")"
strTo = Mid$(strTo, 3)
DoCmd.SendObject acSendReport, "CAMS DUE", acFormatRTF, _
strTo, "Recurring Training Forecast For the Next 14
Days. ", _
"If you are in the To line of the e-mail, you have
training due.", _
EditMessage:=True
End If
 
Doesn't look like you dimensioned the variable. Add this
line near the top with the other Dim statements...

Dim gstrReportFilter As String

--

Gary Miller
Gary Miller Computer Services
Sisters, OR
________________________
Rick's News said:
I'm getting an error on this line:

gstrReportFilter = "CAMSid = " & rs!CamsID

Run time error 91: Object variable or with block variable not set.

Thank you for helping me...

-Rick

Rick's News said:
How do I loop to get all the e-mail addresses in the TO: line instead
of one at a time? I want to send this report to everyone in the
report. Not one record at a time...

Dim rs As DAO.Recordset
Dim strSQL As String
strSQL = "SELECT CAMSid, Email FROM [WorkcenterRoster] WHERE camsid
Is Not Null;"
Set rs = DBEngine(0)(0).OpenRecordset(strSQL)
Do While Not rs.EOF
gstrReportFilter = "CAMSid = " & rs!CamsID
DoCmd.SendObject acSendReport, "CAMS DUE", acFormatRTF, rs!Email,
"Recurring Training Forecast For the Next 14 Days. ", "If you are in
the To line of the e-mail, you have training due.", EditMessage:=True
rs.MoveNext
Loop
rs.Close
Set rs = Nothing
Click to expand...

Use the loop just to build the filter list and the list of e-mail
addresses; then send the report to that list:

Dim rs As DAO.Recordset
Dim strSQL As String
Dim strFilter As String
Dim strTo As String

strSQL = "SELECT CAMSid, Email FROM [WorkcenterRoster] " & _
"WHERE camsid Is Not Null;"

Set rs = DBEngine(0)(0).OpenRecordset(strSQL)

Do Until rs.EOF
strFilter = strFilter & ", " & rs!CamsID
strTo = strTo & "; " & rs!Email
rs.MoveNext
Loop
rs.Close
Set rs = Nothing

If Len(strFilter) = 0 Then
MsgBox "No data for the report!"
Else
gstrReportFilter = "CAMSid In (" & Mid$(rs!CamsID, 3) & ")"
strTo = Mid$(strTo, 3)
DoCmd.SendObject acSendReport, "CAMS DUE", acFormatRTF, _
strTo, "Recurring Training Forecast For the Next 14
Days. ", _
"If you are in the To line of the e-mail, you have
training due.", _
EditMessage:=True
End If

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...
Click to expand...
 
I'm getting an error on this line:

gstrReportFilter = "CAMSid = " & rs!CamsID

Run time error 91: Object variable or with block variable not set.

Thank you for helping me...

-Rick

Dirk Goldgar said:
Rick's News said:
How do I loop to get all the e-mail addresses in the TO: line instead
of one at a time? I want to send this report to everyone in the
report. Not one record at a time...

Dim rs As DAO.Recordset
Dim strSQL As String
strSQL = "SELECT CAMSid, Email FROM [WorkcenterRoster] WHERE camsid
Is Not Null;"
Set rs = DBEngine(0)(0).OpenRecordset(strSQL)
Do While Not rs.EOF
gstrReportFilter = "CAMSid = " & rs!CamsID
DoCmd.SendObject acSendReport, "CAMS DUE", acFormatRTF, rs!Email,
"Recurring Training Forecast For the Next 14 Days. ", "If you are in
the To line of the e-mail, you have training due.", EditMessage:=True
rs.MoveNext
Loop
rs.Close
Set rs = Nothing
Click to expand...

Use the loop just to build the filter list and the list of e-mail
addresses; then send the report to that list:

Dim rs As DAO.Recordset
Dim strSQL As String
Dim strFilter As String
Dim strTo As String

strSQL = "SELECT CAMSid, Email FROM [WorkcenterRoster] " & _
"WHERE camsid Is Not Null;"

Set rs = DBEngine(0)(0).OpenRecordset(strSQL)

Do Until rs.EOF
strFilter = strFilter & ", " & rs!CamsID
strTo = strTo & "; " & rs!Email
rs.MoveNext
Loop
rs.Close
Set rs = Nothing

If Len(strFilter) = 0 Then
MsgBox "No data for the report!"
Else
gstrReportFilter = "CAMSid In (" & Mid$(rs!CamsID, 3) & ")"
strTo = Mid$(strTo, 3)
DoCmd.SendObject acSendReport, "CAMS DUE", acFormatRTF, _
strTo, "Recurring Training Forecast For the Next 14
Days. ", _
"If you are in the To line of the e-mail, you have
training due.", _
EditMessage:=True
End If

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...
 
Rick's News said:
I'm getting an error on this line:

gstrReportFilter = "CAMSid = " & rs!CamsID

Run time error 91: Object variable or with block variable not set.

Thank you for helping me...
Click to expand...

It's because of an oversight on my part. That line should be:

gstrReportFilter = "CAMSid = " & strFilter

Sorry about that.
 
Gary Miller said:
Doesn't look like you dimensioned the variable. Add this
line near the top with the other Dim statements...

Dim gstrReportFilter As String
Click to expand...

I'm assuming that the "gstr" prefix means that this variable is declared
as a Public variable (of type String) in a standard module.
 
I have another problem...

I couldn't figure out why it was returning all the records, until now...

The recordset is tblWorkCenterRoster.
In a query for the report I have another table tblCAMSDue which only returns
the records I need.
The email address is in the tblWorkCenterRoster table.
How do I make the code check the tblCAMSDue table for the records but the
tblWorkCenterRoster for the email address?
They both have CAMSid to specify the record.
Do I create a second Recordset and compare the CAMSid?

Thank you for all your help on this...

-Rick
 
Rick's News said:
I have another problem...

I couldn't figure out why it was returning all the records, until
now...

The recordset is tblWorkCenterRoster.
In a query for the report I have another table tblCAMSDue which only
returns the records I need.
The email address is in the tblWorkCenterRoster table.
How do I make the code check the tblCAMSDue table for the records but
the tblWorkCenterRoster for the email address?
They both have CAMSid to specify the record.
Do I create a second Recordset and compare the CAMSid?
Click to expand...

You shouldn't need another recordset, but it sounds like you need to
base the recordset you're using on a SQL Statement that joins the two
tables on the CAMSid field. Try replacing these lines:
strSQL = "SELECT CAMSid, Email FROM [WorkcenterRoster] " & _
"WHERE camsid Is Not Null;"
Click to expand...

With these:

strSQL = _
"SELECT tblCAMSDue.CAMSid, WorkcenterRoster.Email " & _
"FROM tblCAMSDue INNER JOIN WorkcenterRoster " & _
"ON tblCAMSDue.CAMSid = WorkcenterRoster.CAMSid;"

I'm a little unsure of the exact table names, because in your message
you wrote "tblWorkCenterRoster", but your SQL statement uses just
"WorkCenterRoster". You'll have to do whatever fixup is needed.
 
Rick's News said:
Getting closer now...

But the report is generated to the same e-mail many times... If the
person is listed in the CAMS table 3 times then their e-mail address
is added 3 times to the e-mail... How do I tell it I want Only 1
e-mail per CAMSid?

WorkCenterRoster = 1 CAMSid in table
CAMS Due = Many Camsids for the same person
Click to expand...

Y'know, life would be a lot simpler if you would describe your table
structures and relationships in advance. <sigh> Okay, add the DISTINCT
keyword to the query:

strSQL = _
"SELECT DISTINCT tblCAMSDue.CAMSid, WorkcenterRoster.Email " & _
"FROM tblCAMSDue INNER JOIN WorkcenterRoster " & _
"ON tblCAMSDue.CAMSid = WorkcenterRoster.CAMSid;"
 
Getting closer now...

But the report is generated to the same e-mail many times... If the person
is listed in the CAMS table 3 times then their e-mail address is added 3
times to the e-mail... How do I tell it I want Only 1 e-mail per CAMSid?

WorkCenterRoster = 1 CAMSid in table
CAMS Due = Many Camsids for the same person

Thanks in advance...

Rick

Dirk Goldgar said:
Rick's News said:
I have another problem...

I couldn't figure out why it was returning all the records, until
now...

The recordset is tblWorkCenterRoster.
In a query for the report I have another table tblCAMSDue which only
returns the records I need.
The email address is in the tblWorkCenterRoster table.
How do I make the code check the tblCAMSDue table for the records but
the tblWorkCenterRoster for the email address?
They both have CAMSid to specify the record.
Do I create a second Recordset and compare the CAMSid?
Click to expand...

You shouldn't need another recordset, but it sounds like you need to
base the recordset you're using on a SQL Statement that joins the two
tables on the CAMSid field. Try replacing these lines:
strSQL = "SELECT CAMSid, Email FROM [WorkcenterRoster] " & _
"WHERE camsid Is Not Null;"
Click to expand...

With these:

strSQL = _
"SELECT tblCAMSDue.CAMSid, WorkcenterRoster.Email " & _
"FROM tblCAMSDue INNER JOIN WorkcenterRoster " & _
"ON tblCAMSDue.CAMSid = WorkcenterRoster.CAMSid;"

I'm a little unsure of the exact table names, because in your message
you wrote "tblWorkCenterRoster", but your SQL statement uses just
"WorkCenterRoster". You'll have to do whatever fixup is needed.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...
 
That did it... Thanks for all the help. Sorry for not being specific in the
first place. I was confused the query statement. I copied the SQL view of
the query for the report and added a couple more where statements and it
does exactly what I wanted. Thank you for your assistance and patience!

Have a good night...
-Rick
 
Back
Top