Microsoft Access Forms

Posted on 2014-10-29
Last Modified: 2015-01-08
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.
Question by:AlfonsoPina
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
LVL 85
ID: 40411611
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

Author Comment

ID: 40411848
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
LVL 85
ID: 40412876
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.
Office 365 Training for Admins - 7 Day Trial

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.


Author Comment

ID: 40418798
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.
LVL 85

Accepted Solution

Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 500 total points
ID: 40419195
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.

Author Comment

ID: 40420741
I'm going to try that right now, let you know shortly how it comes out.

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

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

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
This article describes how to import an Outlook PST file to Office 365 using a third party product to avoid Microsoft's Azure command line tool, saving you time.
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…

627 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