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
  • 3
  • 3
LVL 84
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 84
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.
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!


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 84

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

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

In case Office 2010 has not been deployed in your environment, this article may be quite useful. In our office, we wanted a way to deploy Microsoft Office Professional Plus 2010 through an automated batch file via logon script. This article is docum…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …

747 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now