UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Find Records If Search Word Is Part Of A Field, Access 2010    
 
   
baffled100
post Jun 8 2017, 03:50 PM
Post#1



Posts: 330
Joined: 10-December 12



Hi,

I have a form that has a combo box that is used to find records in different ways, ie search by last name or first name or ID. The code in the after update property of the combo box is:

Me.RecordsetClone.FindFirst "[ID] = " & Me![Combo67]
If Not Me.RecordsetClone.NoMatch Then
Me.Bookmark = Me.RecordsetClone.Bookmark
End If

We have another field that we'd like to be able to look up a word that may be all or part of the field. The field is an 'other name'. So if someone has 'Jones-Smith' as an other name and a user enters 'smith' in the combo box, we'd like to be able to have the combo box list all records that contain 'Smith' . I hope I am explaining this clearly!

Can the code above be adjusted for this purpose?

Thank you!
Go to the top of the page
 
theDBguy
post Jun 8 2017, 04:18 PM
Post#2


Access Wiki and Forums Moderator
Posts: 70,670
Joined: 19-June 07
From: SunnySandyEggo


Hi,

How can someone enter "smith" in a combobox? Don't you ask them to "select" from available choices? Otherwise, why use a combobox at all?

Just curious...

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Microsoft Access MVP | Access Website | Access Blog | Email
Go to the top of the page
 
projecttoday
post Jun 8 2017, 04:57 PM
Post#3


UtterAccess VIP
Posts: 8,384
Joined: 10-February 04
From: South Charleston, WV


It looks like you're actually selecting an ID numerical field with the combo box as there are no quotes around Me.Combo67. If I get what you want, couldn't you add that other field to the combo box?

--------------------
Robert Crouser
Go to the top of the page
 
baffled100
post Jun 8 2017, 05:22 PM
Post#4



Posts: 330
Joined: 10-December 12



Thanks for your replies! When a user starts typing in the combo box, depending on how many letters are typed it, the record closest to what was typed (perhaps equal to) is highlighted.

Yes, the bound column of the combo box is the ID field (hidden from the drop down list). I did have other name in the combo box recordsource, but it doesn't work if the data in the field doesn't start with 'Smith'......does that make sense?
Go to the top of the page
 
tina t
post Jun 8 2017, 05:47 PM
Post#5



Posts: 5,163
Joined: 11-November 10
From: SoCal, USA


QUOTE
The code in the after update property of the combo box is:

Me.RecordsetClone.FindFirst "[ID] = " & Me![Combo67]
If Not Me.RecordsetClone.NoMatch Then
Me.Bookmark = Me.RecordsetClone.Bookmark
End If

We have another field that we'd like to be able to look up a word that may be all or part of the field. The field is an 'other name'. So if someone has 'Jones-Smith' as an other name and a user enters 'smith' in the combo box, we'd like to be able to have the combo box list all records that contain 'Smith' .

going back to your first post, the code you posted finds a record in a form's RecordSource, using FindFirst. the next paragraph (see above) asks how to filter the RowSource (the source of the droplist) of the combobox - that's a completely different operation.

can you clarify what exactly you're trying to do?

hth
tina

--------------------
"you can't take the sky from me"
Go to the top of the page
 
projecttoday
post Jun 8 2017, 07:11 PM
Post#6


UtterAccess VIP
Posts: 8,384
Joined: 10-February 04
From: South Charleston, WV


But does it work if you scroll to the one you are looking for? If so, could you make another combo box with that field as the first visible column?

--------------------
Robert Crouser
Go to the top of the page
 
baffled100
post Jun 9 2017, 08:12 AM
Post#7



Posts: 330
Joined: 10-December 12



Thanks for your replies.....sorry I am not being clear, but I basically want to be able to find all the records that contain a word that the user is trying to find in the other name field. I didn't know if there was a way to adjust the code above to do that, if not, then I'll just create a separate look up and not use a combo box. Thanks anyways!
Go to the top of the page
 
projecttoday
post Jun 9 2017, 08:33 AM
Post#8


UtterAccess VIP
Posts: 8,384
Joined: 10-February 04
From: South Charleston, WV


So why can't you just search on both fields?

--------------------
Robert Crouser
Go to the top of the page
 
theDBguy
post Jun 9 2017, 10:31 AM
Post#9


Access Wiki and Forums Moderator
Posts: 70,670
Joined: 19-June 07
From: SunnySandyEggo


Hi,

See if you can find some ideas from this page.

Good luck!

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Microsoft Access MVP | Access Website | Access Blog | Email
Go to the top of the page
 
baffled100
post Jun 12 2017, 03:11 PM
Post#10



Posts: 330
Joined: 10-December 12



Thanks very much theDBguy! The code in that link would be very helpful, but I can't seem to get it to work! I've created a database just to test the steps outlined in the link, and it's just not working for me. When I start typing in the combo box, a list never appears. I'm confused by what the underlined part of the code does

Private Sub suburb_Change()
Dim cbo As ComboBox ' Suburb combo.
Dim sText As String ' Text property of combo.

Set cbo = Me.suburb
sText = cbo.Text

