Criteria and no criteria

[pulpfriction]

Hope someone can help.

I have a form feed by a query with 100+ records. The query has two field criterias which is requeried from two combo boxes on the form. I would like by default for the form to show all records and only filter the records after a selction in the combo boxes is made.

EG. If the combo box values (both boxes) are NULL, then all records will be displayed.

Any thoughts appreciated. Thanks in advance.

Regards
W

 

[DALeffler]

Have the query the form is bound to pass the two field values to a custom function and check the return value against criteria in the query.

Your form query would have a WHERE statement that looks something like:

WHERE (((fncMyFunction([tblMyTable]![MyField1],[tblMyTable]![MyField2]))=True))

And the function would look like:

Function fncMyFunction(lngMyField1 as Long, lngMyField2 as Long) As Boolean
'returns true if FormName.ControlName is null,
'returns true if FormName.ControlName = lngMyFieldn,
'else returns false

On Error GoTo Err_fncMyFunction 'allow form to open...

fncMyFunction = True

If Not IsNull([MyForm]![cmbCombo1]) Then
     If [MyForm]![cmbCombo1] <> lngMyField1 Then
          fncMyFunction = False
          Exit Function
     End If
End If
    
If Not IsNull([MyForm]![cmbCombo2]) Then
     If [MyForm]![cmbCombo2] <> lngMyField2 Then
          fncMyFunction = False
     End If
End If
      
Exit Function

Err_fncMyFunction:          
End Function

Then use the After Update events for the unbound combo boxes to requery the form.

hth,

 

[elsandalos]

Hi,
i'm trying something similar - using the contents of a combo box as a critera in the query which feeds the form.

I'm trying to implement an 'all' option in the combo box, which will be used put a like "*" in the criteria, thus displaying all records. The code i'm using in the criteria area of the query doesn't work properly:

IIf([forms]![master].[form].[cboFilterPrincipal]="all",
"like "*"",
[forms]![master].[form].[cboFilterPrincipal])

if i pick option "all", i'm getting an error when the query is run. The rest of it works fine, so something's wrong with my use of the wildcard in the TRUE part. If i use something else there, it all works fine, so it's not that the query doesn't like an IIf statement in its criteria:

IIf([forms]![master].[form].[cboFilterPrincipal]="all",
"Fred",
[forms]![master].[form].[cboFilterPrincipal])

... so the above works fine, using "Fred" as the criteria if i pick "all" in the combo box.

Must be something simple ....

El.

 

[Mile-O]

The criteria you need is:

[Forms]![YourForm]![YourCombo] Or [Forms]![YourForm]![YourCombo] Is Null

 

[elsandalos]

weird - that seems too simple to work, but it does.

THANKS!

El.