Opening form based on Variable

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Currently I open a form based on a parameter query, is it possible to change
this to a combo box stating the values possible. There are a lot of values
possible when opening the form and the users dont always know all possible
values.
If yes, or other alteratives how?
Thanks
 
One approach is to remove the parameter from the query, and place an unbound
combo on the form. In the AfterUpdate of the combo, filter the form to
matches.

To open the form so it has no matches until they choose something in the
combo:
DoCmd.OpenForm "Form1", WhereCondition:="(False)"

Then in the AfterUpdate event of the combo, set the Filter of the form based
on the value in the combo.
 
Thanks Allen
The first part makes sence, but I am not sure about the filter for the
AfterUpdate,
Do I open another form or the same form based on the Condition?
 
The specifics here depend on your field name and the data type of that
field.

This example shows how to filter a Text type field named City, based on the
user's choice in an unbound combo named cboFilterCity:

Private Sub cboFilterCity_AfterUpdate()
Dim strWhere As String
If Not IsNull(Me.cboFilterCity) Then
strWhere = "[City] = """ & Me.cboFilterCity & """"
'Debug.Print strWhere
Me.Filter = strWhere
Me.FilterOn = True
End If
End Sub

If the field is a Number type, drop the extra quotes:
strWhere = "[City] = " & Me.cboFilterCity

If the string is not working as expected, remove the apostrophy from the
beginning of the Debug.Print line. Then when it fails, open the Immediate
Window (Ctrl+G), and see what came out. It needs to look just like the WHERE
clause in a query.

It is possible to build a more involved search form, with lots of unbound
search boxes at the top, and build the filter from just those boxes where
the user entered some criteria. If you are interested in that, you can
download a little sample here:
http://allenbrowne.com/unlinked/Search2000.zip
 
Back
Top