Access 2007 - Table A lookup values based on Table B

[Stuart]

Hi

I hope someone can help with this.

Table A has my data which stores the data that is input by my users on
their form. They select a company code "ABC" from the drop down list.

I then have another field that I would like to be based on Table B
which has an analysis code but the list should be shortened to show on
those for the company code "ABC" or whatever company code is selected.

Also, would it be possible to for the lookup list to show both the
analysis code and the name of the analysis code in the drop down
list? The analysis code and the analysis name are in two seperate
fields of Table B. It would just be more helpful to see both when
selecting from the drop down list.

Any help will be much appreciated.

Thanks alot

 

[Ross La Haye]

Hi

I hope someone can help with this.

Table A has my data which stores the data that is input by my users on
their form. They select a company code "ABC" from the drop down list.

I then have another field that I would like to be based on Table B
which has an analysis code but the list should be shortened to show on
those for the company code "ABC" or whatever company code is selected.

Also, would it be possible to for the lookup list to show both the
analysis code and the name of the analysis code in the drop down
list? The analysis code and the analysis name are in two seperate
fields of Table B. It would just be more helpful to see both when
selecting from the drop down list.

Any help will be much appreciated.

Thanks alot

If I understand you correctly, this is possible.

Let's say the combo box for Table A is called Combo0.
Let's say the combo box for Table B is called Combo1.
Let's say both of these are on Form1.

Put an event procedure in Combo0. This is just 1 line of code: Me.Refresh.

The Source Data for Combo1 should be a query that joins Table A and Table B.. There should be three columns in the query, in this order, analysis code, analysis name (from Table B), and company code (from Table A). Company code may be unchecked in the QBE grid. The condition for company code should be =[Forms].[Form1].[Combo0].

Make sure that the bound column on the Data tab is 1.

Also, set the column count to 2 for Combo1 and adjust the column widths accordingly. You can also display column heads, if it helps... This is on the format tab of the properties for the combo box.

That should do it.