Requery Combo Box

[Chuck]

Not sure if I am doing this correctly or not. I have a form that has
several combo boxes on it that allow a user to pick a name from a list. I
need to have the next combo box be requeried and not include the selection
from the first box. This has to continue until four boxes have been
selected.

The table, tblDesignators, have the data that is in the combo box. For an
example, it contains the names of people.

The user selects a name from the first combo box that has a query behind it
to select all the names. After the selection, the user moves onto the next
box and selects a new name. I don't want the name from the first selection
to appear in the box. I almost had it by using a query that had something
like this in the criteria of the query:

<>Forms!frmApprovalList!ComboBox1.

This did work for the second combo box.

The third combo box I tried the criteria as

<>Forms!frmApprovalList!ComboBox1 AND <>Forms!frmApprovalList!ComboBox2.

The selection ended up with no records. I tried an OR as well and still no
results.

If someone decides to go back and change names, the first box has to be
requeried and start again.

Make sense?

 

[Ken Sheridan]

You'll need to repeat the column name in the criterion for the second combo
box:

<>Forms!frmApprovalList!ComboBox1 AND [Designator]
<>Forms!frmApprovalList!ComboBox2

You can do it in SQL view or all in one column as a single entry in the
criteria row in query design view, or you can add the Designator column to
the design grid twice, unchecking the 'show' check box for the second
instance. On the same criteria row for each column add the restrictions
separately in each, i.e. <>Forms!frmApprovalList!ComboBox1 for one and
<>Forms!frmApprovalList!ComboBox2 for the other. Access will then insert the
AND operator in the SQL statement for you.

Ken Sheridan
Stafford, England

 

[Chuck]

Thanks Ken, I tried that and the results were an empty box.
The SQL was generated like you said, but produced no results.

You'll need to repeat the column name in the criterion for the second
combo
box:

<>Forms!frmApprovalList!ComboBox1 AND [Designator]
<>Forms!frmApprovalList!ComboBox2

You can do it in SQL view or all in one column as a single entry in the
criteria row in query design view, or you can add the Designator column to
the design grid twice, unchecking the 'show' check box for the second
instance. On the same criteria row for each column add the restrictions
separately in each, i.e. <>Forms!frmApprovalList!ComboBox1 for one and
<>Forms!frmApprovalList!ComboBox2 for the other. Access will then insert
the
AND operator in the SQL statement for you.

Ken Sheridan
Stafford, England

Not sure if I am doing this correctly or not. I have a form that has
several combo boxes on it that allow a user to pick a name from a list.
I
need to have the next combo box be requeried and not include the
selection
from the first box. This has to continue until four boxes have been
selected.

The table, tblDesignators, have the data that is in the combo box. For
an
example, it contains the names of people.

The user selects a name from the first combo box that has a query behind
it
to select all the names. After the selection, the user moves onto the
next
box and selects a new name. I don't want the name from the first
selection
to appear in the box. I almost had it by using a query that had
something
like this in the criteria of the query:

<>Forms!frmApprovalList!ComboBox1.

This did work for the second combo box.

The third combo box I tried the criteria as

<>Forms!frmApprovalList!ComboBox1 AND <>Forms!frmApprovalList!ComboBox2.

The selection ended up with no records. I tried an OR as well and still
no
results.

If someone decides to go back and change names, the first box has to be
requeried and start again.

Make sense?

 

[Ken Sheridan]

The lists for the second and third combo boxes will be empty until, in the
case of the second, a selection has been made from the first and the second
requeried, and in the case of the third, a selection has been made from the
second and the third requeried. This is because the controls are Null until
a selection I made and any comparison with Null results in Null, so the WHERE
clauses of the two RowSource queries will not evaluate to True for any row.
Once a selection has been made and the next combo box down the line requeried
then its list should be populated.

Ken Sheridan
Stafford, England

Thanks Ken, I tried that and the results were an empty box.
The SQL was generated like you said, but produced no results.

You'll need to repeat the column name in the criterion for the second
combo
box:

<>Forms!frmApprovalList!ComboBox1 AND [Designator]
<>Forms!frmApprovalList!ComboBox2

You can do it in SQL view or all in one column as a single entry in the
criteria row in query design view, or you can add the Designator column to
the design grid twice, unchecking the 'show' check box for the second
instance. On the same criteria row for each column add the restrictions
separately in each, i.e. <>Forms!frmApprovalList!ComboBox1 for one and
<>Forms!frmApprovalList!ComboBox2 for the other. Access will then insert
the
AND operator in the SQL statement for you.

Ken Sheridan
Stafford, England

Not sure if I am doing this correctly or not. I have a form that has
several combo boxes on it that allow a user to pick a name from a list.
I
need to have the next combo box be requeried and not include the
selection
from the first box. This has to continue until four boxes have been
selected.

The table, tblDesignators, have the data that is in the combo box. For
an
example, it contains the names of people.

The user selects a name from the first combo box that has a query behind
it
to select all the names. After the selection, the user moves onto the
next
box and selects a new name. I don't want the name from the first
selection
to appear in the box. I almost had it by using a query that had
something
like this in the criteria of the query:

<>Forms!frmApprovalList!ComboBox1.

This did work for the second combo box.

The third combo box I tried the criteria as

<>Forms!frmApprovalList!ComboBox1 AND <>Forms!frmApprovalList!ComboBox2.

The selection ended up with no records. I tried an OR as well and still
no
results.

If someone decides to go back and change names, the first box has to be
requeried and start again.

Make sense?