Access VBA OpenRecordset Group Level OutputTo No records error

Mac1206

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

Some videos you may like

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

welshgasman

Well-known Member
Joined
May 25, 2013
Messages
777
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?
 

Mac1206

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

welshgasman

Well-known Member
Joined
May 25, 2013
Messages
777
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
 

Mac1206

Board Regular
Joined
Jun 3, 2016
Messages
170
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]));
 

welshgasman

Well-known Member
Joined
May 25, 2013
Messages
777
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?
 

xenou

MrExcel MVP, Moderator
Joined
Mar 2, 2007
Messages
16,628
Office Version
2013
Platform
Windows
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?
 

welshgasman

Well-known Member
Joined
May 25, 2013
Messages
777
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. :(
 

Mac1206

Board Regular
Joined
Jun 3, 2016
Messages
170
Thanks, I don't know VB well enough to understand and this is very confusing....Thanks anyway
 

welshgasman

Well-known Member
Joined
May 25, 2013
Messages
777
Well a good debugging tip is change one thing at a time and test.

Just what does the ID represent and are there duplicates?
 

Watch MrExcel Video

Forum statistics

Threads
1,095,728
Messages
5,446,174
Members
405,389
Latest member
Excel n00b2

This Week's Hot Topics

Top