Make text box automatic, based on drop-down menu

It actually is still a 1-to-1-to-1 relationship, though, because even though some products do go to other customers occaisionally, they are still referred to by the main customer, which is purely slang. 

If I use =[Combo69].Column as the Control Source, it causes the correct number to appear when the slang "Customer Name" is selected from the combo box.  That works just fine for my purposes, if I can also record this value in the table.  What was Marshall saying about using a    Me.PartNumber = Me.Combo69.Column(1)  command in VBA code?  How could I do that, and would it accomplish what I want? 

EDIT: I put in the line of VBA code, Me.[Part Number] = Me.Combo69.Column(1) and it didn't seem to have any effect.

The fact that a product may sell to more than one customer, however infrequently, means that there is a either a one-to-many relationship type from Products to Customers, which assumes that each customer can purchase only one product, or a many-to-many relationship type between Products to Customers, which assumes that, in addition to it being possible for a product to sell to more than one customer, each customer can purchase more than one product.  I'm going to forget about your actual table and column names for now and simply describe a model which corresponds to your description.

1.  Assuming a one-to-many relationship type first, the Products entity type has attributes PartNumber and AssemblyPartNumber, both of which are candidate keys, so the table modelling this entity type would be:

Products

....PartNumber (primary key)

....AssemblyPartNumber (indexed uniquely)

....other non-key columns

The Customers entity type would be modelled by a table like this:

Customers

....CustomerID (primary key)

....CustomerName

....PartNumber (foreign key, indexed non-uniquely)

....other non-key columns

Note that the only column common to both tables is PartNumber.

A form for entering rows into the Customers table can be based on a query which joins the tables, e.g.

SELECT Customers.*,

Products.AssemblyPartNumber

FROM Customers INNER JOIN Products

ON Customers.PartNumber = Products.PartNumber;

Other non-key column from Products can be included if necessary. The control bound to the PartNumber column from Customers should be a combo box with a RowSource property of:

SELECT PartNumber FROM Products ORDER BY PartNumber;

Once a part number is selected in the combo box the AssemblyPartNumber control will be filled automatically.  The Locked property of the AssemblyPartNumber  control ( and controls bound to any other columns from Products) should be set to True (Yes) and its Enabled property to False (No) to make it read only.

2.  With a many-to-many relationship type on the other hand, the model is:

Products

....PartNumber (primary key)

....AssemblyPartNumber (indexed uniquely)

....other non-key columns

Customers

....CustomerID (primary key)

....CustomerName

....other non-key columns

And to model the relationship type:

CustomerProducts

....CustomerID (foreign key, indexed non-uniquely)

....PartNumber (foreign key, indexed non-uniquely)

The last table resolves the many to-many-relationship type into two one-to-many relationship types.  It might have other columns modelling other attributes of the relationship type.  The primary key of this table, assuming a customer can purchase the same product only once is a composite one made up of both columns.  If a customer can purchase the same product more than once the key would be extended to include another column, e.g. a DatePurchased column.

With this model a form based on Customers can be used, and within it a subform based on the following query:

SELECT CustomerProducts.*,

Products.AssemblyPartNumber

FROM CustomerProductsINNER JOIN Products

ON CustomerProducts.PartNumber = Products.PartNumber;

Again the control bound to the PartNumber column from Customers should be a combo box with a RowSource property as above.  Similarly the AssemblyPartNumber control, and any controls bound to any other columns from Products, should be made read only by setting their Locked and Enabled properties as described above.

As the many-to-many relationship type is symmetrical, the interface could equally well be a Products form with a CustomerProducts subform within it of course, so that one or more customers could be entered per product rather than one or more products per customer.  The subform's query would in this case join the CustomerProducts to Customers and return the non-key columns from the latter of course.

So far we've modelled the relationship between Customers and Products on the basis of your description and described the appropriate interfaces.  Now we return to your original question, which presumably relates to the insertion of data via a form into some other table which has a foreign key CustomerID column.  Here we come full circle, as the only column needed in this other table is a CustomerID foreign key column, for which a combo box with a RowSource of:

