Newbie Help Please - Autofill?

[danny westman]

Hello all, I am a new Access user and I have a form that uses a junction table within the form as a subform or perhaps a subtable. In this subtable, I would like to autofill one field (description) based on the value chosen from another field (part number). The part number field is a drop-down box that the user scrolls and chooses the item so, based on that selection, I want the description field to automatically update. The descriptions and part numbers are contained in a separate table called Inventory. Could somebody please point me in the right direction?

Thank you all.

 

[MarkRennes]

I think that you've almost answered your own question. You are quite correctly storing different kinds of data in different tables - one table for each kind of thing or entity. In the subtable, you should keep the part number as what is called the foreign key - it's a foreign key because it's actually the primary key of another table, your inventory table. The right place to store the description of the part is in the inventory table - and nowhere else, certainly not in the subtable where it will constitute duplicate information. Instead, when you want to know what the description of a part is, you use a query. A query can be defined so as to join together data in your subtable and the inventory table. Then you can display it or use it to make dynamic reports.

If you need to be able to check the part number and description of a record you are adding to your subtable, look up the part number and its corresponding description using what's called a combo box as part of a form.

Reading between the lines, you may need a customer table; an order table with a customer ID as foreign key; an order details table (your subtable, your junction table) with order number and part number as two foreign keys; and of course the inventory table with part number as its primary key. You can then bring everything together by creating a form for customer with a subform for order with a subsubform for order details and a combo box on the order details subsubform to look up a part number and description.

 

[danny westman]

Thanks for the reply Mark! Actually, I have a combo box already set for the product number and though I could use another combo box for description, I'd rather have it populate automatically based on whatever is selected for the product number. That way, I don't get a mismatch between products and descriptions where the user can choose any item from either combo boxes. I was thinking that a WHERE condition macro could do this or perhaps a short SQL lookup.

Is that possible?

 

[boblarson]

Make sure the description field is included in the combo's rowsource and that the column count of the combo is set to the number of columns in the row source query. So, if you have ProductID and Product Name as fields in the query you would use 2 as the column count. You can then set the column widths to 1";0" to show the ID and not the description. And then just put a text box on your form and set it's control source to something like this:

=[ComboNameHere].[Column](1)

where 1 is the second column (because it is Zero-based)

 

[danny westman]

Thanks Bob. I'm a bit confused with your solution because I'm working with a table (junction table actually) within the form so I can't add additional fields here but only edit the field properties from the junction table itself which includes the description field. If that is still workable, how do I specifically reference the combo box for the Product ID in the description field properties? Currently, the combo box is showing both the Product ID alongside the Description when the user opens it up so all that is left is somehow getting the description to auto-populate based on the Product ID selection. I don't see a specific name for the combo box in the field properties in the junction table. Sorry, I'm trying to get my head around this so perhaps I am not clearly explaining my challenge? Thanks again.

 

[boblarson]

From your description of your set up you sound like you have lookups defined at the table level (using a combo directly in the table's field) which you should not have. See here for why.

What I'm talking about is on the form, you have a combo box (it has a name as it is a control on the form and you can find that name by going into the form's design view and clicking on it and then looking into the properties dialog and the name is the first thing listed in the ALL tab).

Perhaps if you can post a copy of your database (with bogus data of course) we can simplify trying to explain it all and go straight to the issue.

 

[danny westman]

Hi Bob, thanks again! Yes, you're correct that I'm using table-level combos and drop-downs and didn't realize the potential issue here. Please take a look at my DB and see where I'm going with this although I think your solution is the correct one which I'm working out now. I'm curious about your thoughts on my setup with the junction table within the form (Sales Order Entry) and how that ties back to the Sales Order Detail table. If I'm headed down the wrong path I'd like to stop the bleeding now. Your input is greatly appreciated!

 

[boblarson]

Hi Bob, thanks again! Yes, you're correct that I'm using table-level combos and drop-downs and didn't realize the potential issue here. Please take a look at my DB and see where I'm going with this although I think your solution is the correct one which I'm working out now. I'm curious about your thoughts on my setup with the junction table within the form (Sales Order Entry) and how that ties back to the Sales Order Detail table. If I'm headed down the wrong path I'd like to stop the bleeding now. Your input is greatly appreciated!

Okay, here you go. I made these changes:

• Removed the lookups from the product table and order details table.
• Removed the table subform from the main form and created an actual subform.
• Removed the description field from the order details table (you only need to store it one place)
• Set the control source for the description field on your subform to be what I initially had written in the previous post.
• Changed the row source in for the subform's productID combo and set the sort on description.
• Renamed the controls on the subform just to show how it normally is done.

There might have been some other things too, but I can't remember if I did.

 

[danny westman]

Thank you Bob! That makes sense and keeps the control in the definable objects and not in the tables themselves. This type of reference could also work for the Sales Order ID field on the subform, correct? Instead of having the user enter that number more than once on the main form then subform, the Sales Order ID field can simply auto-populate for each line item. Or is having this field even necessary?

 

[danny westman]

I answered my own question, it's not necessary with the existing relationships.

Thanks Bob!