Limit the data selection based on two fields?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Cam - 08 Mar 2008 20:25 GMT

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.

Reply to this Message

John W. Vinson - 08 Mar 2008 20:35 GMT

>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.

Signature

John W. Vinson [MVP]

Reply to this Message

Cam - 09 Mar 2008 00:18 GMT

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

> >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,
[quoted text clipped - 6 lines]
> chosen or if it varies based on item and side, but the combo can handle it
> either way.

Reply to this Message

John W. Vinson - 09 Mar 2008 06:39 GMT

>John,
>
[quoted text clipped - 12 lines]
>* 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?

Signature

John W. Vinson [MVP]

Reply to this Message

Richard Rost - 09 Mar 2008 08:31 GMT

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

> 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.

Reply to this Message