?
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
?
433 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
  • 5
  • 3
9 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

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

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
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.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Suggested Courses

850 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