Custom Form to Capture Parameter Query Criteria

[BJM]

I need to create a custom form that will allow non-
technical users to supply multiple parameters in a
comfortable interface that will then be passed to an
underlying query and then used to open a report.

I understand how to use a query to populate a combo box
with a table field list (of course codes, for example) to
prevent typos. The trouble is in a situation where there
may be more than one record that meets the criteria (all
records for weeks 3 - 5 for example). I have tried using a
list box that allows multiple selections, but then when
the values are passed to the query underlying the
destination report, the fields with more than one value
return an error.

And what if the user wants to leave one parameter blank in
order to return all records? That causes the open report
action to fail as well.

I'm sure there must be syntax to allow for this, but I'm
afraid my learning curve hasn't reached that height! Could
anyone please help?

Thanks,
BJM

 

[PC Datasheet]

Build a modal popup form that contains unbound controls for you to enter
criteria. You then need a button on the form with the following code in the
Click event:
Me.Visible = False
By not closing the form, the query is able to get at the criteria. In the
Close event of the report, put the following code:
DoCmd.Close acForm, "MyCriteriaForm"

For each field in the query where you are setting criteria on the form, put
the following expression for each criteria:
Forms!MyCriteriaForm!NameOfCriteriaControl Or
(Forms!MyCriteriaForm!NameOfCriteriaControl Is Null)
This expression aloows you to leave any criteria control blank.

You can open the criteria form in the OnOpenevent of the report:
DoCmd.OpenForm "MyCriteriaForm"
If Not IsLoaded("MyCriteriaForm") Then
Cancel = True
End If

You can find the code for the IsLoaded function in Northwind's standard
modules.

Be sure in your code to open the report you put the line:
On Error Resume Next

 

[PC Datasheet]

FredG posted the IsLoaded function in another post --

Function IsLoaded(ByVal strFormName As String) As Integer
' Returns True if the specified form is open in Form view or
Datasheet
view.

Const conObjStateClosed = 0
Const conDesignView = 0

If SysCmd(acSysCmdGetObjectState, acForm, strFormName) <>
conObjStateClosed Then
If Forms(strFormName).CurrentView <> conDesignView Then
IsLoaded = True
End If
End If
End Function