Worked like a champ! The next step I need to take is give the user the
ability to select the filter criteria. If I change the script to:
Private Sub cmdOpenProjectReport_Click()
On Error GoTo Err_cmdOpenProjectReport_Click
Dim stDocName As String
stDocName = "rptStandard"
DoCmd.OpenReport stDocName, acPreview, , "[strMachineType] =
Enter_Machine_Type"
Exit_cmdOpenProjectReport_Click:
Exit Sub
Err_cmdOpenProjectReport_Click:
MsgBox Err.Description
Resume Exit_cmdOpenProjectReport_Click
End Sub
I get a popup asking to "Enter_Machine_Type", which if I input a valid entry
works fine. The issue I have is that the user has no idea what valid entries
are (or at least all of them). What I want to do is have the pop up display
the valid filter entries for strMachineType. strMachineType is populated by
a combo box on Form="frmMain". This combo box is controlled by
tblProjectTypes.strProjectTypes. So ultimately what I would like is for the
popup to have a combo box with all the value in
tblProjectTypes.strProjectTypes and the user could then just select the valid
entry. Not sure if this is possible or not.
Duane Hookom said:
Try this code:
Private Sub cmdOpenProjectReport_Click()
On Error GoTo Err_cmdOpenProjectReport_Click
Dim stDocName As String
stDocName = "rptStandard"
DoCmd.OpenReport stDocName, acPreview, , "[strMachineType] = 'TBM'"
Exit_cmdOpenProjectReport_Click:
Exit Sub
Err_cmdOpenProjectReport_Click:
MsgBox Err.Description
Resume Exit_cmdOpenProjectReport_Click
End Sub
--
Duane Hookom
Microsoft Access MVP
:
I started down the filter road to see where I could get. All is well if the
filter is coded in the properties of the report, but if I want to change the
filter via code on a button I can't get it to work. I have a report named
"rptStandard", which has has a record source of "qryMainTopList" defined in
the report properties. I have a button of a form that calls this report via
the following code:
Private Sub cmdOpenProjectReport_Click()
On Error GoTo Err_cmdOpenProjectReport_Click
Dim stDocName As String
stDocName = "rptStandard"
DoCmd.OpenReport stDocName, acPreview
Me.Filter = "[strMachineType] = 'TBM'"
Me.FilterOn = True
Exit_cmdOpenProjectReport_Click:
Exit Sub
Err_cmdOpenProjectReport_Click:
MsgBox Err.Description
Resume Exit_cmdOpenProjectReport_Click
End Sub
where strMachineType is the control source of Text Box:Machine_Type in
"rptStandard" I am trying filter by. I get no errors it just doesn't filter.
I have tried the following variations with no luck:
Me.Filter = "strMachineType = 'TBM'"
Me.Filter = "Machine_Type = 'TBM'"
:
That's a fairly accurate error message. You can change the Record Source in
the On Open event of the report. You could also modify the SQL property of
the report's recordsource saved query prior to opening the report.
If the "Multiple Queries" are just different records from the same
tables/queries then you are much better off just changing the criteria/filter.
--
Duane Hookom
Microsoft Access MVP
:
I am trying to use the same report layout for multiple queries but am having
some trouble. The button code is:
Private Sub cmdOpenProjectReport_Click()
On Error GoTo Err_cmdOpenProjectReport_Click
Dim stDocName As String
stDocName = "rptStandard"
DoCmd.OpenReport stDocName, acPreview
Reports!rptStandard.RecordSource = "qryMainTopList"
Exit_cmdOpenProjectReport_Click:
Exit Sub
Err_cmdOpenProjectReport_Click:
MsgBox Err.Description
Resume Exit_cmdOpenProjectReport_Click
End Sub
The problem is that when I click the button I get an error message saying
“You can’t set the Record Source property in print preview or after printing
has startedâ€.
