Field from Aliased Table Not Staying on Form

[dleewms]

I am using Access 2007. I have a Parts table that I have linked twice to my Work Ticket table. I have a form based on the Work Ticket table. The Parts table simply has the Part ID, Part Description and Serial number. Because a work ticket can have several parts, I have joined the parts table to the work ticket table multiple times (using left joins) and created aliases. When I select the part ID from a combo box on the the form, I have it displaying the value from the corresponding Part Description and Serial number fields. This works perfectly for the first part. I have also created another drop down combo box from which I select the second part ID. However, when I select the part description from my aliased Parts table (for the second part), it only displays the description associated with the first part. As a matter of a fact, it changes the control source of the part number field to the first, unaliased table. I have provided an example below.

tblParts

ID Desc Serial Number
012 Hammer 012Hammer
023 Pliers 023Pliers
789 Wrench 789Wrench

I place the following fields in the Design View of Form and select parts 012 and 023. I expect
Parts.ID Parts.Desc Parts_1.ID Parts_1.Desc
012 Hammer 023 Pliers

Instead I get
012 Hammer 023 Hammer

And when going back to the design view and taking a look, I see my Parts_1.Desc field has changed to to Parts.Desc
Parts.ID Parts.Desc Parts_1.ID Parts.Desc

Can anyone tell me what is going on?

Thanks!

 

[lameid]

First, as part of good database design you should have a normalized database (look up Normalization Rules).

In this case that means you should have a Work_Ticket_Parts table of some sort that relates the one ticket to the many parts. Managing your data this way, this new table would be a subform instead of a series of similar controls / fields.

Now that I've said the correct way to fix it let's look at the other options, Access is likely autocorrecting the fields for you. I don't know where that option is in your version of Access but turning it off might help. Best practice is to turn this off anyway as it can cause performance problems.

Another workaround would be to alias the fields in your query so that the query field names are more along the lines of Part1Desc and Part2Desc. That way access can't autocorect the distinct names.

Please note that the first solution is the right one.