Dynamically Search Multiple Fields - Two Entries with the same name)

[kacey8]

So I used John's guide for a name search box.
http://www.access-programmers.co.uk/forums/showthread.php?t=188663

We are using the Database and have come across an error. We have two records (two names) excatly the same. When we use the dynamic search it shows both records in the results box. BUT when I try to load the record it doesn't load them. I think it may be a problem with my Select Record button which has the following code.

Dim bkmk As Variant
Dim strField As String
Me.RecordsetClone.MoveFirst
'Find the first record that matches what
'is in the search text box.
Me.RecordsetClone.FindFirst "Name Like " _
& Chr(34) & Me.SearchResults & "*" & Chr(34)
 
If Me.RecordsetClone.NoMatch Then
MsgBox "No Match"
Else
bkmk = Me.RecordsetClone.Bookmark
Me.Recordset.Bookmark = bkmk
End If

I can see excatly where the issue is, as it is choosing the first record in the list with that name. when in fact I need a piece of code to choose the record "selected" on the list.

How would I go about that?

 

[CJ_London]

when you bring back the information after the search, you also need to include the unique ID for the record (normally an autonumber) - this will probably be in your recordsetclone. Then you use this unique ID to retrieve the record rather than the name.

However looking at John's code I think this will be difficult to do since it assumes all records have unique names.

I have a routine which works on a form with a textbox to enter your search string and a subform to display the search results after each character is entered. You then select your choice from the subform. If this works for you then I can post the design

 

[kacey8]

The problem is I have just gone live with this with a large number of users, I don't want to drastically change anything on their end.

I can see that my Record Select button is finding the first record in the list that matches that name and displaying it on the form bellow, so it can be edited. (I've attached a screenshot of the form showing how I have it laid out)

I've obviously desensitised it.

As you can see, I think it's simple, they enter the name at the top, it narrows down on the list bellow, they select the name then hit select record and it loads said record bellow to be editted.

I'll be honest I am a novice when it comes to Access, if you think what you have created works, as long as you don't mind helping me impliment it I can certainly try.

 

[CJ_London]

Your searchresults listbox needs to be populated with two fields, the unique ID for the record (which can be hidden if required) and the name. And the on click event on the button needs to be modified to find the record based on the ID rather than the name. Not quite sure how your code is working but I would expect the change would be to change

Me.RecordsetClone.FindFirst "Name Like " _
& Chr(34) & Me.SearchResults & "*" & Chr(34)

to something like
Me.RecordsetClone.FindFirst "ID=" & Me.SearchResults

I've also just noticed you are using Name which is a reserved word and will cause complications in the future

 

[kacey8]

How would I change the search result box to return the name & the ID?

I've changed the button over, just need it to display the Name & the ID for each record. (and if possible hide the ID)

 

[CJ_London]

The listbox will currently have a rowsource of something like

SELECT Name from MyTable WHERE ....

you need to change this to

SELECT ID, Name from MyTable WHERE ....

 

[CJ_London]

you'll also need to change the number of columns from 1 to 2

 

[kacey8]

So the current is

SELECT [QRY_SearchAll].[Name] FROM [QRY_SearchAll] ORDER BY [Name];

So I would want?

SELECT [QRY_SearchAll].[Name],[QRY_SearchAll].[ID] FROM [QRY_SearchAll] ORDER BY [Name];

 

[kacey8]

Tried the code above but on the select button the line in red is apparently wrong (debugging)

Dim bkmk As Variant
Dim strField As String
Me.RecordsetClone.MoveFirst
'Find the first record that matches what
'is in the search text box.
[COLOR=red]Me.RecordsetClone.FindFirst "ID=" & Me.SearchResults[/COLOR]
 
If Me.RecordsetClone.NoMatch Then
MsgBox "No Match"
Else
bkmk = Me.RecordsetClone.Bookmark
Me.Recordset.Bookmark = bkmk
End If

 

[CJ_London]

ID and name need to be the other way round i.e.

SELECT [QRY_SearchAll].[ID],[QRY_SearchAll].[Name] FROM [QRY_SearchAll] ORDER BY [Name]

I'm also assuming you have a field called ID in you QRY_SeachAll query. You may be calling it something else

Again, I will advise you that Name is a reserved word - you will undoubtable have problems at some point unless you change it.

Here is a link to reserved words

http://support.microsoft.com/kb/286335

 

[kacey8]

Hi, Nope it is listed as ID in the Query,

 [Web Leads Table].ID

I assume that is correct, thats the segment of the SQL code for the query relating to the ID

EDIT

Nevermind, found the column count. so it is displaying two columns

 

[kacey8]

Again, I will advise you that Name is a reserved word - you will undoubtable have problems at some point unless you change it.

Here is a link to reserved words

http://support.microsoft.com/kb/286335

I'll change this ASAP... but as users are in it right now I can't. I assume it I change the field name in the BackEnd the users individual access files will stop working.

 

[CJ_London]

When you change the field name to something else you will need to review all queries, forms and reports plus any code such as that you have posted in this thread that refer to that field to change the field name there as well.

Is the form now working as required?

 

[kacey8]

Hi,

Yes of course, Luckily I have only around 15 queries, 20 reports and maybe 6-7 forms to check.. I will wait till the software is not being used.

It's working perfectly, Thanks to your help. I will also mark this thread as Solved,

Thank you good man.

 

[CJ_London]

no problem - happy to help