Looping problem making me loopy.

[stephenaa5]

Hello, all. I have tried to cobble together/teach myself how to perform a process, and have not been successful. Hope someone can help.


I have a query called TechsWithCallsQry which is a list of employees who are to receive the “AutodispatchLabelsRpt” report. It is based on a number of criteria. TechsWithCallsQry contains TechName and TechEmail result fields for each record/employee who is to receive his report.

The report “AutoDispatchLabelsRpt” is filtered by having its underlying query “AutoDispatchQry” have one of its components, TechnicianAssigned be filtered. This can be filtered via the “pass through form” AutoDispatchLabelsFrm. I’ve tested all of this in the immediate window, and it works just fine.

What I would like to do is loop through all of the names/ records in TechsWithCallsQry and:

1. For each TechName in TechsWithCallsQry
2. Create a variable that is passed to a hidden form (AutoDispatchLabelsFrm). This gives AutoDispatchQry a value for “TechnicianAssigned”. This will filter the report.
3. Create a variable that the command below can use to take “TechsWithCallsQry.Email” and use it in the place of RecipientEmail below.
4. Execute: docmd.SendObject acSendReport, "DispatchLabelsRpt", acformatpdf, RecipientEmail, , , "Dispatch Labels as of "&now(), "SampleMessage", false
a. I know this works, tested in the immediate window.
5. Go to the next TechName.
6. Close the form when done.

I have resolved the issue of Outlook not wanting to let the program send emails by using a 3^rd party app. There are about 100 emails that need to be send each day, so it’s important that we get this done. Any suggestions would be appreciated, I’m still just learning. I have taken a stab at the code below, and am certain it’s an abomination. The whole thought process may be an abomination, but I freely admit it’s 100% workaround. Thanks for your help. I'm trying, folks...


Stephen.

Start------------------------------------------------------
Dim db As DAO.DataBase
Dim rs as DAO.Recordset
Set db = CurrentDB()
Set rs = db.OpenRecordset(TechsWithCallsQry)
Dim RecipientEmail as string
Dim Technician as string



‘ Opens form to serve as conduit for TechnicianAssigned value to the select query. Works fine.

Docmd.OpenForm "AutoDispatchLabelsFrm", achidden


‘start the loop. No idea if this is working.

While Not rs.EOF

‘ sets the values for both the email function to be the current record.

RecipientEmail = TechsWithCallsQry.Email
Technician = TechsWithCallsQry.TechName

‘sets the pass through form “Name” value to be the current record’s “Technician” value. This makes the report filter for the correct data via another query. Cannot tell if this works.

[Forms]![AutoDispatchLabelsFrm].[Name].Value = Technician

‘Send the report to the recipient’s email address. Works fine, tested.

docmd.SendObject acSendReport, "AutoDispatchLabelsRpt", acformatpdf, RecipientEmail, , , "Dispatch Labels as of "&now(), "SampleMessage", false

‘Go to the next record, or end. Cannot tell if this works.

Rs.Movenext

Wend

Set rs = Nothing



‘Close the hidden form. Works fine, tested.

docmd.close acform, "AutoDispatchLabelsFrm"

[ssanfu]

Don't know if this will help, but here are the things I saw that should be fixed:

Dim db As DAO.DataBase
Dim rs as DAO.Recordset
Dim RecipientEmail as string
Dim Technician as string

Set db = CurrentDB()
Set rs = db.OpenRecordset(TechsWithCallsQry)

‘ Opens form to serve as conduit for TechnicianAssigned value to the select query. Works fine.
Docmd.OpenForm "AutoDispatchLabelsFrm", achidden

‘start the loop. No idea if this is working.
While Not rs.EOF

‘ sets the values for both the email function to be the current record.
'RecipientEmail = TechsWithCallsQry.Email
'Technician = TechsWithCallsQry.TechName

'have to refer to the recordset
RecipientEmail = rs("Email")
Technician = rs("TechName")

‘sets the pass through form “Name” value to be the current record’s “Technician” value. This makes the report filter for the correct data via another query. Cannot tell if this works.

'"NAME" is a reserved word in ACCESS and shouldn't be used for object names. Plus it is not very descriptive. "TechName" would be MUCH better!!!
[Forms]![AutoDispatchLabelsFrm].[Name] = Technician

‘Send the report to the recipient’s email address. Works fine, tested.
docmd.SendObject acSendReport, "AutoDispatchLabelsRpt", acformatpdf, RecipientEmail, , , "Dispatch Labels as of "&now(), "SampleMessage", false

‘Go to the next record, or end. Cannot tell if this works.
Rs.Movenext

Wend

'close before de-referencing 
rs.close
Set rs = Nothing