Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Allow combobox selection to populate text fields (with data from multiple related tables) in a form (MS Access 2010) ?

Posted on 2013-02-04
6
Medium Priority
?
1,182 Views
Last Modified: 2013-02-04
Hello-

I am currently building a DB in MS Access 2010 and I am having some trouble with the forms.

I have built the DB with many related tables. I have now created a form that contains a few fields with data from various related tables. The form contains a combobox where a customer's full name can be selected and their related information will populate the fields within the form.


I am having trouble formatting the combobox so that when the client using the DB selects a customer's name in the combobox, it will display the customer's information in the text fields on the form.

I am new to MS Access and have no prior DB experience.

I am grateful for any help pertaining to this issue.

Thank you so much !
0
Comment
Question by:Daniel Van Der Werken
  • 3
  • 3
6 Comments
 
LVL 85

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 2000 total points
ID: 38852402
The simplest way is to include all the needed data in the combo's Recordsource, and then set the textboxes to the values in the combo's AfterUpdate event. For example, if I have a combo with a Recordsource like this:

SELECT CustomerID, CustomerName, CustomerPHone, CustomerEmail FROM Customer

And I have the combo's properties set as such:

ColumnCount = 4
ColumnWidths = 0,1,0,0

The user will see only CustomerName, but you can then run code like this in the AFterUpadate;

Me.txCustPhone = Me.MyCombo.Column(2)
Me.txCustEmail = Me.MyCombo.Column(3)

Note that columns are Zero-based, so Column(2) is the THIRD column ...
0
 
LVL 20

Author Comment

by:Daniel Van Der Werken
ID: 38852525
By the combobox Recordsource, do you mean the combobox ControlSource ?

Also, I would be selecting from multiple tables.
For instance, I want to populate the following text fields:
- First Name
- Last Name
- Company Name
- Department
- Location

In the combobox, the client will select the customer by their full name and then their info will be pulled from their respective tables and populate the text fields listed above.

You have coded the Select statement with From 1 table but I am actually pulling data from 4 different (but related) tables.

Do you know how I would do this ???
0
 
LVL 85

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 2000 total points
ID: 38852623
do you mean the combobox ControlSource
No - sorry, I mean RowSource, not RecordSource (I'm mixing my .NET with VBA :) )

Can you create a query that pulls in all that data? If so, you can use the query for the RowSource of your combo and still use the technique described above.

If not, then you can always use DLookups for this:

me.txCompany = DLookup("CompanyName", "Company", "EmployeeID=" & Me.MyCombo.Column(0)

See Access help for more information on DLookup.

Note too that you should only use this to DISPLAY that information on the form - the textboxes your "write" to should not be bound (i.e. should have no ControlSource). When storing related data in Child table, you should relate only the ID value of the Parent table - so if I relate an Invoice record to a Company, I store ONLY the CompanyID in the Invoice table, and not the CompanyName, Phone, etc etc ...
0
Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 
LVL 20

Author Comment

by:Daniel Van Der Werken
ID: 38852868
Ahh !! Okay, I have gotten it about 50 % functional.

I have created the necessary query and added some code. Now the First Name, Last Name and Company text fields are populated when I select a customer name from the combobox. The only problem I'm still having is populating the Department field (from the Department table) and the Location field (from the Location table).

Not sure why it still won't select from the other tables.
0
 
LVL 85

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 2000 total points
ID: 38852951
Are the Department and Location data included in the rowsource of the query?

If so, then are you sure that the ColumnCount of the query accurately reflects the number of columns? In other words, if you have 5 columns in your query, you need to set the ColumnCount = 5 in the combo.

If they're not, then you'll have to work with the query so that it will return the correct fields from those tables.
0
 
LVL 20

Author Comment

by:Daniel Van Der Werken
ID: 38853106
YES !! Thank you very much.
I needed to adjust the Column Count and Column Width.

Your advice solved the issues perfectly.

Thank you.
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

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

We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
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…
Suggested Courses

877 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