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

 

[Mac1206]

I started over and this is what I have done thus far: the main query returns 31 grouped rows out of 660 records....I remove everything else TempVar, etc....

Qry_Max_Pymt_PymtEmailDetail"

SELECT T_Payment_Details.ID, T_Payment_Details.PymtsPayee
FROM T_Payment_Details
GROUP BY T_Payment_Details.ID, T_Payment_Details.PymtsPayee
ORDER BY T_Payment_Details.ID;

The Second query returns all 660 rows with the removal of the WHERE Clause, I get an output of 31 .xls files now but all 660 are in each file...Could you please show me what I suppose to add to get the required results per ID and PymtsPayee: I might have 250 rows for ID 14586 and 2 rows for ID 74152....I don't know what I'm missing from here to achieve this goal...

"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;



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

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

 

[Mac1206]

The ID represents a payment to a client whom might have various transactions under that ID which I need to capture...To include the Payee Name So Yes Duplicate ID's thus it's Group By....

 

[welshgasman]

That sounds more like it.

Now you need to restrict the output query to only a certain ID and that is where the TempVar comes back in.

You need to set the Tempvar to each value of the ID as you move through the recordset of the grouping query.

So put the criteria back in, set it in the immediate window with a valid ID and as a test run it from the query designer. It should give you the required records.?

Now go back to your code, make sure you are effectively doing the same process as above and then run it. Even step though the code line by line with F8 one or two times to check, then run the rest with F5.

Also do you have Option Explicit at the top of all your modules.?

 

[Mac1206]

Welshgasman,

Do I create my TempVars as such in my code, I'm unsure if I'm doing this right since I never did it before? Unsure how to test in immediate window but will look it up...

Not sure what this means: You need to set the Tempvar to each value of the ID as you move through the recordset of the grouping query. I have 31 different values in the Group By so how do I set the TempVars to each value?

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 = rst![ID]

And 2nd Query as Such:

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]));

 

[Mac1206]

Not sure what my TempVars!txtID =????, or if it's right at all...or does it suppose to be a value...12345,54127,45784???

 

[welshgasman]

There are several ways to define TempVars.
I first found out using

TempVars("txtID") = rst!ID    ' the name can be anything you want

and have been using that method every time


You were on the right track initially.
Effectively we need to
Read each record from first grouping query
For each ID run the second query
Move to next record in first query and repeat until EOF of first query.

I've just run a quick test to see the syntax of all versions as I was not sure running another .Add would change the TempVar, but it appears to do so.

Sub TestTempVars()
Dim tmpTest As TempVar

TempVars!tmpTest = "Test1"
TempVars("txtID") = 123456
TempVars.Add "tmpTest2", (456789)
Debug.Print "ymptest:" & TempVars!tmpTest
Debug.Print "txtID:" & TempVars("txtID")
Debug.Print "tmpTest2:" & TempVars!TmpTest2

TempVars.Add "tmpTest2", "second"
Debug.Print "tmpTest2:" & TempVars!TmpTest2
TempVars!TmpTest2 = 789
Debug.Print "tmpTest2:" & TempVars!TmpTest2

TempVars.Add "tmpTest3", 123
Debug.Print "tmpTest2:" & TempVars!TmpTest3
TempVars.Add "tmpTest3", 1234
Debug.Print "tmpTest2:" & TempVars!TmpTest3

TempVars!TmpTest4 = "last test"
Debug.Print "tmpTest2:" & TempVars!TmpTest4

End Sub

Anyway I will stick to my method, even though I do not Dim them (I Dim every other variable)
Run that code yourself to see the output.

 

[xenou]

Is a tempvars value accessible to the query that needs to run (e.g. as a parameter)? For no particular reason I've never used TempVars. Another solution would be to use a hidden textbox on the form (which I think is what Mac1206 was trying to do originally).

 

[Mac1206]

Is a tempvars value accessible to the query that needs to run (e.g. as a parameter)? For no particular reason I've never used TempVars. Another solution would be to use a hidden textbox on the form (which I think is what Mac1206 was trying to do originally).

You are right, initially I was trying to use a unbound text box name txtID but was not sure if I had to place the text box in the header or details section of the form...My form is based on a 3 parameter for 1. pymtdate, 2. Pymt_Source, and 3. Pymt_Custodian...

Once I put in these parameters, I run my open form button based on that....I also created a Mktbl based on that parameter query in order to create a query...

 

[Mac1206]

You are right, initially I was trying to use a unbound text box name txtID but was not sure if I had to place the text box in the header or details section of the form...My form is based on a 3 parameter for 1. pymtdate, 2. Pymt_Source, and 3. Pymt_Custodian...

Once I put in these parameters, I run my open form button based on that....I also created a Mktbl based on that parameter query in order to create a query...

My Recordset query is based on the table made from the parameter query so this might be the issue as to why my unbound txtID box was not working on the form since it is based on the parameter query...I'm a visual person and don't quite know all the terms used in VB as of yet...Thanks

In welshgasman example, I might have up to 500 different client files so would I have to create values for each?

 

[welshgasman]

Is a tempvars value accessible to the query that needs to run (e.g. as a parameter)? For no particular reason I've never used TempVars. Another solution would be to use a hidden textbox on the form (which I think is what Mac1206 was trying to do originally).

@xenou
Yes, I have used it a lot for the reason that it can be set from pretty much anywhere.?