?
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
?
430 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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
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

10 Questions to Ask when Buying Backup Software

Choosing the right backup solution for your organization can be a daunting task. To make the selection process easier, ask solution providers these 10 key questions.

Question has a verified solution.

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

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
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…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

770 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