If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
1 Report, Multiple Queries
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”. |
#2
|
|||
|
|||
1 Report, Multiple Queries
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 "jutlaux" wrote: 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”. |
#3
|
|||
|
|||
1 Report, Multiple Queries
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" wrote: 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 "jutlaux" wrote: 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”. |
#4
|
|||
|
|||
1 Report, Multiple Queries
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" wrote: 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" wrote: 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 "jutlaux" wrote: 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”. |
#5
|
|||
|
|||
1 Report, Multiple Queries
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" wrote: 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" wrote: 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" wrote: 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 "jutlaux" wrote: 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”. |
#6
|
|||
|
|||
1 Report, Multiple Queries
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" wrote: 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" wrote: 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" wrote: 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" wrote: 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 "jutlaux" wrote: 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”. |
#7
|
|||
|
|||
1 Report, Multiple Queries
Worked great. Thanks for all your help.
"Duane Hookom" wrote: 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" wrote: 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" wrote: 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" wrote: 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" wrote: 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 "jutlaux" wrote: 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”. |
#8
|
|||
|
|||
1 Report, Multiple Queries
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" wrote: 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" wrote: 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" wrote: 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" wrote: 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" wrote: 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 "jutlaux" wrote: 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”. |
Thread Tools | |
Display Modes | |
|
|