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 |
#11
|
|||
|
|||
Filter report on a form
Does your record source for the report reference the values on the controls on
the form to filter the records? Post the SQL view of the record source. Or are you trying to build a filter based on the form's control values? John Spencer Access MVP 2002-2005, 2007-2009 The Hilltop Institute University of Maryland Baltimore County Arlene wrote: John, I tought I was done, but I am not! . I followed your steps here and they worked very nice, the problem is that now the reports opens up showing the same information no matter what is selected from the combo box. What am I missing? "John Spencer" wrote: It can be done, but I would advise against it. In the REPORTS open event, you would need to open the form in dialog mode (that halts all processing except in the opened form), set the values, and use the button to set the form's visible property to false. At this point the report can grab the values from the form. Then in the Report's close event, use code to close the form. John Spencer Access MVP 2002-2005, 2007-2009 The Hilltop Institute University of Maryland Baltimore County Arlene wrote |
#12
|
|||
|
|||
Filter report on a form
I have that on the button on the form to open the report based on the
criteria I selected from the combo box to open the report and close the form once the report is open. "John Spencer" wrote: What are you attempting to do with these lines? DoCmd.SelectObject acForm, "Select Unit Classification" DoCmd.Close If the form you want to use is Select Unit Classification then you cannot do it from this button after you have already opened the report. TOO LATE. Also using DoCmd.Close without specifying the object type and specific object name is asking for trouble. DoCmd.Close will close whatever object currently has the focus. That code be the form you are on, the report you just opened, or the form you just opened. That code does not look like what I suggested. Are you trying to open a form to get parameters and then open the report? John Spencer Access MVP 2002-2005, 2007-2009 The Hilltop Institute University of Maryland Baltimore County Arlene wrote: Thank you John, with this the reports opens the form, but when I select the criteria from the form another window pops up asking for the parameter value. I am guessing I need to change something on the form. This is what I have on the MSV for the button on my form: Private Sub Command9_Click() On Error GoTo Err_Command9_Click Dim stDocName As String stDocName = "Rpt Position Summary by Unit Class" DoCmd.OpenReport stDocName, acPreview DoCmd.SelectObject acForm, "Select Unit Classification" DoCmd.Close Exit_Command9_Click: Exit Sub Err_Command9_Click: MsgBox Err.Description Resume Exit_Command9_Click End Sub "John Spencer" wrote: == Open the report in design view == Click in the little square at the top left of the window (where the rulers intersect) == If the properties pane is not showing, right-click on the square and select properties. == Click on the EVENT TAB == Click in the ON Open == Select the Event Procedure from the drop down == Click on the ... button The procedure would be something like Private Sub Report_Open(Cancel As Integer) 'Open a form and stop all processing outside the form 'until the form is closed or made invisible Docmd.OpenForm "MyParameterForm",,,,,acdialog End Sub That should open the form. Now you need to code the button on the form to set the form's visible property to False instead of closing the form. Me.Visible = False At this point the form is still available and the recordsource for your report can get information from the form. Why do I advise against it? It just seems to be more complex then opening the form, getting the parameters, and executing the report from a button on the form. It does have the advantage of being able to use the form for input on multiple reports without having to have the user specify which report to open from the parameter form. It does not allow the user to cancel the report without more code. And that introduces the need to trap for errors when you cancel the report based on the trapping whether or not the form is open. If you close the form instead of hiding the visibility, you can then add code to the open event of the report to check if the form is open or not. If the form is closed, then you cancel the report (Cancel = True) Also, you now need to add code to the Close event to close the parameter form. John Spencer Access MVP 2002-2005, 2007-2009 The Hilltop Institute University of Maryland Baltimore County Arlene wrote: How can I do that? I tried to create the open event but I guess I am not doing it correctly. Why do you advise against it? "John Spencer" wrote: It can be done, but I would advise against it. In the REPORTS open event, you would need to open the form in dialog mode (that halts all processing except in the opened form), set the values, and use the button to set the form's visible property to false. At this point the report can grab the values from the form. Then in the Report's close event, use code to close the form. John Spencer Access MVP 2002-2005, 2007-2009 The Hilltop Institute University of Maryland Baltimore County Arlene wrote: Hi, I have a report and form that are "connected" to each other, meaning that I select the cirteria on the form (combo box) and the reports opens based on that criteria. What I want is to do somewhat of the opposite. I want that when I click on the report, the form opens for the user to select the criteria and then the reports will open based on that. I hope I am making sense! |
#13
|
|||
|
|||
Filter report on a form
This is it:
SELECT [UnitClassification], [UnitClassification] FROM tblUnitClassification ORDER BY [UnitClassification]; "John Spencer" wrote: Does your record source for the report reference the values on the controls on the form to filter the records? Post the SQL view of the record source. Or are you trying to build a filter based on the form's control values? John Spencer Access MVP 2002-2005, 2007-2009 The Hilltop Institute University of Maryland Baltimore County Arlene wrote: John, I tought I was done, but I am not! . I followed your steps here and they worked very nice, the problem is that now the reports opens up showing the same information no matter what is selected from the combo box. What am I missing? "John Spencer" wrote: It can be done, but I would advise against it. In the REPORTS open event, you would need to open the form in dialog mode (that halts all processing except in the opened form), set the values, and use the button to set the form's visible property to false. At this point the report can grab the values from the form. Then in the Report's close event, use code to close the form. John Spencer Access MVP 2002-2005, 2007-2009 The Hilltop Institute University of Maryland Baltimore County Arlene wrote |
#14
|
|||
|
|||
Filter report on a form
You can't close the form. If you do it won't be available for the report to
get values from. set the form's visible property to false. Forms("Select Unit Classification").Visible = False Did you try my suggested version for opening the report and then having the report open the form. If so, did it fail and how did it fail? John Spencer Access MVP 2002-2005, 2007-2009 The Hilltop Institute University of Maryland Baltimore County Arlene wrote: I have that on the button on the form to open the report based on the criteria I selected from the combo box to open the report and close the form once the report is open. "John Spencer" wrote: What are you attempting to do with these lines? DoCmd.SelectObject acForm, "Select Unit Classification" DoCmd.Close If the form you want to use is Select Unit Classification then you cannot do it from this button after you have already opened the report. TOO LATE. Also using DoCmd.Close without specifying the object type and specific object name is asking for trouble. DoCmd.Close will close whatever object currently has the focus. That code be the form you are on, the report you just opened, or the form you just opened. That code does not look like what I suggested. Are you trying to open a form to get parameters and then open the report? John Spencer Access MVP 2002-2005, 2007-2009 The Hilltop Institute University of Maryland Baltimore County Arlene wrote: Thank you John, with this the reports opens the form, but when I select the criteria from the form another window pops up asking for the parameter value. I am guessing I need to change something on the form. This is what I have on the MSV for the button on my form: Private Sub Command9_Click() On Error GoTo Err_Command9_Click Dim stDocName As String stDocName = "Rpt Position Summary by Unit Class" DoCmd.OpenReport stDocName, acPreview DoCmd.SelectObject acForm, "Select Unit Classification" DoCmd.Close Exit_Command9_Click: Exit Sub Err_Command9_Click: MsgBox Err.Description Resume Exit_Command9_Click End Sub "John Spencer" wrote: == Open the report in design view == Click in the little square at the top left of the window (where the rulers intersect) == If the properties pane is not showing, right-click on the square and select properties. == Click on the EVENT TAB == Click in the ON Open == Select the Event Procedure from the drop down == Click on the ... button The procedure would be something like Private Sub Report_Open(Cancel As Integer) 'Open a form and stop all processing outside the form 'until the form is closed or made invisible Docmd.OpenForm "MyParameterForm",,,,,acdialog End Sub That should open the form. Now you need to code the button on the form to set the form's visible property to False instead of closing the form. Me.Visible = False At this point the form is still available and the recordsource for your report can get information from the form. Why do I advise against it? It just seems to be more complex then opening the form, getting the parameters, and executing the report from a button on the form. It does have the advantage of being able to use the form for input on multiple reports without having to have the user specify which report to open from the parameter form. It does not allow the user to cancel the report without more code. And that introduces the need to trap for errors when you cancel the report based on the trapping whether or not the form is open. If you close the form instead of hiding the visibility, you can then add code to the open event of the report to check if the form is open or not. If the form is closed, then you cancel the report (Cancel = True) Also, you now need to add code to the Close event to close the parameter form. John Spencer Access MVP 2002-2005, 2007-2009 The Hilltop Institute University of Maryland Baltimore County Arlene wrote: How can I do that? I tried to create the open event but I guess I am not doing it correctly. Why do you advise against it? "John Spencer" wrote: It can be done, but I would advise against it. In the REPORTS open event, you would need to open the form in dialog mode (that halts all processing except in the opened form), set the values, and use the button to set the form's visible property to false. At this point the report can grab the values from the form. Then in the Report's close event, use code to close the form. John Spencer Access MVP 2002-2005, 2007-2009 The Hilltop Institute University of Maryland Baltimore County Arlene wrote: Hi, I have a report and form that are "connected" to each other, meaning that I select the cirteria on the form (combo box) and the reports opens based on that criteria. What I want is to do somewhat of the opposite. I want that when I click on the report, the form opens for the user to select the criteria and then the reports will open based on that. I hope I am making sense! |
#15
|
|||
|
|||
Filter report on a form
I did used your suggestions for the report to open the form and that worked
wonderfully , but I still can't get the form to pull the criteria according to what is selected. It always gives me the same info. I changed the close form for the visible=false and nothing. "John Spencer" wrote: You can't close the form. If you do it won't be available for the report to get values from. set the form's visible property to false. Forms("Select Unit Classification").Visible = False Did you try my suggested version for opening the report and then having the report open the form. If so, did it fail and how did it fail? John Spencer Access MVP 2002-2005, 2007-2009 The Hilltop Institute University of Maryland Baltimore County Arlene wrote: I have that on the button on the form to open the report based on the criteria I selected from the combo box to open the report and close the form once the report is open. "John Spencer" wrote: What are you attempting to do with these lines? DoCmd.SelectObject acForm, "Select Unit Classification" DoCmd.Close If the form you want to use is Select Unit Classification then you cannot do it from this button after you have already opened the report. TOO LATE. Also using DoCmd.Close without specifying the object type and specific object name is asking for trouble. DoCmd.Close will close whatever object currently has the focus. That code be the form you are on, the report you just opened, or the form you just opened. That code does not look like what I suggested. Are you trying to open a form to get parameters and then open the report? John Spencer Access MVP 2002-2005, 2007-2009 The Hilltop Institute University of Maryland Baltimore County Arlene wrote: Thank you John, with this the reports opens the form, but when I select the criteria from the form another window pops up asking for the parameter value. I am guessing I need to change something on the form. This is what I have on the MSV for the button on my form: Private Sub Command9_Click() On Error GoTo Err_Command9_Click Dim stDocName As String stDocName = "Rpt Position Summary by Unit Class" DoCmd.OpenReport stDocName, acPreview DoCmd.SelectObject acForm, "Select Unit Classification" DoCmd.Close Exit_Command9_Click: Exit Sub Err_Command9_Click: MsgBox Err.Description Resume Exit_Command9_Click End Sub "John Spencer" wrote: == Open the report in design view == Click in the little square at the top left of the window (where the rulers intersect) == If the properties pane is not showing, right-click on the square and select properties. == Click on the EVENT TAB == Click in the ON Open == Select the Event Procedure from the drop down == Click on the ... button The procedure would be something like Private Sub Report_Open(Cancel As Integer) 'Open a form and stop all processing outside the form 'until the form is closed or made invisible Docmd.OpenForm "MyParameterForm",,,,,acdialog End Sub That should open the form. Now you need to code the button on the form to set the form's visible property to False instead of closing the form. Me.Visible = False At this point the form is still available and the recordsource for your report can get information from the form. Why do I advise against it? It just seems to be more complex then opening the form, getting the parameters, and executing the report from a button on the form. It does have the advantage of being able to use the form for input on multiple reports without having to have the user specify which report to open from the parameter form. It does not allow the user to cancel the report without more code. And that introduces the need to trap for errors when you cancel the report based on the trapping whether or not the form is open. If you close the form instead of hiding the visibility, you can then add code to the open event of the report to check if the form is open or not. If the form is closed, then you cancel the report (Cancel = True) Also, you now need to add code to the Close event to close the parameter form. John Spencer Access MVP 2002-2005, 2007-2009 The Hilltop Institute University of Maryland Baltimore County Arlene wrote: How can I do that? I tried to create the open event but I guess I am not doing it correctly. Why do you advise against it? "John Spencer" wrote: It can be done, but I would advise against it. In the REPORTS open event, you would need to open the form in dialog mode (that halts all processing except in the opened form), set the values, and use the button to set the form's visible property to false. At this point the report can grab the values from the form. Then in the Report's close event, use code to close the form. John Spencer Access MVP 2002-2005, 2007-2009 The Hilltop Institute University of Maryland Baltimore County Arlene wrote: Hi, I have a report and form that are "connected" to each other, meaning that I select the cirteria on the form (combo box) and the reports opens based on that criteria. What I want is to do somewhat of the opposite. I want that when I click on the report, the form opens for the user to select the criteria and then the reports will open based on that. I hope I am making sense! |
#16
|
|||
|
|||
Filter report on a form
Where is Command9? Is it a command button on the report, or in another form
(it looks like a form). What does your code look like in the Reports Open or Load event? By failing to include the acDialog as a parameter in the OpenReport method, you are allowing the lines of code in the Command9_Click event to continue, which closes the "Select Unit Classification" form. The line that opens the report should read: docmd.OpenReport stDocName, acPreview, , , acDialog I would then remove all of the lines after that one from the Command9_Click event, and add a single line of code to the REPORTs close event Private Sub Report_Close docmd.close acform, "Select Unit Classification" End Sub HTH Dale "Arlene" wrote in message ... Thank you John, with this the reports opens the form, but when I select the criteria from the form another window pops up asking for the parameter value. I am guessing I need to change something on the form. This is what I have on the MSV for the button on my form: Private Sub Command9_Click() On Error GoTo Err_Command9_Click Dim stDocName As String stDocName = "Rpt Position Summary by Unit Class" DoCmd.OpenReport stDocName, acPreview DoCmd.SelectObject acForm, "Select Unit Classification" DoCmd.Close Exit_Command9_Click: Exit Sub Err_Command9_Click: MsgBox Err.Description Resume Exit_Command9_Click End Sub "John Spencer" wrote: == Open the report in design view == Click in the little square at the top left of the window (where the rulers intersect) == If the properties pane is not showing, right-click on the square and select properties. == Click on the EVENT TAB == Click in the ON Open == Select the Event Procedure from the drop down == Click on the ... button The procedure would be something like Private Sub Report_Open(Cancel As Integer) 'Open a form and stop all processing outside the form 'until the form is closed or made invisible Docmd.OpenForm "MyParameterForm",,,,,acdialog End Sub That should open the form. Now you need to code the button on the form to set the form's visible property to False instead of closing the form. Me.Visible = False At this point the form is still available and the recordsource for your report can get information from the form. Why do I advise against it? It just seems to be more complex then opening the form, getting the parameters, and executing the report from a button on the form. It does have the advantage of being able to use the form for input on multiple reports without having to have the user specify which report to open from the parameter form. It does not allow the user to cancel the report without more code. And that introduces the need to trap for errors when you cancel the report based on the trapping whether or not the form is open. If you close the form instead of hiding the visibility, you can then add code to the open event of the report to check if the form is open or not. If the form is closed, then you cancel the report (Cancel = True) Also, you now need to add code to the Close event to close the parameter form. John Spencer Access MVP 2002-2005, 2007-2009 The Hilltop Institute University of Maryland Baltimore County Arlene wrote: How can I do that? I tried to create the open event but I guess I am not doing it correctly. Why do you advise against it? "John Spencer" wrote: It can be done, but I would advise against it. In the REPORTS open event, you would need to open the form in dialog mode (that halts all processing except in the opened form), set the values, and use the button to set the form's visible property to false. At this point the report can grab the values from the form. Then in the Report's close event, use code to close the form. John Spencer Access MVP 2002-2005, 2007-2009 The Hilltop Institute University of Maryland Baltimore County Arlene wrote: Hi, I have a report and form that are "connected" to each other, meaning that I select the cirteria on the form (combo box) and the reports opens based on that criteria. What I want is to do somewhat of the opposite. I want that when I click on the report, the form opens for the user to select the criteria and then the reports will open based on that. I hope I am making sense! |
#17
|
|||
|
|||
Filter report on a form
In another thread, you posted that the source for the report was
This is it: SELECT [UnitClassification], [UnitClassification] FROM tblUnitClassification ORDER BY [UnitClassification]; Notice that there is no where clause in that. You would need something like This is it: SELECT [UnitClassification], [UnitClassification] FROM tblUnitClassification WHERE [NameOfSomeField] = Forms![Name Of the Form]![Select Unit Classification] ORDER BY [UnitClassification] Or you might be able to create a filter, by using code immediately after you call the form. The following is speculation on my part and I can't test the syntax of this right now. Me.Filter = "[Name of Some Field] =""" & Forms![Name Of the Form]![Select Unit Classification] & """" Me.FilterOn = True John Spencer Access MVP 2002-2005, 2007-2009 The Hilltop Institute University of Maryland Baltimore County Arlene wrote: I did used your suggestions for the report to open the form and that worked wonderfully , but I still can't get the form to pull the criteria according to what is selected. It always gives me the same info. I changed the close form for the visible=false and nothing. "John Spencer" wrote: You can't close the form. If you do it won't be available for the report to get values from. set the form's visible property to false. Forms("Select Unit Classification").Visible = False Did you try my suggested version for opening the report and then having the report open the form. If so, did it fail and how did it fail? John Spencer Access MVP 2002-2005, 2007-2009 The Hilltop Institute University of Maryland Baltimore County Arlene wrote: I have that on the button on the form to open the report based on the criteria I selected from the combo box to open the report and close the form once the report is open. "John Spencer" wrote: What are you attempting to do with these lines? DoCmd.SelectObject acForm, "Select Unit Classification" DoCmd.Close If the form you want to use is Select Unit Classification then you cannot do it from this button after you have already opened the report. TOO LATE. Also using DoCmd.Close without specifying the object type and specific object name is asking for trouble. DoCmd.Close will close whatever object currently has the focus. That code be the form you are on, the report you just opened, or the form you just opened. That code does not look like what I suggested. Are you trying to open a form to get parameters and then open the report? John Spencer Access MVP 2002-2005, 2007-2009 The Hilltop Institute University of Maryland Baltimore County Arlene wrote: Thank you John, with this the reports opens the form, but when I select the criteria from the form another window pops up asking for the parameter value. I am guessing I need to change something on the form. This is what I have on the MSV for the button on my form: Private Sub Command9_Click() On Error GoTo Err_Command9_Click Dim stDocName As String stDocName = "Rpt Position Summary by Unit Class" DoCmd.OpenReport stDocName, acPreview DoCmd.SelectObject acForm, "Select Unit Classification" DoCmd.Close Exit_Command9_Click: Exit Sub Err_Command9_Click: MsgBox Err.Description Resume Exit_Command9_Click End Sub "John Spencer" wrote: == Open the report in design view == Click in the little square at the top left of the window (where the rulers intersect) == If the properties pane is not showing, right-click on the square and select properties. == Click on the EVENT TAB == Click in the ON Open == Select the Event Procedure from the drop down == Click on the ... button The procedure would be something like Private Sub Report_Open(Cancel As Integer) 'Open a form and stop all processing outside the form 'until the form is closed or made invisible Docmd.OpenForm "MyParameterForm",,,,,acdialog End Sub That should open the form. Now you need to code the button on the form to set the form's visible property to False instead of closing the form. Me.Visible = False At this point the form is still available and the recordsource for your report can get information from the form. Why do I advise against it? It just seems to be more complex then opening the form, getting the parameters, and executing the report from a button on the form. It does have the advantage of being able to use the form for input on multiple reports without having to have the user specify which report to open from the parameter form. It does not allow the user to cancel the report without more code. And that introduces the need to trap for errors when you cancel the report based on the trapping whether or not the form is open. If you close the form instead of hiding the visibility, you can then add code to the open event of the report to check if the form is open or not. If the form is closed, then you cancel the report (Cancel = True) Also, you now need to add code to the Close event to close the parameter form. John Spencer Access MVP 2002-2005, 2007-2009 The Hilltop Institute University of Maryland Baltimore County Arlene wrote: How can I do that? I tried to create the open event but I guess I am not doing it correctly. Why do you advise against it? "John Spencer" wrote: It can be done, but I would advise against it. In the REPORTS open event, you would need to open the form in dialog mode (that halts all processing except in the opened form), set the values, and use the button to set the form's visible property to false. At this point the report can grab the values from the form. Then in the Report's close event, use code to close the form. John Spencer Access MVP 2002-2005, 2007-2009 The Hilltop Institute University of Maryland Baltimore County Arlene wrote: Hi, I have a report and form that are "connected" to each other, meaning that I select the cirteria on the form (combo box) and the reports opens based on that criteria. What I want is to do somewhat of the opposite. I want that when I click on the report, the form opens for the user to select the criteria and then the reports will open based on that. I hope I am making sense! |
|
Thread Tools | |
Display Modes | |
|
|