I need to select a specific lookup list

[Clarkey]

Hi,

I am creating a simple(ish) database using Access 2007. My main stumbling
block is that I need to display a specific lookup table is an option (yes) is
selected and another lookup table if no is selected.

For example, the option will be Discount applied YES or NO
if YES I need a lookup list (called discount_yes, created in a separate
table) to display on the form so the option can be selected. If NO is
selected I need the discount_no lookup list (again created as a separate
table) to display so the options from that list can be selected.

Is there any easy way to do this???

 

[Ken Sheridan]

You don't need separate 'look-up' tables for discounts and non-discounts.
Moreover its bad design to do so as data is being represented by table names,
not by values in rows in a table as should be the case. Just have a single
Discounts 'look-up' table and give this a Boolean (Yes/No) Discount column.
For those rows where a discount applies set this column to True (Yes), for
the others leave it False (No)

For the RowSource of the combo box bound to the field in the referencing
table on your data entry form reference the other control on the form bound
to the field whose value determines which subset of rows from the Discounts
table you want to list. So, if the field from the Discounts table you want
listed is called DiscountLevel say, and the field which determines which
subset to use is called DiscountOption, and this is also a Boolean (Yes/No)
data type, then the combo box's RowSource property would be:

SELECT DiscountLevel FROM Discounts WHERE Discount = Form!DiscountOption
ORDER BY DiscountLevel;

Note that you can refer to the current form by means of the Form property;
you don't need to fully reference it.

To change the list shown in the combo box, which I'll assume is called
cboDiscountLevel, the you need to requery the combo box control in two
places, (1) in the form's Current event procedure, (2) in the DiscountOption
control's AfterUpdate event procedure, with:

Me. cboDiscountLevel.Requery

Ken Sheridan
Stafford, England

 

[Klatuu]

You don't give enough information to get an answer. How are you using the
tables for lookups? BTW, you don't really need two tables. You should have
all the entries in one list with a field designating whether Discount Applied
is Yes or No. Then just filter the list based on the user's selection.