Prev: Filter Form Based on Subform Combo Field Problem
Next: dlookup with multiple criteria
From: Carlnlola on 21 Apr 2010 14:44
I have a database of contacts (names, addresses, phone numbers, emails). I
have created a table with this info already filled out (imported from Excel).
All I want to do is create a form with a single drop-down list where, when
the last name is selected, all the other fields automatically populate with
that person's correct information.

I have tried DLookup but that seems too cumbersome and doesn't work unless I
go to design view and back to form view. Is there not a simple function in
Access that lets me do what I need to without having to write code. I am NOT
a code writer, don't understand it, but willing to try if that's the only
option.

From: golfinray on 21 Apr 2010 16:29
It is easier to use a form filter. Install a combobox. Allow the combo wizard
to do this for you. Set it up for Last Name. Then right click on the combo,
go to the afterupdate event, click the button to the right, and start the
code builder. Then type:
Me.Filter = "[lname] = """ & Me.combo# & """"
Me.Filteron = True

The combo# will be displayed, like combo10 or combo22.
--
Milton Purdy
ACCESS
State of Arkansas


"Carlnlola" wrote:

> I have a database of contacts (names, addresses, phone numbers, emails). I
> have created a table with this info already filled out (imported from Excel).
> All I want to do is create a form with a single drop-down list where, when
> the last name is selected, all the other fields automatically populate with
> that person's correct information.
>
> I have tried DLookup but that seems too cumbersome and doesn't work unless I
> go to design view and back to form view. Is there not a simple function in
> Access that lets me do what I need to without having to write code. I am NOT
> a code writer, don't understand it, but willing to try if that's the only
> option.
>
> .
>
From: Carlnlola on 21 Apr 2010 17:01
You are AWESOME! Thanks...works like a champ!

-Carl

golfinray wrote:
>It is easier to use a form filter. Install a combobox. Allow the combo wizard
>to do this for you. Set it up for Last Name. Then right click on the combo,
>go to the afterupdate event, click the button to the right, and start the
>code builder. Then type:
>Me.Filter = "[lname] = """ & Me.combo# & """"
>Me.Filteron = True
>
>The combo# will be displayed, like combo10 or combo22.
>> I have a database of contacts (names, addresses, phone numbers, emails). I
>> have created a table with this info already filled out (imported from Excel).
>[quoted text clipped - 9 lines]
>>
>> .

From: Carlnlola via AccessMonster.com on 22 Apr 2010 07:54
golfinray:

Ok...your solution works great but there's a small issue. My database
contains over 500 names and inevitably, there's duplicate last names. How do
I get the thing to show each person's info (right now, it filters out stuff
and the same info is displayed for 3 different Jones')

Thanks for taking the time to help out

-Carl

Carlnlola wrote:
>You are AWESOME! Thanks...works like a champ!
>
>-Carl
>
>>It is easier to use a form filter. Install a combobox. Allow the combo wizard
>>to do this for you. Set it up for Last Name. Then right click on the combo,
>[quoted text clipped - 9 lines]
>>>
>>> .

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-forms/201004/1

From: golfinray on 22 Apr 2010 08:55
One of the problems with using name is that there may be 4 or 5 Jones'. Can
you assign a unique ID number to each person, then search on the ID number.
You could even set up an ID like
001 Jones L
002 Jones T
003 Jones B
etc. Even better would be just to have cascading combo boxes (2) and have
a reference to the individuals, like Jones. Then the second combo you pick
T., B., or L. Look at the search feature Allen Browne has at
www.allenbrowne.com or Martin Greene at www.fontstuff.com
Good Luck!
--
Milton Purdy
ACCESS
State of Arkansas


"Carlnlola via AccessMonster.com" wrote:

> golfinray:
>
> Ok...your solution works great but there's a small issue. My database
> contains over 500 names and inevitably, there's duplicate last names. How do
> I get the thing to show each person's info (right now, it filters out stuff
> and the same info is displayed for 3 different Jones')
>
> Thanks for taking the time to help out
>
> -Carl
>
> Carlnlola wrote:
> >You are AWESOME! Thanks...works like a champ!
> >
> >-Carl
> >
> >>It is easier to use a form filter. Install a combobox. Allow the combo wizard
> >>to do this for you. Set it up for Last Name. Then right click on the combo,
> >[quoted text clipped - 9 lines]
> >>>
> >>> .
>
> --
> Message posted via AccessMonster.com
> http://www.accessmonster.com/Uwe/Forums.aspx/access-forms/201004/1
>
> .
>
 |  Next  |  Last
Pages: 1 2
Prev: Filter Form Based on Subform Combo Field Problem
Next: dlookup with multiple criteria