1 Report, Multiple Queries

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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â€.
 
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.
 
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'"
 
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


jutlaux said:
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'"

Duane Hookom said:
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.
Click to expand...
Click to expand...
 
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


jutlaux said:
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'"

Duane Hookom said:
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â€.
Click to expand...
Click to expand...
Click to expand...
 
I would place a combo box on the form where the code is running.

Private Sub cmdOpenProjectReport_Click()
On Error GoTo Err_cmdOpenProjectReport_Click
Dim stDocName As String
Dim strWhere as String
stDocName = "rptStandard"
strWhere = "1=1 "
If Not IsNull(Me.cboMachType) Then
strWhere = strWhere & " And [strMachineType]= """ & _
Me.cboMachType & """"
End If
DoCmd.OpenReport stDocName, acPreview, , strWhere

Exit_cmdOpenProjectReport_Click:
Exit Sub
Err_cmdOpenProjectReport_Click:
MsgBox Err.Description
Resume Exit_cmdOpenProjectReport_Click
End Sub

--
Duane Hookom
Microsoft Access MVP


jutlaux said:
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


jutlaux said:
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â€.
Click to expand...
Click to expand...
Click to expand...
 
Worked great. Thanks for all your help.

Duane Hookom said:
I would place a combo box on the form where the code is running.

Private Sub cmdOpenProjectReport_Click()
On Error GoTo Err_cmdOpenProjectReport_Click
Dim stDocName As String
Dim strWhere as String
stDocName = "rptStandard"
strWhere = "1=1 "
If Not IsNull(Me.cboMachType) Then
strWhere = strWhere & " And [strMachineType]= """ & _
Me.cboMachType & """"
End If
DoCmd.OpenReport stDocName, acPreview, , strWhere

Exit_cmdOpenProjectReport_Click:
Exit Sub
Err_cmdOpenProjectReport_Click:
MsgBox Err.Description
Resume Exit_cmdOpenProjectReport_Click
End Sub

--
Duane Hookom
Microsoft Access MVP


jutlaux said:
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â€.
Click to expand...
Click to expand...
Click to expand...
 
I am trying to filter the report by the control source ysnKeepInTopList.
This is a checkbox on From=frmMain. When I run the script below I get a
"Data type mismatch in critical expression". I have tried 'YES', 'Yes',
'TRUE', 'True', '-1' but I always get the same error.

Private Sub cmdOpenReportByTopList_Click()
On Error GoTo Err_cmdOpenReportByTopList_Click
Dim stDocName As String

stDocName = "rptStandard"

DoCmd.OpenReport stDocName, acPreview, , "[ysnKeepInTopList] = 'Yes'"

Exit_cmdOpenReportByTopList_Click:
Exit Sub
Err_cmdOpenReportByTopList_Click:
MsgBox Err.Description
Resume Exit_cmdOpenReportByTopList_Click
End Sub

Duane Hookom said:
I would place a combo box on the form where the code is running.

Private Sub cmdOpenProjectReport_Click()
On Error GoTo Err_cmdOpenProjectReport_Click
Dim stDocName As String
Dim strWhere as String
stDocName = "rptStandard"
strWhere = "1=1 "
If Not IsNull(Me.cboMachType) Then
strWhere = strWhere & " And [strMachineType]= """ & _
Me.cboMachType & """"
End If
DoCmd.OpenReport stDocName, acPreview, , strWhere

Exit_cmdOpenProjectReport_Click:
Exit Sub
Err_cmdOpenProjectReport_Click:
MsgBox Err.Description
Resume Exit_cmdOpenProjectReport_Click
End Sub

--
Duane Hookom
Microsoft Access MVP


jutlaux said:
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â€.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top