Search function clarification

[NascarBaritone]

I was reading and following the post titled "Search Function" that I found using the search here in the forums. I followed the SearchDB.mdb file and have a working search text box in my database. My only problem is that when I search for a name that has multiple entries ("Wilson" or "Smith" for example), regardless of the name that I click on it goes to the first record with that last name.

To clarify, I have a James Wilson, Gail Wilson, and Sue Wilson in my database. When I search for "Wilson" all three come up in my pop-up box as they should, but if I click on Gail Wilson or Sue Wilson it takes me to James Wilson's record.

I have a feeling there might be a way to have it look for a unique field (First names will be unique to the last name as I don't have any duplicate names, i.e., two Sue Wilson's), but I don't know how.

Any idea where I might have strayed?

 

[Alansidman]

Take a look at this and it will solve your issues.

http://www.datapigtechnologies.com/flashfiles/searchform.html

Alan

 

[NascarBaritone]

Perhaps I missed it, but I don't think I saw in the video where my question was answered. It shows how to create a search box, but doesn't address how to go to a specific record when the results are returned.

I have attached the database I used as a reference. What I am trying to figure out is how, when you enter "Smith" in the surname search for the database, after you click Lisa Smith, it take you to her record. However, if you click Bob Smith, it takes you to his record.

Like I said, I think it has to do with the unique ID number (which I do have unique numbers attached to each of my records, I'm just not using them in the query), but I'm not certain.

 

[Alansidman]

okay, I think I mis-understood your question. If what you want is to open a record while searching it in a form, then this will do it for you. If I missed the boat again, then please post back

http://www.datapigtechnologies.com/flashfiles/buildcustomfilter.html

 

[NascarBaritone]

Again, thank you for the video, however it doesn't address my need.

It shows what I have already done to an extent. However, it doesn't address how Access can differentiate between two records.

The database I attached is exactly what I need, I just don't completely understand how it works or where I'm missing something. If you open the database and look at the duplicate surnames, but with different first names and how Access goes to the right one, not the first one, you'll see what I am talking about.

 

[NascarBaritone]

I'm bumping this to see if anyone can clarify the search function for me. I'm still not getting a grasp on how Access differentiates between records with the same last name as in the attached database from my previous post.

 

[Alansidman]

Seems to be working ok now. I changed the last line of the code for the surname query. For first name and last name it returns the record clicked on in the list box.

 

[NascarBaritone]

I'm just really not explaining myself well.

That isn't actually my database, I'm just trying to duplicate the search function that is being used.

Where I am getting stuck is after doing the search, my box opens with the correct search results. However, when I click on the record, it goes to the first record with the matching last name if there are multiple people with the same last name.

For example, when I search for "Wilson" in my form, my box comes up like this:

Wilson, Gail
Wilson, James
Wilson, Sue

If I click on James Wilson's record, it takes me to Gail Wilson (because she is the first "Wilson" in the database). They do have unique numbers that I can include in the query, but when I put that column in the query, save it, include it on the OnClick event for the search results list box it doesn't take me to any record.

 

[NascarBaritone]

I hate when it is always the simplest method.

I just needed to change the GoToControl to the unique number and keep everything else the same. That way it searches for the last name, but also matches up with the unique number.

Thanks for the help and patience.

 

[ajentp]

Re: Search function

okay, I think I mis-understood your question. If what you want is to open a record while searching it in a form, then this will do it for you. If I missed the boat again, then please post back

http://www.datapigtechnologies.com/flashfiles/buildcustomfilter.html

I checked your video, You work is awesome. I have the question if the same trick can work where main form has two sub forms. Also if instead of using combo box, can we use text box where user type in the some thing and it can populate the form for that records such as if customer type item code, it populate all the records in the form.