Microsoft Access Forms

I need to learn a simple task. I have a form that enables me to enter users into a database. I enter into fields: "username", "Lname", "Fname", etc. I would like to be able to search through previously entered Last names (Lname) to edit users. if I put in record 32 John Doe, and later when I'm on record 100 or so, I want to be able to simply click a dropdown in "Lname" and searvh for Doe and find any and all records with that last name. possibly I might do it with the username instead but you get my drift.
AlfonsoPinaSr. Systems AdministratorAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
If you want to use a Dropdown, then do this:

1. Add a Combobox to your form. Do NOT set a ControlSource for that combo
2. Set the Combos Rowsource like this: "SELECT DISTINCT LName FROM YourTable"
3. In the AfterUpdate event of your Combo:

Me.Filter = "Lname = '" & Me.YourCombo.Column(0) & "'"
Me.FilterOn = True
0
AlfonsoPinaSr. Systems AdministratorAuthor Commented:
Ok, this gave me the ability to select a username from the list but it kept the same record I'm working on . for instance:

Username: John.doe
Lname: Doe
Fname: John

after selecting a different username from the list i get the same record but with the new username

UserName: jane.doe
Lname: Doe
Fname: John

so it didn't select the whole record for the username I'm looking for it simply changed the username of the current record. it is close though
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
I would assume that's not the same record, but rather the record associated with jane.doe (since they have the same LName value, but "Jane" comes before "John").

If you want to find the jane.doe record, you'd have to change the Filter to point to the correct one. I'd assume you'd want to filter for UserName instead of LName.
0
The Ultimate Tool Kit for Technolgy Solution Provi

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 for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

AlfonsoPinaSr. Systems AdministratorAuthor Commented:
Yes, sorry, I forgot to respond since I've been traveling.  I changed it to username because of other associations. Those were two different records. If I'm working on record two and for some reason I need to review the first record, I would like to select it from a drop-down.  I dont want to select the name and it simply change the username for the rexord I'm currently editing.  I want it to select and open that record. But that may be asking a bit much now that I think about it.
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
You generally do searches with an unbound combo, not a bound one, since changing the value of a bound control would update the underlying data.

So if you need a search combo, add a new combo to the form, do NOT set the ControlSource property, and use the code you posted first to locate the record.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
AlfonsoPinaSr. Systems AdministratorAuthor Commented:
I'm going to try that right now, let you know shortly how it comes out.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Office

From novice to tech pro — start learning today.