Letting the user choose criteria

[msreed]

Amateuar needs to know, Is this thes best way to do this?

I have created a report where I want the user to determine the criteria (without using parameters in a query).

I have a form that lists reports, I will add this one here, but I wanted to add a pop-form that lets the user select criteria. They would be selecting up to four codes (text boxes) and then choosing states (1 or more) from a list box.

Based on criteria chosen the report would run. Can I pass the info over?

Right now I am running the reports for them b/c the criteria always changes. Any advise welcome, thanks.

 

[GingerR]

i use one form, where the user selects the report, all their criteria (depending on which report is chosen).
on it you'd put your report list, perhaps in a combo box.
in the combo box's AfterUpdate property, you can put code that will make visible only the controls (text boxes, etc) visible that you want - the ones that pertain to that report.
then you have a button on this form, and put into the OnClick event a bunch of validation code plus some code to concoct a WHERE string, prior to running the OpenReport code.

example of combo box's AfterUpdate property:

Select Case me!cboReportSelection
Case "Report1"
me!txtDate.visible = true
me!txtCustomer.visible = false
Case "Report2"
me!txtDate.visible = false
me!txtCustomer.visible = true
end select

then in your button's OnClick event, check to make sure the user entered the info they're supposed to, that they havent left stuff blank:

if me!cboReportSelection = "Report1" then
if isnull(me!txtDate) or me!txtDate = "" then
msgbox "Please enter a date!",vbokonly,"ERROR"
me!txtDate.setfocus
exit sub
end if
end if

if me!cboReportSelection = "Report2" then
if isnull(me!txtCustomer) or me!txtCustomer = "" then
msgbox "Please enter a Customer!",vbokonly,"ERROR"
me!txtCustomer.setfocus
exit sub
end if
end if

dim strWHERE
select case me!cboReportSelection
Case "Report1"
strWhere = "Date = #" & me.txtDate & "#"
Case "Report2"
strWhere = "Customer = '" & me.txtCustomer & "'"
end select

docmd.openreport me!cboReportSelection,acPreview,,strWhere

hope this helps. it should give you an idea of how to go about it.
g