auto populate fields

Question

Please somebody help.

I have a simple form with 3 fields on it.

PartNumber

Description

SerialNumber

I used 2 tables for this form:

1st Table: PartsList with 2 fields - PartNumber and Description both "Text".

2nd Table: MainTable with 3 fields - PartNumber , Description and Serial Number. all are "text" as well.

I created combo box for my PartNumber (i used PartsList Table) field and text box for Description and SerialNumber.

What I want to do is to auto populate the Description Field every time the user select a PartNumber from the drop down selection of the PartNumber combo box. But at the same time this records entered should me save in the MainTable.

Pleas help how to accomplish this. Appreciate your response. Thanks in advance.

Joey

Answer 1

Hi Scott.

Thank you for your reply.

Unfortunately I really need to store the description in my Main Table because I have lots queries and reports link to Main Table. Everyday there are multiple entry of parts removal and installation.

The only purpose of the PartsList table is to have the list of all the Parts we have which is more or less 100 items.

here is the actual sample of my Main Table PNoff is the PartNumber and Desc Off is the Description I mentioned above. I hope this can help to figure out how to solve this issue.

And yes I did check your blog but it is kinda difficult for me to understand since I'm just a beginner using access db. What I'm trying to achieve now is to solve this issue in the most simplest way.

Thank you again and I hope u can give more advise.

Joey

Answer 2

You don't need Description in your main table since its already in the Parts table. There is no need to repeat it.

Did you check out my blog as suggested. It discusses these issues and gives you several alternatives on how to deal with it.

Answer 3

Hi John,

Thank you for your advise.

Basically, the whole idea of my database is to store all the removal and installation of all the Parts we used everyday. Currently, the database is running but I want to prevent user to commit mistake because by selecting Part Number and Description on 2 separate combo boxes. Ideally, Each Part Number has a corresponding Description. That is why I created PartsList Table with all the list of our Parts. I created 2 fields on this table " PartNumber" and "Description" .

My other table i called it MainTable where I store all the Removal and Installation details in daily basis. I created TransID, PartNumber, Description, SerialNumber, DateRemoved, TechnicianName to name the few fields. User need to fill all these fields in a form I created but there are times that the user selected PartNumber with different Description.

2 things will gonna accomplish on this issue:

1st it will prevent the user to commit mistakes by selecting PartNumber and Description.

2nd it will be easier for the user to fill all the fields so it will save more time for us.

I hope u can give me more advise and technique how to accomplish this goal in my database.

Thanks a lot and appreciate your usual support.

Joey

Answer 4

Please review my Blog article on Displaying data from related tables. This deals with the situation you are encountering and discusses ways to handle it.

Answer 5

Well...

Don't.

The whole POINT of relational databases is to store information once, and once only, and then display it when you need it. The Description should exist in the Parts table, and ONLY in the parts table. If you have it both places, it wastes disk (trivial these days), but much more important, opens you to having invalid data. You (or someone else) could edit the Description field in the main table, the parts list, or both, giving you one part with two different descriptions!

To just display the description on the form (without storing it in the main table), simply include the description as a column in the combo box and put a textbox on the form with a control source like

=comboboxname.Column(n)

where n is the zero based subscript of the field you want to display - first column is (0), second is (1).