Can I set the Recordset on a report when I call for it to open 1

[ckeener]

I am trying to get a report to open with information based on selection on my form, but when I try to set the record source, I get an error that I can't change the recordsource while the report is printing. Can this be done? This is my code that is called in the frmPrintLogs.

Private Sub cmdPrint_Click()
    Dim lstitem As Variant
    Set db = CurrentDb
    For Each lstitem In Me.lstLog.ItemsSelected
         strSQL = "SELECT * FROM tblLogs WHERE LogRef = " & lstLog.Column(0, lstitem)
         DoCmd.OpenReport "rptLog", acViewPreview
         Reports!rptLog.RecordSource = strSQL
    Next
End Sub

?!?!?!?!

 

[dhookom]

First, you will have a very difficult time opening multiple instances of the same report. Is there a reason why you think you need to do this?

You can change the record source of a report in the On Open event of the report.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[ckeener]

The users need to be able to select multiple entries on a listbox in frmPrintLogs and have all the selected logs print. Each report contains all the information about 1 log.

I guess if I could just open one instance of the report with only the selected records included, that would be exactly what I needed. I didn't realize that it would be so difficult.

Thanks,

Ck

 

[dhookom]

You could have sent all the individual reports straight to the printer and I believe that would work. Can you just filter a single report on all the selected logs with page breaks between the logs?

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[ckeener]

That sounds like a good idea, but how? I am fairly new to reports. So far this is the first one that could not be done with an Access wizard. Thanks for all the ideas Duane.

 

[dhookom]

There is an FAQ faq703-3936 that uses code to create a where condition that can be used in a DoCmd.OpenReport command. You would set your report to group by Log and page break either before or after the group.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[ckeener]

Thanks Duane,

It is working exactly the way I wanted it to. Thanks for pointing my efforts in the correct direction. This is the code that works for me if any one elese has the same problem.

Private Sub Report_Open(Cancel As Integer)
    Dim lstitem As Variant
    strSQL = "SELECT * FROM tblLogs WHERE LogRef = "
    For Each lstitem In Forms!frmPrintLogs.lstLog.ItemsSelected
        strSQL = strSQL & _
            Forms!frmPrintLogs.lstLog.Column(0, lstitem) & _ 
            " OR LogRef = "
    Next
    strSQL = Left(strSQL, Len(strSQL) - 13) 'Remove last _
         "OR LogRef = " from strSQL
    strSQL = strSQL & ";"
    Me.RecordSource = strSQL
End Sub

 

[MichaelRed]

I find that changing the recordsource of a report is somewhat cumbersome, and usually quite slow. My 'workaround" for this is to change the recordsource itself of the report (e.g. the QUERY) to have the data. This can often be done simply by having the criteria be based on values on a form and set as parameters in the query.




MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[ckeener]

Micheal,
Do you mind showing me an example of what you are talking about. Call me dumb, but I wasn't aware that the content of an existing query could be changed in the code. This sounds interesting.

Thanks,
Ck

 

[dhookom]

Michael,
In this case, Ck has a multi-select list box providing the criteria for the query. You can't use a simple reference to the listbox in the saved query. Ck would have to change the SQL of the querydef in order to use a saved query.

The method that I regularly use is to not set a criteria in the report and not set the report's record source on open. I build the "IN (....)" clause and use it in the DoCmd.OpenReport line. An example of this is FAQ703-3936.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[MichaelRed]

dhookom, you can set the parameters for a query in a procedure, and use the results as the recordsource of the report which is also opened in the procedure. Not so different from your building the subquery (IN clause). In either case, the basic source for the recordset (of the report) would need to include the capability to insert the additional information.

ckeener, you have all of the essiential elements in your post where you say it is working for you.




MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[ckeener]

So basically, strSQL is my query. Like this

Private Sub cmdSave_Click()
    Dim lstitem As Variant
    strSQL = "SELECT * FROM tblLogs WHERE LogRef = "
    For Each lstitem In Me.lstLog.ItemsSelected
        strSQL = strSQL & Me.lstLog.Column(0, lstitem) & " OR LogRef = "
    Next
    strSQL = Left(strSQL, Len(strSQL) - 13) 'Remove last " OR LogRef = " from strSQL
    strSQL = strSQL & " ORDER BY LogNumber;"
    qrytest = strSQL
    DoCmd.OpenReport "rptLog", acViewPreview
End Sub

and the recordsource of my report just equals qrytest, right?

 

[dhookom]

Let's assume we have a function that returns an IN clause

Function GetIn() as String
GetIn= "IN (1,43,55,95,125)"
End Function

You can't use this function in a saved query with sql like:
SELECT tblEmployee.*
FROM tblEmployee
WHERE Employee ID = GetIn();
Or, if you have a text box on a form Forms!frmA!txtMyIn that contains the value:
"(1,43,55,95,125)"
You can't use this control reference in a saved query with sql like:
SELECT tblEmployee.*
FROM tblEmployee
WHERE Employee ID = Forms!frmA!txtMyIn;
I have suggested two methods of using the multi-select list box and Ck has suggested a third (setting the report's record source on open).


Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[MichaelRed]

or something similar to this:

Private Sub cmdRpt_Click()

    'Report Source Query (SQL) _
     SELECT tblServicePlans.* FROM tblServicePlans WITH OWNERACCESS OPTION;


    Dim strCriteria As String

    strCriteria = "[Company] = " & Chr(34) & Me.cboCompany & Chr(34) _
                & " and [ServicePlan] = " & Chr(34) & Me.cboPlan & Chr(34)

    DoCmd.OpenReport "rptDetails", acPreview, , strCriteria

End Sub
[code]

re the ubiquitous [b]{F1}[/b] (a.k.a Help) on OpenReport

DoCmd.OpenReport reportname[, view][, filtername]
[, wherecondition]

It is the last element ([b][i]wherecondition)[/i][/b], which is set / represented by "strCriteria" in my simplistic example.

Although this may be in confllict with the basic source query of your report, as -if it includes pparameters- the prompt(s) will still appear, must be responed to and will be effective.  Note that the qrySql in my example id just a comment, and NOT manipulated.




MichaelRed
m.red@att.net

Searching for employment in all the wrong places