compile error using FindNext command

  • Thread starter Thread starter tstew
  • Start date Start date
T

tstew

Hello,

I have an unbound text box on a form (single) and I want to search one field
in my database for matching records. The field is house addresses like "1234
Main St. #12" or "5678 Rancho Santa Margarita". There are around 10,000
records in the database. Some of the addresses will have the same begining
numbers, like "120 Mesa Ave." and "120 Starlight Dr.". I would like to use
the FindNext command, but the code below doesn't compile. I'm ok with cycling
through the few matching records until I get the one I want, there aren't
that many with the same starting digits. Any ideas how to make the code below
work?

BTW, I've tried going through Allen Browne's code and that is over my head.

THANKS!
Mark
Private Sub CMDFindAdd_Click()
Dim strAddressRef As String
Dim strFindAdd As String

'Check txtFindAdd for Null value or Nill Entry first.

If IsNull(Me![txtfindadd]) Or (Me![txtfindadd]) = "" Then
MsgBox "Please enter a value!", vbOKOnly, "Invalid Search!"
Me![txtfindadd].SetFocus
Exit Sub
End If
'---------------------------------------------------------------

'Performs the search using value entered into txtfindadd
'and evaluates this against values in Address

DoCmd.ShowAllRecords
DoCmd.GoToControl ("Address")
DoCmd.FindRecord Me!txtSearch

ADDRESS.SetFocus
strAddressRef = ADDRESS.Text
txtfindadd.SetFocus
strFindAdd = txtfindadd.Text

'If matching record found sets focus in PicLocation

If strAddressRef = txtfindadd Then
ADDRESS.SetFocus

'If value not found sets focus back to txtSearch and shows msgbox
Else
MsgBox "Match Not Found For: " & strSearch & " - Please Try
Again.", _
, "Invalid Search"
txtfindadd.SetFocus
End If
End Sub
 
This code should find all addresses that match the portion typed in the
search box,
assuming a continuous form.


Private Sub CMDFindAdd_Click()
Dim strWhere As String

If Len(Me.txtFilterAddress & vbNullString) > 0 Then
strWhere = "([Address] Like """ & Me.txtFilterAddress & "*"") "
End If

Me.Filter = strWhere
Me.FilterOn = True
End Sub
--------------------------
Note: the code assumes an unbound textbox called txtFilterAddress and
a field in the form's recordsource called Address.

Change txtFilterAddress and Address to suit your database.

Watch the line wrap, this bit should be all on one line.
strWhere = "([Address] Like """ & Me.txtFilterAddress & "*"")"



Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia


tstew said:
Hello,

I have an unbound text box on a form (single) and I want to search one
field
in my database for matching records. The field is house addresses like
"1234
Main St. #12" or "5678 Rancho Santa Margarita". There are around 10,000
records in the database. Some of the addresses will have the same begining
numbers, like "120 Mesa Ave." and "120 Starlight Dr.". I would like to use
the FindNext command, but the code below doesn't compile. I'm ok with
cycling
through the few matching records until I get the one I want, there aren't
that many with the same starting digits. Any ideas how to make the code
below
work?

BTW, I've tried going through Allen Browne's code and that is over my
head.

THANKS!
Mark
Private Sub CMDFindAdd_Click()
Dim strAddressRef As String
Dim strFindAdd As String

'Check txtFindAdd for Null value or Nill Entry first.

If IsNull(Me![txtfindadd]) Or (Me![txtfindadd]) = "" Then
MsgBox "Please enter a value!", vbOKOnly, "Invalid Search!"
Me![txtfindadd].SetFocus
Exit Sub
End If
'---------------------------------------------------------------

'Performs the search using value entered into txtfindadd
'and evaluates this against values in Address

DoCmd.ShowAllRecords
DoCmd.GoToControl ("Address")
DoCmd.FindRecord Me!txtSearch

ADDRESS.SetFocus
strAddressRef = ADDRESS.Text
txtfindadd.SetFocus
strFindAdd = txtfindadd.Text

'If matching record found sets focus in PicLocation

If strAddressRef = txtfindadd Then
ADDRESS.SetFocus

'If value not found sets focus back to txtSearch and shows msgbox
Else
MsgBox "Match Not Found For: " & strSearch & " - Please Try
Again.", _
, "Invalid Search"
txtfindadd.SetFocus
End If
End Sub
Click to expand...
 
Back
Top