Access VBA OpenRecordset Group Level OutputTo No records error

Mac1206

Board Regular
Joined
Jun 3, 2016
Messages
184
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
 

Attachments

  • Queries.jpg
    Queries.jpg
    127.6 KB · Views: 12
  • GroupbyExport.jpg
    GroupbyExport.jpg
    45.8 KB · Views: 12
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
Code:
TempVars("txtID") = rst!ID.Value
 
Upvote 0

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
This definitely works
Code:
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
 
Upvote 0
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. :oops:
 
Upvote 0
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
 
Upvote 0
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"
 

Attachments

  • Testfolder_ExampleFiles.png
    Testfolder_ExampleFiles.png
    31 KB · Views: 4
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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...
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,184
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top