Make text box automatic, based on drop-down menu

Anonymous
2011-03-21T18:06:56+00:00

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?

Microsoft 365 and Office | Access | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

45 answers

Sort by: Oldest
Most helpful Newest Oldest
  1. Anonymous
    2011-03-25T13:20:36+00:00

    You are trying to copy the part number from the programs table to the table with the produced units.  That is putting the same data in two places.  The only thing that can appear in more than one table is the primary key from a parent table.  A field in a child table that contains the primary key from another table is known as a foreign key field.  The foreign key is the only information needed to retrieve all the other fields in the parent table's related record.

    If your boss told you to do anything else, then you no longer have a relational database and might as well be using a spreadsheet.

    Alright, lecture over.  As reluctant as I am to facilitate your creation of an unnormalized problem database, I'll try one more time to explain how to replace a text box expression with VBA code so the calculated value can be saved.

    If the [Part Number] text box uses the expression:

        =Me.Combo69.Column(1)

    and displays the desired value, then you can change the text box's control source from the expression to the PN field in the form's record source table and use the line of code:

       Me.[Part Number] = Me.Combo69.Column(1)

    to set the value.  The text box should then display the same value as before AND save the value to the PN field in the form's record source table.  If it does not, then you have missed something somewhere.

    Was this answer helpful?

    0 comments
  2. Anonymous
    2011-03-25T14:40:58+00:00

    Where do I place that line of code?  Not just anywhere in the VBA Code Builder, right?

    Was this answer helpful?

    0 comments
  3. Anonymous
    2011-03-25T15:23:34+00:00

    The line of code goes in Combo69's AfterUpdate event procedure.  Since that's where you had it before and you said it did not do anything, either you are missinterpreting what's happening or you did something wrong.

    There should be no visible difference on the form so you have to do something to save the record (navigate to a different record in the form, close the form, etc) and then check the record in the table to see if the PN field was set to the part number from the combo box.

    If the PN field did not get saved with the record, then you did something wrong and you should carefully review the steps I provided earlier.

    Was this answer helpful?

    0 comments
  4. Anonymous
    2011-03-25T15:32:08+00:00

    Yeah, it doesn't seem to be doing anything, let alone what I want it to accomplish, but I can't even think of a single thing I might be missing.

    Was this answer helpful?

    0 comments
  5. Anonymous
    2011-03-28T20:22:16+00:00

    What if I were to make the P/N boxes into combo boxes, and only show Column(1) and Column(2), as i'm showing Column(0) as the Customer/Program name combo?  Is there some sort of way to link those?

    Was this answer helpful?

    0 comments