Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Combobox, continuous form, Update an unbound field with data from master table based upon data in combobox

Posted on 2010-08-23
10
Medium Priority
?
432 Views
Last Modified: 2013-11-28
I have a form that has a combobox that is used to select a product from the product master table. There is a selection query behind the combobox that selectes ProductID, ProductCode, and ProductDesc. This is a continuous form. The data entry person knows the product codes and wants to enter those in records. However, they would like for the product description to be displayed next to the product code alongside every record. How can I make that happen?

If I put an unbound field, the same data is displayed for every record. It appears I have to add a field in the transaction table for the product description. That kills the design of a relational database. Then, what happens if the description changes in the product master table? The new description will not be in the transaction table.
0
Comment
Question by:rodneygray
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
10 Comments
 
LVL 75
ID: 33504806
Can't you just make the Description appear in the 2nd column of the combo box ?

mx
0
 
LVL 75
ID: 33504830
Sorry ... I see what you mean ...

Instead, have your Form's query Join to the Lookup table with the Codes and Description.
Bring the Description field into the query ... and make it the Control Source for a new field on the form.

mx
0
 
LVL 75

Accepted Solution

by:
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 1000 total points
ID: 33504937
Something like this for the Form's Record Source:

SELECT Table1.*, Table2.ProdDscpt
FROM Table1 LEFT JOIN Table2 ON Table1.ProdCode = Table2.ProdCode;

Table2 in this example would be your Product Code lookup table.

mx
0
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
LVL 7

Assisted Solution

by:jpipkins
jpipkins earned 1000 total points
ID: 33505629
Set your control source on the text box to Combobox.column(2).
0
 
LVL 75
ID: 33505965
Actually, his solution does work and I really should have thought of that. Some how, I was reading the Q differently.

It just probably needs to be

=[YourComboBoxName].[column](1)

Remember that the Column() property is Zero Based ... physical col 1 = Column(0) ... 2 >> 1 and so on.

mx
0
 
LVL 7

Expert Comment

by:jpipkins
ID: 33505992
Actually, it does work.  I'm looking at it right now.  I have a combobox named Combo0 with three columns.  Next to that I have a textbox.  The Control Source of the text box = "=Combo0.column(2)"  I've done this numerous times and it's a very effective and simple solution, especially if you have multiple occurrences on a form.  With DMX's solution, be sure to make the text box read only if you don't want the users editing the product description.
0
 
LVL 7

Expert Comment

by:jpipkins
ID: 33506004
Thanks mx!  'Preciate the support!
0
 
LVL 75
ID: 33506069
rodneygray:
Confirm that jp's solution does in fact work, then hit the Request Attention button to reopen the Q and give him credit.  Whereas my solution certainly works, it's a bit of overkill for this situation.

mx
0
 
LVL 1

Author Closing Comment

by:rodneygray
ID: 33530251
Darn vba! I had tried jpipkins solution. However, as stated above, it didn't appear to work. It was after DatabaseMX and jpipkins stated that it would work that I looked back over the form. I had mis-spelled the combobox name. Thanks for your help.
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

618 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question