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

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
I would presume that is beacuse you are referring to a form control value for Max_Pymt_EmailDetails1 ? You do not show that control name :(, is that meant to be txtID

I would generally use a TempVar if I wanted to use a query from several places. The form would set the TempVar, your sub would set the tempVar etc.

Presumably the change in txtID is not being recognised for some reason?
 
Upvote 0
I would presume that is beacuse you are referring to a form control value for Max_Pymt_EmailDetails1 ? You do not show that control name :(, is that meant to be txtID

I would generally use a TempVar if I wanted to use a query from several places. The form would set the TempVar, your sub would set the tempVar etc.

Presumably the change in txtID is not being recognised for some reason?
How do I create a TempVar and where would I place it in my code
 
Upvote 0
I tend to create mine like
Code:
TempVars("QueryID") = <whatever it will be>

In your query the criteria would change to [TempVars]![QueryID] and you set that TempVar from wherever before you run the query.

So set the TempVar where you are trying to set Me.txtID
 
Upvote 0
I tend to create mine like
Code:
TempVars("QueryID") = <whatever it will be>

In your query the criteria would change to [TempVars]![QueryID] and you set that TempVar from wherever before you run the query.

So set the TempVar where you are trying to set Me.txtID
I tried that but it's still only seeing the record the cursor is set too on the Form, below is my code and query...I must be doing something wrong because it's not grouping nor looping through the recordset....The output is grouping fine but the records are not coming through...I'm doing something wrong here? My queries are pulling from a table of 660 records which need to group by ID...That should output 31 files which is working except for capturing the records for the specific files...

Private Sub cmdSendRpt_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 = Me.ID.Value

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


Query: Max_Pymt_EmailDetails1
SELECT T_Payment_Details.ID, T_Payment_Details.PymtAmount AS [Total Payment Amount], T_Payment_Details.Pymt_Custodian AS Custodian, T_Payment_Details.PymtMethod AS [Payment Method], T_Payment_Details.PymtsPayee AS [Payee Name], T_Payment_Details.Email
FROM T_Payment_Details
WHERE (((T_Payment_Details.ID)=[TempVars]![txtID]));
 
Upvote 0
What is the first query meant to do?, as you are getting the same details in the second query.?
Start debugging the code. Inspect the values, see how many records your recordset gets?

Shouldn't you be grouping the first query on some field. The ID field I would expect is an autonumber field? Yes?
 
Upvote 0
Note that it looks like Mac1206 has created a form called TempVars with a textbox in it (txtID). This is probably NOT the same as using TempVars in MSAccess.

For debugging, I would ask if the query works correctly without OutPutTo -- that is, can you just run the query directly and get the right result?
 
Upvote 0
No I think that syntax is correct.

This is from one of mine
Code:
WHERE (((tblSubmitterInvoice.DateInvoiced)=Date()) AND ((tblSubmitterClient.SubmitterID)=[TempVars]![SubmitterID]) AND ((tblSubmitterInvoice.DatePaid) Is Null));
However I have just noticed that he has changed
Code:
Me.txtID = rst![ID]
to
Code:
TempVars!txtID = Me.ID.Value
so he is using the form value and not the recordset anymore. :(
 
Upvote 0
Thanks, I don't know VB well enough to understand and this is very confusing....Thanks anyway
 
Upvote 0
Well a good debugging tip is change one thing at a time and test.

Just what does the ID represent and are there duplicates?
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,557
Latest member
richa mishra

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