Access VBA OpenRecordset Group Level OutputTo No records error

[Mac1206]

Hi,

I have an issue when my code runs, it only grab the records that the cursor is on and not looping through to grab the specific records grouped by ID....For the 31 clients, ID 48961 which is 1 client of the 31 clients which the cursor was on in the Form was the only records pulled for the 31 different client groups...See Attachment Below...My issue is I'm only pulling records where my cursor is pointing to and not looping through all the records...Thanks

Private Sub cmdSendRpt_Click()
Dim rst As DAO.Recordset
Dim strSql As String

strSql = "Select * FROM Qry_Max_Pymt_PymtEmailDetail;"

Set rst = CurrentDb.OpenRecordset(strSql)

Do While Not rst.EOF

Me.txtID = rst![ID]

DoCmd.OutputTo acOutputQuery, "Max_Pymt_EmailDetails1", acFormatXLS, "C:\Users\amccoy\Desktop\TestFolder" & "\" & rst![ID] & "_" & rst![PymtsPayee] & ".xls"

DoEvents
rst.MoveNext

Loop

rst.Close
Set rst = Nothing

End Sub

 

[welshgasman]

The full error is Run-Time error 32535
TempVars can only store data. They cannot store Objects.


tempvars("txtID")=48951
? tempvars("txtID")
48951

Came back so I'm not understanding the issue...

Ok, try

TempVars("txtID") = rst!ID.Value

 

[welshgasman]

This definitely works

Sub TestTempvars()
Dim rst As DAO.Recordset

Set rst = CurrentDb().OpenRecordset("Select * from Transactions")
Do Until rst.EOF
    TempVars("txtID") = rst!ID.Value
    Debug.Print TempVars("txtID")
    rst.MoveNext
Loop
Set rst = Nothing
End Sub

 

[Mac1206]

Ok, try

TempVars("txtID") = rst!ID.Value

That did it welshgasman, it's working perfectly now...Awesome and thanks so so much to all of you for having patience with me and seeing it to the end....Thanks so much and I learned a lot too...

 

[welshgasman]

FWIW it seems that when trying to assign the ID Access was *thinking* you were meaning the rst ID object.?
I must apologise as .Value property is the default for controls/recordset fields, and I generally never use it.

Note to self, it is needed when TempVars are used.

 

[Mac1206]

Thanks welshgasman, now that I got the Files to correctly output to a folder, how do I get them to email automatically to the specific client whom might have 3 or more email addresses and not include those that are not valid email addresses?? Sense we already have the code grouping like it suppose to and outputting the data for each client how can we get this to email automatically just by the click of a button to all the recipients?

Private Sub CmdEmailRpt_Click()

Dim rst As DAO.Recordset
Dim strSql As String
Dim txtID As TempVars



strSql = "Select * FROM Qry_Max_Pymt_PymtEmailDetail;"

Set rst = CurrentDb.OpenRecordset(strSql)

Do While Not rst.EOF

TempVars("txtID") = rst!ID.Value




DoCmd.SendObject acOutputQuery, "Max_Pymt_EmailDetails1", acFormatXLS, "email", , "This is just a test", "This is a test to ensure delivery of all emails", False

DoEvents
rst.MoveNext


Loop


rst.Close

Set rst = Nothing

End Sub

 

[Mac1206]

Is it possible to grab each file that equals the group by ID from my C:\Users\amccoy\Desktop\TestFolder and send it out automatically to the specific client. the Email field is already part of the recordset.

DoCmd.OutputTo acOutputQuery, "Max_Pymt_EmailDetails1", acFormatXLS, "C:\Users\amccoy\Desktop\TestFolder" & "\" & rst![ID] & "_" & rst![PymtsPayee] & ".xls"

 

[welshgasman]

Well I would bring in the email address with the first query, but you mention multiple addresses?
How are you able to determine what is a valid email address and what is not? Really you should not be allowing any invalid email addresses in the DB to start with.?

tt@tt.com looks like a valid email address, but might not exist in real life.?

It also depends on how you have stored the other email addresses.? This is probably one of the few times having email, email1, email2, email3 would make life easier even if not normalized.

 

[welshgasman]

Is it possible to grab each file that equals the group by ID from my C:\Users\amccoy\Desktop\TestFolder and send it out automatically to the specific client. the Email field is already part of the recordset.

DoCmd.OutputTo acOutputQuery, "Max_Pymt_EmailDetails1", acFormatXLS, "C:\Users\amccoy\Desktop\TestFolder" & "\" & rst![ID] & "_" & rst![PymtsPayee] & ".xls"

Well you could just send instead of writing to a file, but do you need that file.?
SendObject is not something I've used, I've always doen it via Outlook automation.

 

[Mac1206]

Well you could just send instead of writing to a file, but do you need that file.?
SendObject is not something I've used, I've always doen it via Outlook automation.

Yes the output file is needed thus, I wanted to get that out the way first. Now I need to email this out automatically and grab each email which 1 client might have 3 different emails populated in the specific Email field...

 

[welshgasman]

Ok, the simplest, though not the most efficient would be to just use the SendObject after creating the file.
The reason it is inefficient is beacuse the query will run twice, once for the file and once for the email.?
Bring in ALL the email addresses in the first query and concatenate them with a semi colon ; and use that as the To address.