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]

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]

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]

I tend to create mine like

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]

I tend to create mine like

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]

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]

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]

No I think that syntax is correct.

This is from one of mine

WHERE (((tblSubmitterInvoice.DateInvoiced)=Date()) AND ((tblSubmitterClient.SubmitterID)=[TempVars]![SubmitterID]) AND ((tblSubmitterInvoice.DatePaid) Is Null));

However I have just noticed that he has changed

Me.txtID = rst![ID]

to

TempVars!txtID = Me.ID.Value

so he is using the form value and not the recordset anymore.

 

[Mac1206]

Thanks, I don't know VB well enough to understand and this is very confusing....Thanks anyway

 

[welshgasman]

Well a good debugging tip is change one thing at a time and test.

Just what does the ID represent and are there duplicates?