Error in my Search By - Search For Combo Boxes

C

Curtis Stevens

I have two combo boxes:

Search By Combo
Search For Combo

You select the field column name in BY and type in the name or data in the
FOR. For example, you want to search company name field and you type in ABC
Flowers in the FOR box.

It works, except when I type in a ' in the FOR combo, like ABC's Flower

I tried it & click debug and access highlights this line of code (MARKED
THIS LINE WITH >><<)

Private Sub SearchFor_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[" & Me.[SearchBy] & "] = '" & Me![SearchFor] & "'" <<<<<
Click to expand...
Click to expand...
Click to expand...
rs.FindFirst "[" & Me.[SearchBy] & "] Like '" & Me![SearchFor] & "*'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
Me.SearchFor = Null
End Sub

NO ERROR, for you reference:

Private Sub SearchBy_AfterUpdate()
Me.SearchFor.RowSourceType = "Table/Query"
Me.SearchFor.RowSource = "SELECT DISTINCT [" & _
Me.SearchBy & "] FROM [" & _
Me.SearchBy.RowSource & _
"] ORDER BY [" & Me.SearchBy & "]"
End Sub


Thanks!
Curtis
 
B

BruceS

Curtis,
Try

rs.FindFirst "[" & Me.[SearchBy] & "] = " & Chr(34) & Me![SearchFor] & Chr(34)

Haven't tested, but it should work with any internal character except a
double quote.

Bruce

Curtis Stevens said:
I have two combo boxes:

Search By Combo
Search For Combo

You select the field column name in BY and type in the name or data in the
FOR. For example, you want to search company name field and you type in ABC
Flowers in the FOR box.

It works, except when I type in a ' in the FOR combo, like ABC's Flower

I tried it & click debug and access highlights this line of code (MARKED
THIS LINE WITH >><<)

Private Sub SearchFor_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[" & Me.[SearchBy] & "] = '" & Me![SearchFor] & "'" <<<<<
Click to expand...
Click to expand...
rs.FindFirst "[" & Me.[SearchBy] & "] Like '" & Me![SearchFor] & "*'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
Me.SearchFor = Null
End Sub

NO ERROR, for you reference:

Private Sub SearchBy_AfterUpdate()
Me.SearchFor.RowSourceType = "Table/Query"
Me.SearchFor.RowSource = "SELECT DISTINCT [" & _
Me.SearchBy & "] FROM [" & _
Me.SearchBy.RowSource & _
"] ORDER BY [" & Me.SearchBy & "]"
End Sub


Thanks!
Curtis
Click to expand...
 
C

Curtis Stevens

Bruce,

Sorry, MS never sent me an email when you replied. That worked, thanks! I
noticed it also works when I take out the second line that is almost a
duplicate, right below it. Is that ok? It works, no issues, just want to
make sure it is needed for something else I haven't tested it for.

Referring to this line:
rs.FindFirst "[" & Me.[SearchBy] & "] Like '" & Me![SearchFor] & "*'"

Thanks
Curtis

rs.FindFirst "[" & Me.[SearchBy] & "] = " & Chr(34) & Me![SearchFor] & Chr(34)

Haven't tested, but it should work with any internal character except a
double quote.

Bruce

Curtis Stevens said:
I have two combo boxes:

Search By Combo
Search For Combo

You select the field column name in BY and type in the name or data in the
FOR. For example, you want to search company name field and you type in ABC
Flowers in the FOR box.

It works, except when I type in a ' in the FOR combo, like ABC's Flower

I tried it & click debug and access highlights this line of code (MARKED
THIS LINE WITH >><<)

Private Sub SearchFor_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[" & Me.[SearchBy] & "] = '" & Me![SearchFor] & "'" <<<<<
Click to expand...
rs.FindFirst "[" & Me.[SearchBy] & "] Like '" & Me![SearchFor] & "*'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
Me.SearchFor = Null
End Sub

NO ERROR, for you reference:

Private Sub SearchBy_AfterUpdate()
Me.SearchFor.RowSourceType = "Table/Query"
Me.SearchFor.RowSource = "SELECT DISTINCT [" & _
Me.SearchBy & "] FROM [" & _
Me.SearchBy.RowSource & _
"] ORDER BY [" & Me.SearchBy & "]"
End Sub


Thanks!
Curtis
Click to expand...
Click to expand...
 
B

BruceS

Curtis,

Glad it helped. Please click the "Yes" for "Was this post helpful?"

Can't picture what you do with the return after you get it, so it's hard to
answer the second question. If you use the second search, you need the
Chr(34)'s in the statement for it to handle the apostrophes when they're
entered.

The two searches are doing two different things:

The first one (without the asterisk) is only going to return an exact match,
e.g. "ABC's" if that's what you typed in.

The second one (with asterisk) will return anything that begins with the
string, e.g. "ABC's of Life", "ABC's Flowers & Gifts", "ABC's Insurance
Agency".

Doing them back to back, like in your code example, just doubles the
processing. The first search is immediately "undone" by the second. If the
table is not indexed on the field being searched, they could return two
different records.

If there could be multiple matches, you'll need to handle picking the right
record with your code. You can use FindNext to advance to the next record
and can test on NoMatch to see if there were no original or further matches.

HTH,
Bruce


Curtis Stevens said:
Bruce,

Sorry, MS never sent me an email when you replied. That worked, thanks! I
noticed it also works when I take out the second line that is almost a
duplicate, right below it. Is that ok? It works, no issues, just want to
make sure it is needed for something else I haven't tested it for.

Referring to this line:
rs.FindFirst "[" & Me.[SearchBy] & "] Like '" & Me![SearchFor] & "*'"

Thanks
Curtis

rs.FindFirst "[" & Me.[SearchBy] & "] = " & Chr(34) & Me![SearchFor] & Chr(34)

Haven't tested, but it should work with any internal character except a
double quote.

Bruce

Curtis Stevens said:
I have two combo boxes:

Search By Combo
Search For Combo

You select the field column name in BY and type in the name or data in the
FOR. For example, you want to search company name field and you type in ABC
Flowers in the FOR box.

It works, except when I type in a ' in the FOR combo, like ABC's Flower

I tried it & click debug and access highlights this line of code (MARKED
THIS LINE WITH >><<)

Private Sub SearchFor_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[" & Me.[SearchBy] & "] = '" & Me![SearchFor] & "'" <<<<<
rs.FindFirst "[" & Me.[SearchBy] & "] Like '" & Me![SearchFor] & "*'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
Me.SearchFor = Null
End Sub

NO ERROR, for you reference:

Private Sub SearchBy_AfterUpdate()
Me.SearchFor.RowSourceType = "Table/Query"
Me.SearchFor.RowSource = "SELECT DISTINCT [" & _
Me.SearchBy & "] FROM [" & _
Me.SearchBy.RowSource & _
"] ORDER BY [" & Me.SearchBy & "]"
End Sub


Thanks!
Curtis
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Pull Down Coding Not Working Well 4
One combo updates another comob, but need all records listed. 5
Search combo box 2
search with combo box on subform 3
flexibility in searching by adding “search by†combo box 1
Search combo boxes 0
Combo box doesn't search last names with apostrophe 2
Use combo box to force a selection in secondary combo boxes? 7

Top