query by form

[Matthew]

I am trying to build a form that has certain criteria on it (combo
boxes) which a user can pick from a range of values to specify the
criteria he wants when he runs a query on the form. I am trying out
different ways to do this, but nothing is working. Does anyone know
the easiest way to do this?

[Darryl Kerkeslager]

It would be pretentious to say that this is the *best* way, but here's a
simplified example:

Two comboboxes, with:
cboCity.rowsource = "SELECT city FROM lu_city_table"
cboState.rowsource = "SELECT state FROM lu_state_table"

When you click your query button:

lstResults.rowsource = "SELECT add_house, add_street, add_apt, add_city,
add_state, add_zip FROM address WHERE add_city = ' " & Me.cboCity & " ' AND
add_state = ' " & Me.cboState & " ' "

lstResults.requery
Darryl Kerkeslager
"Matthew" <ma************@eaton.com> wrote in message
news:7f**************************@posting.google.c om...

I am trying to build a form that has certain criteria on it (combo
boxes) which a user can pick from a range of values to specify the
criteria he wants when he runs a query on the form. I am trying out
different ways to do this, but nothing is working. Does anyone know
the easiest way to do this?

[Geoff]

I'm not sure if you are trying to write a Query By Form solution.

If you are, I have seen a good textbook solution. It used checkboxes
down the left side of the form and choices to the right of them.

For example, to the right of checkbox 1, you might have a combo box
where the user can select a single value. To the right of checkbox 2,
you might have two textboxes where the user can enter a From date and
a To date.

By default, the checkboxes down the left are enabled and the choices
down the right are disabled.

When the user selects a checkbox, its After_Update event handler
enables the choice to its right, so the user can now make a selection
or enter a value.

And vice versa, if the user then deselects the checkbox, the
After_Update event handler disables the choice to the right.

So the After_Update event handler would contain code like:
Me.cboChoice1.Enabled = (Me.chkChoice1=True)
or (same thing):
Me.cboChoice1.Enabled = Me.chkChoice1
or
Me.txtFromDate.Enabled = Me.chkChoice2
Me.txtToDate.Enabled = Me.chkChoice2

Presumably, you have a command button on the form to signal the user
has made all necessary choices. This button's Click event handler
should check which checkboxes are true and build an SQL statement
accordingly from the appropriate combo selections or textbox entries.
Then you can run or use the SQL statement.

There a numerous possibilities, but in principle that's how it could
be done.

As I said at the beginning, I realise your question could be
interpreted at meaning something quite different, but hope this was
your intended meaning.

Geoff

"Matthew" <ma************@eaton.com> wrote in message
news:7f**************************@posting.google.c om...

I am trying to build a form that has certain criteria on it (combo
boxes) which a user can pick from a range of values to specify the
criteria he wants when he runs a query on the form. I am trying out
different ways to do this, but nothing is working. Does anyone know
the easiest way to do this?

[Dave]

If there are several criteria and they are not always used, I like to
construct a Where string and then use that string in opening the query, form
or report for which the criteria apply. I usually use a user-defined
function like GeteWhereString() to return the string:

-----------------

GetWhereString() as String

Dim strWhere as String

strWhere = vbNullString

' FIRST NAME
If Not IsNull(Me!txtPersFirstName) Then
strWhere = strWhere & " AND [PersFirstName] Like " & Chr(34) &
Me![txtPersFirstName] & Chr(34)
End If

' LAST NAME
If Not IsNull(Me!txtPersLastName) Then
strWhere = strWhere & " AND [PersLastName] Like " & Chr(34) &
Me![txtPersLastName] & Chr(34)
End If

' STREET NAME
If Not IsNull(Me!txtPersStreetAddress) Then
strWhere = strWhere & " AND [PersStreetAddress] Like " & Chr(34)
& Me![txtPersStreetAddress] & Chr(34)
End If

' CITY
If Not IsNull(Me!txtPersCity) Then
strWhere = strWhere & " AND [PersCity] Like " & Chr(34) &
Me![txtPersCity] & Chr(34)
End If

If strWhere <> vbNullString then strWhere = Mid(strWhere,6) ' remove
initial " AND "

GetWhereString = strWhere

--------------

In the example above, if the user supplies criteria for any of the items
listed, they will be included in the Where string. If the user leaves some
out (no First Name, for example) the Where string simply ignores that field.
Using "Like" enables the user to use wild cards; the Chr(34)s are just text
delimiters.

This would be used in opening a form like this, assuming the form is
based on a table or query that includes the required fields.

DoCmd.OpenForm "MyForm", , , GetWhereString

If you want to use it with a query, you have to modify the querydef's
SQL property, so it's probably simpler to create a separate form in
datasheet view and open that.

"Matthew" <ma************@eaton.com> wrote in message
news:7f**************************@posting.google.c om...

I am trying to build a form that has certain criteria on it (combo
boxes) which a user can pick from a range of values to specify the
criteria he wants when he runs a query on the form. I am trying out
different ways to do this, but nothing is working. Does anyone know
the easiest way to do this?