Having trouble getting a report to use two different recordsets (1 Viewer)

[chuckcoleman]

Hi,

I want to open a report and use one of two different queries based on the value of a field in a form.

In the code below UseTop100 looks at the value of a field using DLookUp on a form. Based on if that value if True I want to use the query "Startup Letter Query-2-Top100". If the value is False I want to use the query "Startup Letter Query-2". I don't get an errors and the report opens, but it doesn't have any records in either case. If I run the queries separately, there are records.

What am I doing wrong?

Thanks!

Private Sub ViewStartupLetterX_Click()
'On Error GoTo ViewStartupLetterX_Click_Error

Dim UseTop100 As Boolean
Dim rpt As String
Dim strRecordSource As String
Dim rs100 As DAO.Recordset
Dim rsAll As DAO.Recordset
Dim db As DAO.Database

Set db = CurrentDb()
Set rs100 = db.OpenRecordset("Startup Letter Query-2-Top100")
Set rsAll = db.OpenRecordset("Startup Letter Query-2")

UseTop100 = DLookup("Top100", "Admin Table", "LimitNbr=1")

If UseTop100 = True Then
 strRecordSource = rs100
ElseIf UseTop100 = False Then
 strRecordSource = rsAll
End If

rpt = "Startup Customer Letter"
DoCmd.OpenReport rpt, acViewPreview, , strRecordSource

'Set rpt = Nothing
 
ViewStartupLetterX_Click_Exit:
  Exit Sub
ViewStartupLetterX_Click_Error:
  MsgBox Err.Description
  Resume ViewStartupLetterX_Click_Exit

End Sub

 

[Gasman]

How is that a valid WHERE value?

DoCmd.OpenReport method (Access)

Office VBA reference topic

learn.microsoft.com

One way would be to give it a static record source named query, then copy the relevant query as that recordsource query name, before opening the report. Probably the eaisest way to go.

 

[chuckcoleman]

Gasman, I don't understand your question. I set rs100 to one of the queries and rsAll to the other query. in the DoCmd.OpenReport I open the right report and I think I'm setting the recordset to one of the queries after the preview, two commas and then the strRecordSource. The WHERE statement is in the queries if that is your question.

 

[Pat Hartman]

You've got a lot of unnecessary code. AND, more importantly, you've got it in the wrong place. The setting of the RecordSource belongs in the report's Open event, NOT where you have it. The click event then just needs to open the report
rpt = "Startup Customer Letter"
DoCmd.OpenReport rpt, acViewPreview

I also don't think the Dlookup() is what I would use to control the RecordSource. I would probably use a combo or other control on a form where the user selects the option. Then the report uses that option. In that case, you would just replace the dlookup() with a reference to a form control:
UseTop100 = Forms!yourForm!CriteriaOption

Private Sub Form_Open(Cancel As Integer)
Dim UseTop100 As Boolean
On Error GoTo ErrProc

UseTop100 = DLookup("Top100", "Admin Table", "LimitNbr=1")

If UseTop100 = True Then
    Me.RecordSource =  "Startup Letter Query-2-Top100"
Else
    Me.RecordSource =  "Startup Letter Query-2"
End If

ExitProc:
      Exit Sub
ErrProc:
    Select Case Err.Number
        Case Else
              MsgBox Err.Number & " -- " & Err.Description
              Resume ExitProc
    End Select

End Sub

 

[Gasman]

The Where clause is just that, criteria without the Where keyword, not the value of a recordset.
That is just nonsense.

 

[chuckcoleman]

Hi Pat, your feedback worked! Thank you, I always appreciate your clarity. As you indicated, the biggest issue I had was not placing the code in the On Open section of the Report. I looked at a lot of examples online and none of them suggested or addressed that. Thank you again for your clarity. Take care. Chuck

 

[MajP]

If you want to open a report and modify the records returned on a report you can use the Where condition passing in a criteria. That is a filter on a specific field/s. That is limited and does not work in your case. That does not allow you to do such things as change the Top records returned, change the sort order, or completely change the query.

If you want to pass in a different recordsource (query) beyond what you can do with a simple wherecondition, you can use the openArgs argument and pass in a query name. Then in the reports on open (not load).

Private Sub Report_Open(Cancel As Integer)
  If Me.OpenArgs & "" <> "" Then
    Me.RecordSource = Me.OpenArgs
  End If
End Sub

so from a form you can pass in a different query name and open the report with many different queries

DoCmd.OpenReport "rptName", acViewPreview, , , , "qryName"

 

[chuckcoleman]

