Limiting Dropdown based on existing records?

[M S]

I have a dropdown box, txtCall, that simply selects [CallNumber] from
tblCalls based on a previous field entered, txtCycle . Each cycle has a
certain number of calls. The values are 1 through 10.

However, I want this box to limit the items to restrict when a previous
value was already saved in the database. For instance, if a record was
already saved to the database for Cycle #1, Call #1 and #2, then I only want
this dropdown to allow someone to select 3 through 10, when Cycle #1 is
selected.

Help?

 

[Wayne Morgan]

If the 1 through 10 are in a table, you could use an Unmatched Query to fill
the Row Source of the combo box. This would first require a query that will
get the unique call values from the table where the cycle number matches the
selection in the first combo box. In this case, this query would return 1
and 2. You would then use this query and the table holding the 1-10 in an
unmatched query. Since 3-10 don't exist, these are the values that would be
returned.

Example:
First query:
SELECT DISTINCT CallNumber FROM Table1 WHERE CycleNumber =
Forms!Form1!Combo1

Second query (Row Source of 2nd combo box):
SELECT CallNumber FROM tblCallNumbers LEFT JOIN FirstQuery ON
tblCallNumbers.CallNumber = FirstQuery.CallNumber
WHERE FirstQuery.CallNumber Is Null;

In the AfterUpdate event of the first combo box, you would need to Requery
the second combo box.

Example:
Me.Combo2.Requery

Since you're only allowing one of each call for each cycle, the DISTINCT
should be unneeded (i.e. there should already only be one of each anyway),
but also shouldn't hurt anything.

 

[M S]

Thanks for your help, this worked great!

If the 1 through 10 are in a table, you could use an Unmatched Query to fill
the Row Source of the combo box. This would first require a query that will
get the unique call values from the table where the cycle number matches the
selection in the first combo box. In this case, this query would return 1
and 2. You would then use this query and the table holding the 1-10 in an
unmatched query. Since 3-10 don't exist, these are the values that would be
returned.

Example:
First query:
SELECT DISTINCT CallNumber FROM Table1 WHERE CycleNumber =
Forms!Form1!Combo1

Second query (Row Source of 2nd combo box):
SELECT CallNumber FROM tblCallNumbers LEFT JOIN FirstQuery ON
tblCallNumbers.CallNumber = FirstQuery.CallNumber
WHERE FirstQuery.CallNumber Is Null;

In the AfterUpdate event of the first combo box, you would need to Requery
the second combo box.

Example:
Me.Combo2.Requery

Since you're only allowing one of each call for each cycle, the DISTINCT
should be unneeded (i.e. there should already only be one of each anyway),
but also shouldn't hurt anything.

--
Wayne Morgan
MS Access MVP

I have a dropdown box, txtCall, that simply selects [CallNumber] from
tblCalls based on a previous field entered, txtCycle . Each cycle has a
certain number of calls. The values are 1 through 10.

However, I want this box to limit the items to restrict when a previous
value was already saved in the database. For instance, if a record was
already saved to the database for Cycle #1, Call #1 and #2, then I only
want
this dropdown to allow someone to select 3 through 10, when Cycle #1 is
selected.

Help?