The reason it is going to the first record in the recordset when no match
is
found is because of this line:
DoCmd.ShowAllRecords
The ShowAllRecords method does a couple of things, some of which you don't
really want to do. It removes any filtering on the form's recordsetand is
requeies the recordset. A requery always takes you back to the first
record
of a form recordset.
So, the question is is there any filtering on the form or the form's
recordset? If not, maybe just removing that line will resovle the issue.
If
that doesn't do it, I would suggest rewriting the sub using more specific
code. The code, as written, is one step better than macros, but is still
a
long shot from precisely written code.
If you need help rewriting how the search is done, I would need to know
the
name of the field in the form's recordset you want to search on. But, as
a
general way of doing a search, this may help.
This assumes the code is in the form module of the form you are searching:
'Performs the search using value entered into txtSearch
'and evaluates this against values in UNIT_ID field
Private Sub cmdSearch_Click()
'Check txtSearch for Null value or Nill Entry first.
If Nz(Me.txtSearch), vbNullString = vbNullString Then
MsgBox "Please enter a value!", vbOKOnly, "Invalid Search
Criterion!"
Me.txtSearch.SetFocus
End If
Else
With Me.RecordsetClone
'Use this if the table field Unit_ID is numberic
.FindFirst "[Unit_ID] = " & .txtSearch
'Use this if the table field Unit_ID is text
.FindFirst "[Unit_ID] = """ & .txtSearch & """"
If .NoMatch Then
MsgBox "Match Not Found For: " & strSearch & " - Please Try
Again.", _
, "Invalid Search Criterion!"
txtSearch.SetFocus
Else
MsgBox "Match Found For: " & strSearch, ,
"Congratulations!"
Me.Unit_ID.SetFocus
Me.txtSearch = vbNullString
End If
End With
End With
End Sub
--
Dave Hargis, Microsoft Access MVP
Rohn said:
Our Customer Service people SEARCH the database (main form / parent
record)
to make sure there is not an existing complaint on the particular
CUSTOMER
ORDER, if there is, they record the new issue in the subform details
which
make a new child record. If the SEARCH turns up, no record found, they
know
to start a new / parent record for the CUSTOMER ORDER that has no
previous
complaints.
But after running the SEARCH and not finding a record, the form/subform
is
now displaying the first record of the database and not leaving the form
set
to DataEntry so the user can easily start a new record!
The current code:
'--------------------------------------------------------------
'Seach field concept by Graham Thorpe
'--------------------------------------------------------------
Private Sub cmdSearch_Click()
Dim Unit_IDRef As String
Dim strSearch As String
'Check txtSearch for Null value or Nill Entry first.
If IsNull(Me![txtSearch]) Or (Me![txtSearch]) = "" Then
MsgBox "Please enter a value!", vbOKOnly, "Invalid Search
Criterion!"
Me![txtSearch].SetFocus
Exit Sub
End If
'---------------------------------------------------------------
'Performs the search using value entered into txtSearch
'and evaluates this against values in UNIT_ID field
DoCmd.ShowAllRecords
DoCmd.GoToControl ("Unit_ID")
DoCmd.FindRecord Me!txtSearch
Unit_ID.SetFocus
Unit_IDRef = Unit_ID.Text
txtSearch.SetFocus
strSearch = txtSearch.Text
'If matching record found sets focus in UNIT_ID and shows msgbox
'and clears search control
If Unit_IDRef = strSearch Then
MsgBox "Match Found For: " & strSearch, , "Congratulations!"
Unit_ID.SetFocus
txtSearch = ""
'If value not found sets focus back to txtSearch and shows msgbox
Else
MsgBox "Match Not Found For: " & strSearch & " - Please Try
Again.", _
, "Invalid Search Criterion!"
txtSearch.SetFocus
End If
End Sub
Where in my code did you get a compile error. It was, of course, air
code
written in the message editor, but from practical experience.
I don't understand what you mean about continuing the search. In most
cases, you either find a record or you don't. Perhaps if you could
describe
what you are trying to accomplish and post all the existing code, we
can
offer a how to.
--
Dave Hargis, Microsoft Access MVP
------------------------------------------------------------------------------
:
Dave,
I tried both approaches without success. When I removed
"Me![txtSearch].SetFocus" from the current code, no noticable
difference?
When I replaced the code it gives me a VB Compile Error: Syntax Error
Did I do something wrong?
more information:
Previously, I had been trying to modify the last section of this Code
because it seem like everything works OK until, you do not find an
existing
record and you want to continue to search. But the form & subform get
populated with the first record in the db while attempting your second
search in the search field.