Qry based on form w cbo boxes

[Charles L. Snyder]

Hi -
I have a form with several combo boxes. These provide the parameters for a
qry. I would like to:
1. Be able to select a value from some of the combo boxes, but make no
selection from other combo boxes (ie, include all of the the fields from
these parameters)
2. How do I limit the combo box choices to unique values from the table it
is based on (so that, for example, part number 123451 is on listed once in
the combo box drop down list, not 50 or 60 times)
TIA

 

[John Vinson]

Hi -
I have a form with several combo boxes. These provide the parameters for a
qry. I would like to:
1. Be able to select a value from some of the combo boxes, but make no
selection from other combo boxes (ie, include all of the the fields from
these parameters)

If it's really a number of combo boxes (more than three, say) then
your best bet is to write VBA code to construct a SQL string; step
through the form, control by control, adding a new AND clause to a SQL
string as you go. Air code:

Dim strSQL As String
strSQL = "SELECT [this], [that], [theother] FROM mytable WHERE TRUE"
If Not IsNull(Me!cboLastName) Then
strSQL = strSQL & " AND [LastName] = " & Chr(34) & Me!cboLastName _
& Chr(34)
End If

with a similar IF for each combo box. There are more elegant ways but
this is pretty simple to implement. The TRUE in the seed string lets
you start each combo clause with the word AND (but will retrieve all
records in the table if you don't enter anything in any combo).

2. How do I limit the combo box choices to unique values from the table it
is based on (so that, for example, part number 123451 is on listed once in
the combo box drop down list, not 50 or 60 times)

Base the Combo on a Query which retrieves only one of each value;
surely you have (well, you *should* have anyway!) a Parts table for
which PartNumber is the unique key? If not, set the Unique Values
property of the query to True.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps