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. ScottGem 68,840 Reputation points Volunteer Moderator
    2011-03-23T12:09:41+00:00

    Maybe I can clear this up a bit. One of the guiding principles of relational databases is that data appears in one place and one place only. So if you have a table with Customer, part number and program number info, that that's the ONLY table where that data should appear. The ONLY value from that table that should appear in any other table is the Primary key value stored as a foreign key.

    Now its not clear what the table is that the form you are working with is bound to, but it is clear the only value from the Customers table should be the primary key value as the FK.

    One thing you don't say is whether a customer can have more than one part. That has a bearing on your design.

    Was this answer helpful?

    0 comments
  2. Anonymous
    2011-03-23T12:14:52+00:00

    The Customer, Part Number, and Program Number have a 1-to-1 relationship.

    So, are you all suggesting that I only record one of the three pieces of information (Customer, Part Number, Program Number) in the final record table?  Because I figured that was a good solution, but my boss wants all three recorded in the final table.

    Was this answer helpful?

    0 comments
  3. Anonymous
    2011-03-23T12:42:27+00:00

    Regardless of what words your boss is using, you should be hearing that he wants to see the three pieces of data in one place.  You, as the application's developer, should never let any user, including your boss, see, much less manipulate, a table's datasheet.  Therefore, you need to create forms and/or reports for users to view or edit the data in ways they find useful.  The forms (possibly with subforms) should be based on queries that gather related data into a coherent presentation.

    The design (organization of data) of the tables should be based on the rules of normalization, not on the whims of anyone's idea of what they want to see on the screen.  It's your job to properly structure the data tables and then to figure out how to use queries, forms/subforms and reports to present the data in a useful way.

    Was this answer helpful?

    0 comments
  4. Deleted

    This answer has been deleted due to a violation of our Code of Conduct. The answer was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.

    Comments have been turned off. Learn more

  5. Anonymous
    2011-03-23T14:45:57+00:00

    Understood!

    So I made a query that draws from the query where the Form dumps information (I have front-end/back-end databases, so I dump into a query instead of the table directly) and the known set of Customer/Part/Program information. 

    EDIT:  Well, that didn't really work, because it had tons of duplicates...

    Was this answer helpful?

    0 comments