Hi MajP. I couldn't use the same query because I created a new table that became the criteria for one of the two queries by joining it to other tables. This is kind of an experiment where I had Access pick on a random basis 100 customer ID's where those clients/ID's don't have an email address, print out the letter and track if those customers reply by placing an order. It's to evaluate if the cost of printing and postage vs. the positive revenue impact of the customer placing an order. Based on the results it will determine if they continue to use USPS postage for those without email addresses. Note: every correspondence/invoice sent always requests an email address if we don't have one.

I do appreciate your feedback.

 

[MajP]

Hi MajP. I couldn't use the same query because I created a new table that became the criteria for one of the two queries by joining it to other tables.

That is kind of my point, but unsure because your code has a myriad of problems.

But you appear (incorrectly) to try to use the wherecondition argument but it seems your are trying to pass in a different recordsource not a where condition.

DoCmd.OpenReport rpt, acViewPreview, , strRecordSource

The point is the where condition argument takes in a SQL where statement without the word WHERE. As @Gasman stated. You cannot pass in a SQL string, table name, or query name. If you want to pass in a different recordsource (not a criteria) then you need to pass that in using OpenArgs and the report can set the recordsource to the openargs.

 

[Gasman]

You cannot pass in a SQL string, table name, or query name. If you want to pass in a different recordsource (not a criteria) then you need to pass that in using OpenArgs and the report can set the recordsource to the openargs.

My understanding was they were not even doing that, but trying to supply a recordset object assigned to a string.?
I do not have the DB to walk the code to see what strRecordSource contains, nor do I have the time to write a bunch of code to test it myself.

Edit:
I have just tried and got a Type Mismatch on the strSource = rs line, as I suspected should have happened?

Sub ReportCrap()
Dim strSource As String
Dim rs As DAO.Recordset
On Error Resume Next

Set rs = CurrentDb.OpenRecordset("qryTestTransactions")
strSource = rs
DoCmd.OpenReport "rptTransactions", , , strSource
Set rs = Nothing
End Sub

 

[ebs17]

A small supplement:

Dim rs100 As DAO.Recordset
Dim strRecordSource As String
' ...
strRecordSource = rsAll    ' <= error

A recordset is an object. You can't assign something like that to a string variable.

If you want to directly use a recordset that you already have somewhere, you would have to assign an object:

' report is already open
Set Reports("ReportName").Recordset = rsAll

 

[MajP]

A small supplement:

Dim rs100 As DAO.Recordset
Dim strRecordSource As String
' ...
strRecordSource = rsAll    ' <= error

A recordset is an object. You can't assign something like that to a string variable.

If you want to directly use a recordset that you already have somewhere, you would have to assign an object:

' report is already open
Set Reports("ReportName").Recordset = rsAll

You cannot do that with a report. You cannot assign a recordset to a report like you can with a form afaik.

 

[ebs17]

This already existed for ADP.

But the real question is, if you wanted to assign a recordset, you would have to do it this way: object to object property of a form, a report, a listbox, a combobox.

 

[MajP]

But the real question is, if you wanted to assign a recordset, you would have to do it this way: object to object property of a form, a report, a listbox, a combobox.

That makes no sense. We are talking about reports as far as I can tell not other objects. I already said it does not work for a report.

This already existed for ADP

It works for an ADP. Who gives a crap about ADP? A deprecated unused technology. If you want to modify the recordsource beyond what can be done in the where or filter arguments of the docmd then pass in the recordsource (sql string, query, table name) in the open args. And you have to assign this in the open event and not the load event.

 

[MajP]

If you do have an existing recordset and for some reason it is difficult to know ahead of time the recordset's recordsource then simply pass the recordsets name using the same on open code

Private Sub cmdUseExisting_Click()
  Dim rs As DAO.Recordset
  Set rs = CurrentDb.OpenRecordset("Select Top 50 * from tblPersonnel Order by PersonID")
  DoCmd.OpenReport "rptTest", acViewPreview, , , , rs.Name

End Sub

The above is kind of a dumb example but proves the point that you can get the sql string from a recordset and pass it.

See demo of how to change the recordsource of reports. This method supports print preview.
Shows passing in queries, sql string, and recordset name property.

 

[ebs17]

You want to be right, and I agree with you.
But one should still know the difference between an object and a string, and one should be able to handle both.
The savant cannot contradict that, can he?

 

[MajP]

But one should still know the difference between an object and a string, and one should be able to handle both. The savant cannot contradict that, can he?

Where am I contradicting that? Yes everyone should know that, but using incorrect examples of this does not help.
There is so much wrong in the OPs code, I can only guess what the OP is trying. Not knowing the difference between strings and objects is one. Not knowing what the arguments of the docmd.openreport does is another.

I am contradicting this.

Set Reports("ReportName").Recordset = rsAll

Because it does not work except for a completely obscure ADP that 99.9% of the people will never see. It is confusing and not illustrative.