Solved

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

Posted on 2010-08-23
10
423 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
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 - Access MVP) earned 250 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
 
LVL 7

Assisted Solution

by:jpipkins
jpipkins earned 250 total points
ID: 33505629
Set your control source on the text box to Combobox.column(2).
0
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
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

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

758 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now