• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1248
  • Last Modified:

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

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
Daniel Van Der Werken
Asked:
Daniel Van Der Werken
  • 3
  • 3
3 Solutions
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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
 
Daniel Van Der WerkenIndependent ConsultantAuthor Commented:
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
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Daniel Van Der WerkenIndependent ConsultantAuthor Commented:
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
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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
 
Daniel Van Der WerkenIndependent ConsultantAuthor Commented:
YES !! Thank you very much.
I needed to adjust the Column Count and Column Width.

Your advice solved the issues perfectly.

Thank you.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now