Using Forms to select criteria for Report 1

[smgeorge99]

I have a report that allows the user to select certain criteria for the report. In the report's Open event, I open the criteria form in Dialog mode, so that the user has to make the selection. Here is the code:

Private Sub Report_Open(Cancel As Integer)
'Open the form for date input
DoCmd.OpenForm "InputDate", acNormal, , , acFormEdit, acDialog
End Sub

When the user makes their selection, they hit the OK button, which hides the form. The report then uses the values in the hidden form. In the report's close event, I close the hidden form.

In addition to the OK button in the form, there is a Cancel button in the form. When the user hits cancel, the form closes, but the report still tries to open and fails because the fields it's looking for in the form are not available.

My question is this: How do I implement the Cancel button so that if the user hits the cancel button in the form the reports closes gracefully?

 

[RoyVidar]

Well - You need to set a variable or whatever to some value, and after you hide the form, but before you close it check that value.

You could use an invisible check box in your form, when you press OK set it to true, when Cancel set it to false.

immidieately after you hide the form (in your OnOpen event) check this value.

If Forms("frmMyHiddenForm&quot("chkMyChk&quot then
'do whatever criteria you need
Else
cancel=true
endif
close the form and voila;-)

HTH Roy-Vidar

 

[smgeorge99]

Thanks, that worked great. Have a star on me!

 

[kentwoodjean]

I missed this particular post before I posted mine a few minutes ago. I was asking how to do exactly what you have done with criteria selection on a form for a report. I did exactly what you have indicated and it actually worked......somewhat. When I click on the report, my form opens. The form contains a combo box listing employees. When I select an employee, I click the button "preview report" and when my report comes up, it has not read my criteria selection and instead contains all employees. What am I missing? Do I have to add anything Else in the report field that will contain the name?

 

[Eupher]

kentwoodjean, does your DoCmd.OpenReport line specify a WHERE condition? If not, it will simply open the report displaying all records according to the reports Record Source property. You must add a where condition if you want to filter the report, i.e.:

DoCmd.OpenReport "NameOfYourReport", acPreview, , "[Employee] = '" & Me!cmbEmployee & "'"

where "NameOfYourReport" is the name of your report, [Employee] is the name of your Employee field in your data source, and cmbEmployee is the name of the combo box on your form.

Ken S.