Solved

MS Access Form Field Lookup

Posted on 2011-02-19
4
402 Views
Last Modified: 2012-05-11
Hi all,

I am hoping someone out there knows a quick and easy answer to the following:

I have a form in MS Access.
I have a combo box on the form to select a username from a table called staff.
I want to select an entry in the above combo box and get it to auto-populate the text boxes named firstname, lastname and extension.

It sounds easy but is driving me crazy!!

Many thanks.
0
Comment
Question by:Howco
  • 2
4 Comments
 
LVL 31

Expert Comment

by:Helen_Feddema
ID: 34933258
There are two possibilities here:  

1.  Put the firstname, lastname and extension field in the row source of the combo box (with 0 width columns), then, from the combo box's AfterUpdate event, write their values to the appropriate controls using this syntax:

Me![txtLastNameFirst] = Me![cboSelect].Column(1)
(numbering is zero-based, so Column(1) is the 2nd column)

2.  Make the textboxes on the form unbound, and give them control sources referencing columns of the combo box, like this:

=Me![cboSelect].Column(2)

#2 is generally better, since it is a violation of normalization to have the same data in different tables (apart from key fields).
0
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 500 total points
ID: 34933262
use this as the rowsource of the combo box

select username,firstname, lastname, extension from staff

set the following properties of the combo

column count  4
bound Column  1

now, use the afterupdate event of the combo to populate the textboxes

private sub combo0_afterupdate()

me.firstname=me.combo0.column(1)
me.lastname=me.combo0.column(2)
me.extension=me.combo0.column(3)

end sub
0
 

Author Comment

by:Howco
ID: 34933272
Thanks for you reply.

Sorry, I should have mentioned that the combobox gets its data from a table called staff with columns in the table for username (used for selction in the combo), firstname, lastname and extension.

So I would click the combo and select a username that has been sourced from the staff table and then want the text boxes to take up the other fields of the same record.

0
 

Author Closing Comment

by:Howco
ID: 34933304
Absolutely faultless solution!

All sorted.  Many thanks to you and the others who contributed ideas.

This is the perfect solution though.  Many thanks!
0

Featured Post

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

685 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