Solved

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

Posted on 2010-08-23
10
425 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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
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
 
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

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

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

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

806 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