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
1,115 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 84

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 500 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 84

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 500 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 84

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 500 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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
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…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

732 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