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,087 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 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
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering 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

Suggested Solutions

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

821 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