Make text box automatic, based on drop-down menu

Question

I am using Microsft Access 2007.  I have a form that has a drop-down menu to select a customer from a separate table.  I have a text box next to it for the part number, and another text box next to that for the program number.  The P/Ns are specific to each customer.  So, I would like to make it such that, in this form, when a customer is selected from the menu, the P/Ns automatically generate.  How can this be done?

Answer 1

Generally, you should not save a calculated value in a table.  In this case, you can retrieve the value by using a query that joins to the customer table.

If you really have a good reason to do what you asked, then bind the text box to the table field and use a line of VBA code to set the value:

   Me.PartNumber = Me.Combo69.Column(1)

Answer 2

This is great help, thanks! 

I should mention, I have the Customer combo "Combo46" based on a table, which I have also listed Program Number and Part Number next to their respective Customer.  The Customer combo box in the form is only based on the first column.  So, you recommend that I use a text box, or a combo box, for the Program Number and Part Number? 

Thank you so much!

EDIT: 

Okay, so I figured out how to get the automatically generated P/Ns on the Form, but can I also get them to record in the master Table?  If I use "=[Combo69].Column" as the Control Source to get the automatic P/Ns, then I cannot put into Control Source to record the values in the Table, right?

I tried to put =[Combo69].Column in Default Value, but it didn't do anything

Answer 3

The fact that you have a text box for the part number suggests that there is only one possible part number value for each customer, rather than a subset of part numbers where each subset is specific to one customer.  The latter would require a correlated combo box, as Marshal describes, rather than a text box.  So the first question is which of these two scenarios is the case?

If it is, as your description implies, that there is only one part number value for each customer, then is the part number a column in the customers table?  If so there are a number of ways you can automatically insert the part number into a text box in the form when a customer is selected.  The simplest is probably to include the part number in a hidden column in the combo box (the correct term for the type of control which you describe as a 'drop-down menu').  The combo box would be set up along these lines:

Name:   cboCustomer

ContolSource:    CustomerID

RowSource:     SELECT [CustomerID], [PartNumber], [Customer] FROM [Customers] ORDER BY [Customer];

BoundColumn:   1

ColumnCount:   3

ColumnWidths:  0cm;0cm;8cm

If your units of measurement are imperial rather than metric Access will automatically convert them.  The important thing is that the first two dimensions are zero to hide the first two columns and that the third is at least as wide as the combo box.

This assumes that there is a column (field) CustomerID in the form's underlying table which is a foreign key referencing the primary key column (probably an autonumber) of the Customers table.

To show the part number in the form add a text box to the form, with a ControlSource property of:

=cboCustomer.Column(1)

The Column property is zero-based, so Column(1) is the second column, PartNumber.

In this scenario an important point is that the form's underlying table should not include a PartNumber column.  That would introduce redundancy and leave the table open to inconsistent data.  By looking up the part number value from the Customers table as above, on the other hand, the value associated with the customer for the record is guaranteed and redundancy is eliminated.

The other scenario is that for each customer there is a distinct subset of part numbers available, in which case a table Parts say, would have a foreign key column CustomerID.  So to create correlated combo box in which to select form that subset once customer has been selected, the cboCustomer control would be set up along these lines:

Name:   cboCustomer

ContolSource:    CustomerID

RowSource:     SELECT [CustomerID], [Customer] FROM [Customers] ORDER BY [Customer];

BoundColumn:   1

ColumnCount:   2

ColumnWidths:  0cm;8cm

The combo box in which to select a part number would be set up along these lines:

Name:   cboPartNumber

ContolSource:    PartNumber

RowSource:     SELECT [PartNumber] FROM [Parts] WHERE [CustomerID] = Form![cboCustomer] ORDER BY [PartNumber];

The other properties are left as the defaults.

In the AfterUpdate event procedure of the cboCustomer control you'd put:

    Me.cboPartNumber = Null

    Me.cboPartNumber.Requery

And in the form's Current event procedure you'd put:

    Me.cboPartNumber.Requery

If you are unfamiliar with entering code in event procedures, this is how it's done:

1.  Select the form or the combo box control and open its properties sheet if it's not already open.

2.  Select the relevant event property and select the 'build' button (the one on the right with 3 dots).

3.  Select Code Builder in the dialogue and click OK.  This step won't be necessary if you've set up Access to use event procedures by default.

4.  The VBA editor window will open at the event procedure with the first and last lines already in place.  Enter or paste in the code as new lines between these.

If neither of the above scenarios matches your situation, or there are any points on which you are unclear, post back with a detailed description of what you are trying to achieve, including details of the table underlying the form, and the tables from which the values for the combo box(es') list(s) are obtained.

Answer 4

Thanks! I'm a bit new to Access, so could you elaborate on "customercombo"?  Also, what would "Me.partcombo.Requery" prompt?

Answer 5

That's often called cascading combo boxes.  Set the part number combo box's RowSource to a query that uses criteria like:

  =Forms!yourform.customercombo

Then add a line of code to the customer combo box's AfterUpdate event AND to the form's Current event:

   Me.partcombo.Requery