Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

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,156 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
[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
  • 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
Technology Partners: 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 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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

670 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