Select Case sText
Case " " ' Remove initial space
cbo = Null
Case "MT " ' Change "Mt " to "Mount ".
cbo = "MOUNT "
cbo.SelStart = 6
Call ReloadSuburb(sText)
Case Else ' Reload RowSource data.
Call ReloadSuburb(sText)
End Select
Set cbo = Nothing
End Sub

I've confirmed that sText=the 4 letters that I typed in and that the rowsource is correct, but the list of records that match do not appear for the combo box. I just don't understand functions very well or how it is supposed to work with the sText value.....??
Go to the top of the page
 
theDBguy
post Jun 12 2017, 03:23 PM
Post#11


Access Wiki and Forums Moderator
Posts: 70,670
Joined: 19-June 07
From: SunnySandyEggo


Hi,

Did you follow Step 1 on the website? If so, can you post the complete code from your module? Thanks.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Microsoft Access MVP | Access Website | Access Blog | Email
Go to the top of the page
 
baffled100
post Jun 12 2017, 03:51 PM
Post#12



Posts: 330
Joined: 10-December 12



Thanks for your reply! Yes, I did step 1. I copied and pasted the code directly from the website.

Option Compare Database

Dim sSuburbStub As String
Const conSuburbMin = 3
Function ReloadSuburb(sSuburb As String)
Dim sNewStub As String ' First chars of Suburb.Text

sNewStub = Nz(Left(sSuburb, conSuburbMin), "")
' If first n chars are the same as previously, do nothing.
If sNewStub <> sSuburbStub Then

If Len(sNewStub) < conSuburbMin Then
'Remove the RowSource
Me.suburb.RowSource = "SELECT Suburb, State, Postcode FROM Postcodes WHERE (False);"
sSuburbStub = ""
Else
'New RowSource
Me.suburb.RowSource = "SELECT Suburb, State, Postcode FROM Postcodes WHERE (Suburb Like """ & sNewStub & "*"") ORDER BY Suburb, State, Postcode;"
sSuburbStub = sNewStub

End If
End If
End Function

Private Sub Form_Current()
Call ReloadSuburb(Nz(Me.suburb, ""))
End Sub

Private Sub suburb_AfterUpdate()
Dim cbo As ComboBox
Set cbo = Me.suburb
If Not IsNull(cbo.Value) Then
If cbo.Value = cbo.Column(0) Then
If Len(cbo.Column(1)) > 0 Then
Me.state = cbo.Column(1)
End If
If Len(cbo.Column(2)) > 0 Then
Me.postcode = cbo.Column(2)
End If
Else
Me.postcode = Null
End If
End If
Set cbo = Nothing
End Sub

Private Sub suburb_Change()
Dim cbo As ComboBox ' Suburb combo.
Dim sText As String ' Text property of combo.

Set cbo = Me.suburb
sText = cbo.Text

Select Case sText
Case " " ' Remove initial space
cbo = Null
Case "MT " ' Change "Mt " to "Mount ".
cbo = "MOUNT "
cbo.SelStart = 6
Call ReloadSuburb(sText)
Case Else ' Reload RowSource data.
Call ReloadSuburb(sText)
End Select
Set cbo = Nothing
End Sub
Go to the top of the page
 
theDBguy
post Jun 12 2017, 03:55 PM
Post#13


Access Wiki and Forums Moderator
Posts: 70,670
Joined: 19-June 07
From: SunnySandyEggo


Hi,

You said you created a test database to try out the technique. Did you create the table it needed? Can you post a copy of your test db? Thanks.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Microsoft Access MVP | Access Website | Access Blog | Email
Go to the top of the page
 
baffled100
post Jun 12 2017, 04:18 PM
Post#14



Posts: 330
Joined: 10-December 12



Sure, thank you!
Attached File(s)
Attached File  testdatabase.zip ( 25.9K )Number of downloads: 1
 
Go to the top of the page
 
theDBguy
post Jun 12 2017, 04:51 PM
Post#15


Access Wiki and Forums Moderator
Posts: 70,670
Joined: 19-June 07
From: SunnySandyEggo


Hi,

Thanks. Give this a try and let us know if it helps...

Attached File(s)
Attached File  testdatabase.zip ( 26.81K )Number of downloads: 7
 

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Microsoft Access MVP | Access Website | Access Blog | Email
Go to the top of the page
 
baffled100
post Jun 12 2017, 05:32 PM
Post#16



Posts: 330
Joined: 10-December 12



Thank you! It's working as I expected my version to work and the changes that I noticed you made don't seem to me to be what was causing my version to not work.....was there one thing that I had wrong that was the problem? I have very limited experience with functions and I don't really understand how calling the function with the sText value works....how does it become sSuburb? And what is the point of sSuburbStub = sNewStub ? pullhair.gif iconfused.gif shocked.gif


Go to the top of the page
 
theDBguy
post Jun 12 2017, 05:35 PM
Post#17


Access Wiki and Forums Moderator
Posts: 70,670
Joined: 19-June 07
From: SunnySandyEggo


Hi,

I did modify the code a little bit to make it work the way I prefer, but the differences between what you had and the one I posted are mainly in the Design View of the Combobox.

Namely, I changed the Column Count to 3 from 1 and set the Row Source Type to Table/Query from blank.

Hope it helps...

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Microsoft Access MVP | Access Website | Access Blog | Email
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    23rd September 2017 - 06:46 PM