SELECT Customers.CustomerID, Customers.CustomerName,

Customers.PartNumber, Products.AssemblyPartNumber

FROM Customers INNER JOIN Products

ON Customers.PartNumber = Products.PartNumber;

is needed.   To show the PartNumber and AssemblyPartNumber values two unbound combo boxes are needed with ControlSource properties of:

=cboCustomer.Column(2)

and

=cboCustomer.Column(3)

where cboCustomer is the name of the combo box bound to the CustomerID column.

The above model preserves all the information content which you and your boss wish, but does so by means of a set of correctly normalized tables with no redundancy, and consequently no risk of update anomalies.  The table and column names will differ from yours and you will doubtless have other columns representing other attributes of the entity type involved, but the model per se tallies with the reality of the business model as you've described it.

Right, good question.  I can't say too much of it for various reasons, and I don't particularly like the system set in place. 

I work for a very specific and specialized contractor company.  In my place of business, "Customer" and "Program" are frequently interchanged, as jargon and slang have developed by which to describe a particular product that we manufacture.  Each product is specific to a customer, though some products have been known to sell to other Customers, so for politics sake, "Customer" names assigned to each program are not considered "official".  EDIT: To clarify.  The Customer names are unofficially used to refer to the programs.

To solve that problem we have a "Final Part Number" which I have also been referring to as a "Program number".  Saying "part number" is misleading, as it sounds as if many of these parts make up the product, but really it is a specification of what product it is. 

And then there is also a secondary, specific "Part Number" which for convience sake I shall refer to here as "Assembly Part Number". 

Frequently, the Assembly Part Number is the exact same number as the Final Part Number.  They're both just specific delegated numbers assigned to each program to have a way to identify a product type without using a customer name. 

Talk about redundancies, right?  I know.

The desired outcome is to have all three pieces of information recorded, along with a lot of testing results, build date, etc. But because of the 1-to-1-to-1 relationship of the Customer Name, Final Part Number, and Assembly Part Number, the technicians wanted the two Part Numbers to automatically generate upon selection of Customer Name from the combo box.  And the boss wants all of these three pieces of information recorded.

I think it's time we stop considering the 'how' and start considering the 'what'.

A relational database is a model of part of the real world in terms of its entity types (modelled by tables) and their attributes (modelled by columns in the tables).  Relationships between entity types may be modelled, in the case of a one-to-many or one-to-one relationship type, by means of a foreign key in one table referencing the primary key of another table, or, in the case of a many-to-many relationship type, by another table which resolves the many-to-many relationship type into two or more one-to-many relationship types, e.g.

Orders----<OrderDetails>----Products

So, the question is what entity types are being modelled by your database and how are they related?  Clearly you have an entity type Customers, and by the sound of it an entity type Parts, which you say are related one-to-one.  This does sound a little unusual to me as a one-to-one relationship type is normally used to model a type/sub-type situation, e.g. Programmers would be a sub-type of Employees.  You also refer to Programs, so is this another entity type modelled by a table, and how does it to relate to Customers and/or Parts?

Only when we have a clear understanding of the reality, can we confidently advise you on the appropriate logical model, and how to implement this as tables.  Following from that we can advise on a suitable interface via forms, but once the logical model in terms of the tables and the relationships between them is firmly established as a robust and accurate model of the reality, the design of the interface should be straightforward, and fall into place naturally.

So, forget about tables, forms and all the other database stuff for the moment and concentrate on describing the real world business model with which you are concerned.  Without a clear picture of this we are having to make assumptions, which may be unwarranted, and consequently are at risk of pursuing red herrings in the advice we offer.

If that's not what you want, then you should probaly be using a subform for each table.  If that is a workable approach, you should use a query for each form/subform if for no other reason than to sort each form's records.