Limiting Dropdown based on selection?

[M S]

I have two dropdown boxes, txtCall and txtCycle. In the database, each
Cycle has up to 10 calls. When a user selects txtCycle, I have programming
to limit the choices in txtCall to the desired calls. For this example,
let's say Cycle #1 has 5 calls.

Now, I want to limit the items in txtCall 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 txtCall
to allow for someone to select 3, 4, or 5.

Help?

 

[Allen Browne]

See:
Limit content of combo/list boxes
at:
http://www.mvps.org/access/forms/frm0028.htm

 

[M S]

Thanks for the help.

I actually saw that article before and it was helpful. However, I want to
combine that along with liminting the combo box based on records saved in
the database. For instance, if a record is saved in the database for call 1
and call 2, I don't want those to appear in the dropdown.... see?

Thanks!
Mike

 

[Allen Browne]

Then you will need to change the RowSource of the combo to one that contains
a subquery to see if the related record exists on any *other* row of the
table that the subform is bound to.

This example assumes the combo is CategoryID, drawing records from a table
named tblCategory, and used in a form bound to MyTable with a numeric
primary key name MyID:

Me.CategoryID.RowSource = "SELECT CategoryID FROM tblCategory WHERE NOT
EXIST (SELECT MyID FROM MyTable WHERE MyID <> " & Nz(Me.MyID, 0) & ");"

If subqueries are new, see Microsoft's introductory article:
How to Create and Use Subqueries
at:
http://support.microsoft.com/?id=209066