Conditional Lookup

[Brad]

I have 2 tables in my database. A main table and an issues table. TblIssue
has 5 columns. Autonum Issue Model Row Seat. TblMain has columns that
match(along with some others not needed). I would like the Issue column to
have a drop down menu depending on what was selected in Model, Row and Seat
column. Fairly new to Access so any help would be appreciated.

Note: Model, Row and Seat are all combo boxes with 3 options in each.

 

[John W. Vinson]

I have 2 tables in my database. A main table and an issues table. TblIssue
has 5 columns. Autonum Issue Model Row Seat. TblMain has columns that
match(along with some others not needed). I would like the Issue column to
have a drop down menu depending on what was selected in Model, Row and Seat
column. Fairly new to Access so any help would be appreciated.

Note: Model, Row and Seat are all combo boxes with 3 options in each.

As far as I know you cannot do so in a Lookup Field in a Table - it's quite
straightforward though to do so using a Combo Box ("lookup") on a Form. This
is just one of many reasons why you should use Forms for data interaction
(they're *designed* for that purpose) rather than using Tables (which are
designed to store data).

On a Form you could base the combo box on a query using criteria such as
=[Forms]![YourFormName]![Model] on the model field, etc.

Do note that if you have a Model field in the main table and also a Model
field in the Issues table... you probably shouldn't, since that would imply
that the data is stored redundantly. How (if at all) are the tables related?

 

[Brad]

I have 2 tables in my database. A main table and an issues table. TblIssue
has 5 columns. Autonum Issue Model Row Seat. TblMain has columns that
match(along with some others not needed). I would like the Issue column to
have a drop down menu depending on what was selected in Model, Row and Seat
column. Fairly new to Access so any help would be appreciated.

Note: Model, Row and Seat are all combo boxes with 3 options in each.

As far as I know you cannot do so in a Lookup Field in a Table - it's quite
straightforward though to do so using a Combo Box ("lookup") on a Form. This
is just one of many reasons why you should use Forms for data interaction
(they're *designed* for that purpose) rather than using Tables (which are
designed to store data).

On a Form you could base the combo box on a query using criteria such as
=[Forms]![YourFormName]![Model] on the model field, etc.

Do note that if you have a Model field in the main table and also a Model
field in the Issues table... you probably shouldn't, since that would imply
that the data is stored redundantly. How (if at all) are the tables related?

Ideally, I would have a user input daily issues in a form such as

Model Row Seat Issue Qty
W 1 R Cut 1
X 2 L Stiff 3

After the user selects a model, row and seat the Issue combo box should
allow the user to select the type of defect. The list of possible defect
codes would be different depending on the combination of model, row and seat
selected. So any suggestions on a different way to set up the table would be
appreciated.

 

[John W. Vinson]

Ideally, I would have a user input daily issues in a form such as

Model Row Seat Issue Qty
W 1 R Cut 1
X 2 L Stiff 3

After the user selects a model, row and seat the Issue combo box should
allow the user to select the type of defect. The list of possible defect
codes would be different depending on the combination of model, row and seat
selected. So any suggestions on a different way to set up the table would be
appreciated.

The table appears to be ok - my concern was that you were storing (say) row
and seat data redundantly in two tables. If you're not, or if the two tables
are joined on the tuple (Model, Row, Seat) then you're fine.