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]

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

How do I execute your test code, just drop it in a module and add the valid ID that suppose to loop through the code? I'm a real novice at this...

 

[welshgasman]

How do I execute your test code, just drop it in a module and add the valid ID that suppose to loop through the code? I'm a real novice at this...

No just use
TempVars("txtID") = rst!ID then run the second query

 

[xenou]

I guess the query needs to refer to the tempvar value as well.

Either,
WHERE FieldName = TempVars!MyVal

Or
WHERE FieldName = Eval("TempVars!MyVal")

In other words, you are using tempvars to share data. The code you are writing needs to set the value (on each loop) but the query you wrote needs to read the value (also on each loop - this is a parameter that you need to use to get the data you need on that loop for that ID)

 

[welshgasman]

I guess the query needs to refer to the tempvar value as well.

Either,
WHERE FieldName = TempVars!MyVal

Or
WHERE FieldName = Eval("TempVars!MyVal")

In other words, you are using tempvars to share data. The code you are writing needs to set the value (on each loop) but the query you wrote needs to read the value (also on each loop - this is a parameter that you need to use to get the data you need on that loop for that ID)

Well we had this in post #5

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

but no idea as to what the query looks like now.?

 

[xenou]

I guess that works. Funny no different if you had a hidden textbox on a form called TempVars. Moral of the story: TempVars is a funny name for a form

 

[Mac1206]

Well we had this in post #5

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

but no idea as to what the query looks like now.?

No just use
TempVars("txtID") = rst!ID then run the second query

Hi welshgasman, I attempted to use TempVars("txtID") = rst!ID which is presenting a error for the "txtID" which is bringing in a Null value, the rst!ID is pulling in the value for the first record 48951

 

[Mac1206]

Hi welshgasman, I attempted to use TempVars("txtID") = rst!ID which is presenting an error for the "txtID" which is bringing in a Null value, the rst!ID is pulling in the value for the first record 48951

The Query is the same as in Post #5 and in order to recieve records from that query, I modified the WHERE Clause to:

(((T_Payment_Details.ID)=Like "*" &[TempVars]![txtID] & "*"));

 

[welshgasman]

Hi welshgasman, I attempted to use TempVars("txtID") = rst!ID which is presenting a error for the "txtID" which is bringing in a Null value, the rst!ID is pulling in the value for the first record 48951

It is not bringing in Null, it is just not assigning it for some reason
What is the error? full description please, not just an error number.

I do not know what to tell you?, the syntax is correct
From the immediate window

tempvars("txtID")=458789
? tempvars("txtID")
458789

tempvars!txtid = "fred"
? tempvars!txtID
fred

 

[Mac1206]

It is not bringing in Null, it is just not assigning it for some reason
What is the error? full description please, not just an error number.

I do not know what to tell you?, the syntax is correct
From the immediate window

tempvars("txtID")=458789
? tempvars("txtID")
458789

tempvars!txtid = "fred"
? tempvars!txtID
fred

The full error is Run-Time error 32535
TempVars can only store data. They cannot store Objects.

It is not bringing in Null, it is just not assigning it for some reason
What is the error? full description please, not just an error number.

I do not know what to tell you?, the syntax is correct
From the immediate window

tempvars("txtID")=458789
? tempvars("txtID")
458789

tempvars!txtid = "fred"
? tempvars!txtID
fred

tempvars("txtID")=48951
? tempvars("txtID")
48951

Came back so I'm not understanding the issue...

 

[Mac1206]

Now my tempvars("txtID") = 48959 and rst![ID] 48951 at least it's showing a value but the wrong one...The txtID is showing the last value I tested...