I want to create my own search/find form to use when the user selects my Add New Record button. This way, before they can add a new record, they must search to see if the person they want to enter is in there already. I'd like to have the first and last name fields on the form so they can search either or both. A drop-down list similar to that displayed for FilterByForm would be a bonus. Any suggestions?
Pop-up form form searching records
- Thread starter lscheer
- Start date
Jack Cowley
Registered User.
- Local time
- Today, 01:27
- Joined
- Aug 7, 2000
- Messages
- 2,639
Why don't you let Access do the search? Let the user enter the First Name then Last Name and then let Acccess check to see if this person exists. If they do, then tell the user. If they don't then the user can carry on none the wiser that Access has already checked for them. You can use DLookup (check for First and Last name not just last name only) in the Before Update event of Last Name to look for the name and if they exist then let the user know, otherwise let them continue.
[This message has been edited by Jack Cowley (edited 08-15-2001).]
[This message has been edited by Jack Cowley (edited 08-15-2001).]
jatfill
Registered User.
- Local time
- Yesterday, 20:27
- Joined
- Jun 4, 2001
- Messages
- 150
Excellent point, Jack...
I tackled this issue just a few months ago on a project... I was originally requested to add some kind of "autofill" to detect duplicate names for registration forms. This as THEY described it was next to impossible, so I did the following:
On the data entry form, after the first and last name are filled out, a query runs that checks for identical names in the fashion of
Like "*" & Forms![Formname]![FirstName] & "*"
Like "*" & Forms![Formname]![LastName] & "*"
The initial code that fires off after the name is entered simply checks the count of the query results, based on the criteria above. If there are none (matches), the user simply continues working, none the wiser.
If there are matches, the user receives a message box informing them of possible matches and gives them the option to display a list box of those matches so they can do a quick check & see if the person exists.
If the person exists, they double click on the record in the list box, the addition is canceled, and they are taken to the user's recordsheet to verify their existing address, etc. If they cancel from the listbox results form, they just continue working in the add form without losing anything.
My end users loved it... it doesn't force anything, but it still intervenes when necessary. And with those parameters it even catches variances like "matt" vs. "matthew" (not the opposite though) or "liz" vs. "elizabeth"
good luck... let me know if you'd like a sample
I tackled this issue just a few months ago on a project... I was originally requested to add some kind of "autofill" to detect duplicate names for registration forms. This as THEY described it was next to impossible, so I did the following:
On the data entry form, after the first and last name are filled out, a query runs that checks for identical names in the fashion of
Like "*" & Forms![Formname]![FirstName] & "*"
Like "*" & Forms![Formname]![LastName] & "*"
The initial code that fires off after the name is entered simply checks the count of the query results, based on the criteria above. If there are none (matches), the user simply continues working, none the wiser.
If there are matches, the user receives a message box informing them of possible matches and gives them the option to display a list box of those matches so they can do a quick check & see if the person exists.
If the person exists, they double click on the record in the list box, the addition is canceled, and they are taken to the user's recordsheet to verify their existing address, etc. If they cancel from the listbox results form, they just continue working in the add form without losing anything.
My end users loved it... it doesn't force anything, but it still intervenes when necessary. And with those parameters it even catches variances like "matt" vs. "matthew" (not the opposite though) or "liz" vs. "elizabeth"
good luck... let me know if you'd like a sample
jatfill
Registered User.
- Local time
- Yesterday, 20:27
- Joined
- Jun 4, 2001
- Messages
- 150
Sure thing...
I'm going to post it here, let me know if you have any problems downloading it or running (it's Access 2000-based)...
it's just a quick sample of the name function I mentioned without a bunch of extra stuff... so it's extremely small & easy to download...
Name Search - sample database
[This message has been edited by jatfill (edited 08-16-2001).]
(Edit: I'm really sorry if this gets bumped to the top, I had to update the link)
I'm going to post it here, let me know if you have any problems downloading it or running (it's Access 2000-based)...
it's just a quick sample of the name function I mentioned without a bunch of extra stuff... so it's extremely small & easy to download...
Name Search - sample database
[This message has been edited by jatfill (edited 08-16-2001).]
(Edit: I'm really sorry if this gets bumped to the top, I had to update the link)
Last edited:
jatfill
Registered User.
- Local time
- Yesterday, 20:27
- Joined
- Jun 4, 2001
- Messages
- 150
no problem, I'm glad you like it...
I had a couple of folks ask for an Access 97 version, so here it is...
Name Search - Access 97
(sorry about editing such an old message... same problem, updating the urls for my new webspace)
I had a couple of folks ask for an Access 97 version, so here it is...
Name Search - Access 97
(sorry about editing such an old message... same problem, updating the urls for my new webspace)
Last edited:
sudhirhinduja
Registered User.
- Local time
- Yesterday, 20:27
- Joined
- Aug 17, 2004
- Messages
- 41
Hey Jatfill,
Could I get a sample of this database? The link does not seem to work. If you are not able to upload it my email is sudspark@gmail.com
Thanks a lot,
Sudhir Hinduja.
Similar threads
