Use VBA to change Report parameter. DoCmd.SetParameter

[anymuon]

I have been trying to bypass the parameter query used in a Report Record Source using DoCmd.SetParameter in VBA. I've not used this format and I can't change the base Report Record Source and remove the parameter. Iseem to be a little thick in the head on the use of DoCmd.SetParameter.

I get the following error:

Run-time error '2766':

The object doesn't contain the Automation object 'IN5201234.'

when running this code off of a button:

Private Sub Print_Click()

DoCmd.SetParameter "Number0", Me.Text42
DoCmd.OpenReport "qCWS", acViewReport

End Sub

The parameter query that filters the report is:

SELECT CWS.NUMBER0, CWS.[System Name], CWS.ACTIVITY_STATUS_CD, CWS.TINWSYS_IS_NUMBER, CWS.Status, CWS.[Full Name]
FROM CWS
WHERE (((CWS.NUMBER0) Like UCase([Enter PWSID (Use IN + PWSID)] & "*")));

Misc info:
This is not a database I work with or designed and is >20yrs old. The reports use a parameter query to fill in a number for the report record source. They want to restrict the dozens of reports down for a particular group. I've included a test database. Right now, I am just working through the coding elements.

Use IN5201234 when testing.

 

[MajP]

The parameters name is not the name of the field, but the text inside of []

DoCmd.SetParameter "[Enter PWSID (Use IN + PWSID)]", "'" & Me.Text42 & "'"
DoCmd.OpenReport "rpqCWS", acViewReport

However the real thing that makes this work is putting the text inside of single quote delimiters. I do not understand why that is required but it works. Usually the beauty of using parmas is that you do not have to delimit them.

 

[anymuon]

Misunderstood what object SetParameter was pointing to or how to point to the underlying parameter in the query.


Revised Code:

Private Sub Print_Click()


Dim strPWSID

strPWSID = Me.Text42.Value

DoCmd.SetParameter "Enter PWSID (Use IN + PWSID)", Chr(34) & strPWSID & Chr(34)

DoCmd.OpenReport "rpqCWS", acViewReport


End Sub

 

[MajP]

FYI,
No code is needed if you set the form's parameter to
Like [Forms]![fmInspRpts]![Text42] & "*"

 

[anymuon]

Let's try this again. Forum wouldn't let me Post my initial reply to my post. Perhaps I can't post a link.

But some housecleaning.

MajP quite correct. I had implemented same format found here in post 1713588, which is the same as MajP's post.

This code won't work, even though Me.Text42.Value will have a correct value:

Private Sub Print_Click()


DoCmd.SetParameter "Enter PWSID (Use IN + PWSID)", Me.Text42.Value

DoCmd.OpenReport "rpqCWS", acViewReport

 

[anymuon]

FYI,
No code is needed if you set the form's parameter to
Like [Forms]![fmInspRpts]![Text42] & "*"

Can't make that change to the base report. Not my system nor have I ever used a parameter query in the base report. Using this report parameter query format is the same for over a hundred reports. I would normally just set the report filter in VBA. This way I could filter OnOpen to whatever the user wanted. Here I need to set it once for all the reports the user selects to print.

Did your response get marked as the solution since it came in before I could get the system to take my response? I don't see a check or your response marked as the solution. I'm new to this forum.

 

[MajP]

Does this work for opening multiple reports? Have you tried it?

This way I could filter OnOpen to whatever the user wanted. Here I need to set it once for all the reports the user selects to print.

According the MSDN I would think once you run the first open report the parameters collection is cleared.

Each call to SetParameter adds or updates a single parameter in an internal parameters collection. The parameters collection is passed to the BrowseTo, OpenForm, OpenQuery, OpenReport, or RunDataMacro method. When the method is run, the parameters collection supplies the needed parameters. When the method is finished, the parameters collection is cleared.

I have never used this method, so I do not know.

 

[anymuon]

Does this work for opening multiple reports? Have you tried it?

According the MSDN I would think once you run the first open report the parameters collection is cleared.

I have never used this method, so I do not know.

Thanks for the response.

Not yet, I'm just at the code outline stage. Once I get the framework working together, I will clean up the code and add error checking.

Correct. I will just loop the ID (PWSID or Number0) through the Listbox.Selected after pressing the print button and call each selected report since the parameter value could change. Hence, why the Open.Report is right after the SetParameter call.

I'll give them four buttons, Print Preview, Print to Printer, Print to pdf or cancel. I like a little cluster of buttons to run a defined set of shortrcuts as opposed to multiple msgboxes asking for some additional decision. I will probably add a fifth to close all selected reports.

 

[MajP]

So within your loop you might have to reset the parameter before each report prints.

 

[anymuon]

Removed an unnecessary loop. This is something like I normally do. Without the parameter query. I have not tested this. It is in the beginning stages.

I would just have to reset the report name, some pseudo code:

'untested code for discussion only

With ctlrptselected
    'loop through all listbox items
    'to see which are selected

For Each varitem In .ItemsSelected
         strgrp = .ItemData(varitem)

         'print each of the selected reports
          With ctlrptselected

              'loop & find selected reports
              ' print
              
                   If .Selected(introw) = True Then
                        strrptname = .column(1, introw)
                        strPWSID = Me.Text42.Value
                        'Parameter and report name get reset here. Best to keep 1, 2 together.
                        DoCmd.SetParameter "Enter PWSID (Use IN + PWSID)", _
                            & Chr(34) & strPWSID & Chr(34)
                        DoCmd.OpenReport strreportname, acViewReport
                 
                    End If
               
         End With
    Next varitem
End With

 

[Gasman]

Why not use a TempVar?
Set it once and use it for all the reports.

 

[anymuon]

Could, for the number out of the textbox. For this, it is about 6, 1/2 dozen or the other. There is just no real gain in execution or code management. I used to smack most everything in subs, functions and variables. I still do, but sometimes it makes more sense just write out the few lines of code to do the job and repeat if needed elsewhere.

For example, I initially set up the listboxes as generic subs and set variables to feed into it when I was exploring using just an option group. But there were issues with loading the option group. Messy to write some controls like option boxes at runtime. So, I added the grouping lisbox and I have around 10 lines of code between them. There is only about 6 lines running the multiselect box. The Select Case statement was going on 20 lines and still I was not reaching all of my objectives, plus I still had the 6-10 for the listbox control plus additional in control if thens to focus the listbox. More than doubling the code and potential problem areas. I will end up repeating some of the listbox code a couple of times, but not enough to warrant subs for each component that are fed by if's or Select Cases. Seems a little sloppy, but sometimes it is just easier to compartmentalize the code units. I also think in terms of other groups who use this database and a couple of other databases that pull data. I can just grab the code elements if they want something similar without having to make sure I getting all of the variables and keeping them cleaned up.