Lookup of a lookup

[johndohnal]

Hi Everyone,
I'm constructing a database that will include a simple inventory management system. I have manufacturer and supplier tables with basic company information, and a packaging table that has units, quantites, etc. Next, I have a material specifications table that lists supplier qualification info with lookup fields to the other three tables using their unique autogenerated IDs. Lastly, I have a supply receipt table that stores Material/Supply, Quantity, and Date Received on each item received in our supply room. On the supply receipt form, I have a combobox "Material/Supply" that lists values from the Material Specifications table and allows the user to select one. My problem is that I also need to display the "Manufacturer" and "Supplier" values that are associated with the selected Material/Supply". I don't need to store the Manufacturer or Supplier, I just need them displyed for user reference. I'm new to Access 2010, so any advise on table field commands, query selection, and form fields will be greatly appreciated. I've attached what I have so far so that you can see what I've done.
Thanks,
John

 

[liddlem]

Make A Query That Displays Your 3 Fields.
In The Combos Data Source Property, Point It To Your Query.

 

[johndohnal]

Thanks liddlem for the suggestion. However, I'm still a little confused.

On the Form properties, I see Record Source.

On the ComboBox properties for Material/Supply, I see Control Source and Row Source. I'm using the Row Source for the SELECT..FROM..ORDER BY to display my list of material/supply for the user to choose.

On the TextBox for Manufacturer and Approved Supplier, I see Control Source.

Material/Supply is stored in the Material Specifications Table and has a lookup in the Supply Receipt Table.

Manufacturer is stored in the Manufacturer Table and has a lookup in the Material Specifications Table.

Approved Supplier is stored in the Supplier Table and has a looiup in the Material Specifications Table.

Am I supposed to include the lookup ID numbers for Manufacturer and Approved Supplier from the Material Specifications Table or the actual fields from the Manufacture and Approved Supplier Tables in the query you suggested?

If I point the Row Source of the ComboBox to the query, I loose the ability to Select the Material/Supply.

I know this probably seems basic to you, but I need more clarification.

Thanks for your help!
John

 

[liddlem]

No Problem.
Edit the Material/Supply field to include both the Manufacturer and supplier as follows.
On the Data tab, in the 'Row Source' property, put this code. (You could have changed it graphically but it might take a little longer for me to explain.)

SELECT DISTINCT [Material Specifications Table].[Material/Supply], [Manufacturer Table].Manufacturer, [Supplier Table].[Approved Supplier] FROM [Supplier Table] INNER JOIN ([Manufacturer Table] INNER JOIN [Material Specifications Table] ON [Manufacturer Table].ID = [Material Specifications Table].[Manufacturer Table_ID]) ON [Supplier Table].ID = [Material Specifications Table].[Supplier Table_ID];

This puts Material, Manufacturer and Supplier into the drop down list, but the user will still not see the content so click on the 'Format' tab.
Set the following parameters
Column Count = 3
Column widths = 5cm;2cm;3cm
List Width = 10cm

Now try it.

 

[liddlem]

Just another note - On each of your forms, you might want to set the 'Record Selectors' property to 'no'.

 

[johndohnal]

Thanks liddlem!

I was able to see all three fields and select a material/supply. I also added a fourth field (using JOIN). Bu using the .column() command, I was also able to display all four fields after selection in separate fields below the first field. I had been trying to use one or the other, but didn't realize you needed to use both together.

However, can you explain your last comment about the record selector property?

Thanks again!
John

 

[johndohnal]

Hi liddlem,

Another related form problem. On the same Supply Receipt Form, I'd like for the form to open to a new record. When I use

DoCmd.GoToRecord , , acNewRec

I lose the ability to select the material/supply in the first combobox. Same thing when I change the Form Data Entry property to yes. Any suggestions?

Thanks,
John

 

[liddlem]

Hi John
I see that your 'Supply Receipt' is sourcing its data from a query that is being run on the form, but you have already saved the query (called 'Supply Receipt Query').

When you save the query, it is referred to as a stored query. This greatly enhances the performance of the DB and (I find) that it greatly reduces the number of runtime issue that occur.

So, instead of putting the query in the ROW SOURCE of the Material/Supply field, simply choose the 'Supply Receipt Query' from the drop down list.

You will need to do the same thing for the Approved Supplier field.

As for the 'Record Selectors' thing, That is the bar on the left hand side of the form. It allows the user to select the whole record.
In essence, if your user clicks on the bar, and presses the delete button, they will delete the entire record. I doubt that is what your want them to do. (Also play with the 'allow deletions' properties of both the forms and fields if you want to prevent deletions.