Limit the data selection based on two fields?

[Cam]

Hello,

I have a data entry form where user input the data. Within the data fields,
I have field call Item, Side, sequence field where any combination of these 3
fields are unique. What I would like to achieve on the sequence field is
limit the selected based on selection of Item and Side. This is to prevent
user from inputting wrong values on that field. Any help is appreciated.

Example fields:
Item Side Sequence
11A1300-1 1st side 50
11A1300-1 1st side 100
11A1300-1 1st side 200
11A1300-1 1st side 400
11A1300-1 1st side 1000
11A1300-1 1st side 1200
11A1300-1 2st side 100
11A1300-1 2st side 200
11A1300-1 2st side 400
11A1300-1 2st side 500
11A1300-1 2st side 600
11A1300-1 2st side 1500
11A1300-1 2st side 1900
11A1700-1 1st side 50
11A1700-1 1st side 100
11A1700-1 1st side 200
11A1700-1 1st side 300
11A1700-1 2st side 400
11A1700-1 2st side 500
11A1700-1 2st side 1100
11A1700-1 2st side 1500
11A1700-1 2st side 2000

Result:
If user select Item = 11A1300-1, then Side = 2nd side, then only these
sequence will be shown on the selection list 100, 200, 400, 500, 600, 1500,
1900.

 

[John W. Vinson]

If user select Item = 11A1300-1, then Side = 2nd side, then only these
sequence will be shown on the selection list 100, 200, 400, 500, 600, 1500,
1900.

Use a Combo Box control on the form for Sequence, based on a query which
retrieves only the valid selections; set its Limit to List property to Yes.

It's not altogether clear from your post how that particular selection list is
chosen or if it varies based on item and side, but the combo can handle it
either way.

 

[Cam]

John,

It's not altogether clear from your post how that particular selection list is
chosen or if it varies based on item and side, but the combo can handle it
either way.

It varies based on the Item and Side selected, then limit the sequence list.
You can see the sample data below.
Example 1:
* If user select Item = 11A1300-1, then Side = 2nd side, then only these
sequence will be shown on the selection list 100, 200, 400, 500, 600, 1500,
1900.

Example 2:
* If user select Item = 11A1300-1, then Side = 1nd side, then only these
sequence will be shown on the selection list 50, 100, 200, 400, 1000, 1200

 

[John W. Vinson]

John,


It varies based on the Item and Side selected, then limit the sequence list.
You can see the sample data below.
Example 1:
* If user select Item = 11A1300-1, then Side = 2nd side, then only these
sequence will be shown on the selection list 100, 200, 400, 500, 600, 1500,
1900.

Example 2:
* If user select Item = 11A1300-1, then Side = 1nd side, then only these
sequence will be shown on the selection list 50, 100, 200, 400, 1000, 1200

In that case you can use three combo boxes - cboItem to select the item,
cboSide to select the side, cboSequence for the sequence. cboItem would be
based on the item table; cboSide could just be a list of values for the four
sides; and cboSequence would be based on a table listing all these valid
values, using the two other combos as criteria to determine which subset of
sequences are valid.

Am I right in understanding that these lists are exclusive, and that the user
would not have the option of adding (say) 2100 to the 2nd side of 11A1300-1?

 

[Richard Rost]

You can use multiple list boxes or combo boxes. I have a tutorial that
illustrates this:

http://www.599cd.com/tips/access/two-combo-boxes/

Hope this helps.

Richard Rost
www.599CD.com