Search Feature

I know this is already problematic...... but,
I am trying to tweak an existing DB developed by another user (here, fix
it). They used a Search Button code from "Graham Thorpe." Works great for
searching the subform for an existing record and giving the user three
different prompts when the record exists, does not exist or errors.

Where I need help, when the search returns "Record does not exist," and the
focus returns to the search box, the form/subform gets focus on the frist
record of the database when I want it to remain with no focus! Any advise
on how to only get focus when a record is found? I can post the code behind
the search button! Maybe start over with a different type of search? but
being fairly new to Access development I would need help with starting over.

Thanks, Rohn

Probably not necessary to start over. Post the code, please.
--
Dave Hargis, Microsoft Access MVP


"Rohn" wrote:

I know this is already problematic...... but,
I am trying to tweak an existing DB developed by another user (here, fix
it). They used a Search Button code from "Graham Thorpe." Works great for
searching the subform for an existing record and giving the user three
different prompts when the record exists, does not exist or errors.

Where I need help, when the search returns "Record does not exist," and the
focus returns to the search box, the form/subform gets focus on the frist
record of the database when I want it to remain with no focus! Any advise
on how to only get focus when a record is found? I can post the code behind
the search button! Maybe start over with a different type of search? but
being fairly new to Access development I would need help with starting over.

Thanks, Rohn

Thanks Dave,

Here is the "On Click" event:
'--------------------------------------------------------------
'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

All you need to do is remove this line of code:

Me![txtSearch].SetFocus

I would write it a little differently:

Private Sub cmdSearch_Click()
Dim Unit_IDRef As String
Dim strSearch As String

'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!"
Exit Sub
End If

--
Dave Hargis, Microsoft Access MVP


"Rohn" wrote:

Thanks Dave,

Here is the "On Click" event:
'--------------------------------------------------------------
'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

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.

"Klatuu" wrote in message
...
All you need to do is remove this line of code:

Me![txtSearch].SetFocus

I would write it a little differently:

Private Sub cmdSearch_Click()
Dim Unit_IDRef As String
Dim strSearch As String

'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!"
Exit Sub
End If

--
Dave Hargis, Microsoft Access MVP


"Rohn" wrote:

Thanks Dave,

Here is the "On Click" event:
'--------------------------------------------------------------
'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


"Rohn" wrote:

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.

"Klatuu" wrote in message
...
All you need to do is remove this line of code:

Me![txtSearch].SetFocus

I would write it a little differently:

Private Sub cmdSearch_Click()
Dim Unit_IDRef As String
Dim strSearch As String

'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!"
Exit Sub
End If

--
Dave Hargis, Microsoft Access MVP


"Rohn" wrote:

Thanks Dave,

Here is the "On Click" event:
'--------------------------------------------------------------
'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

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



"Klatuu" wrote in message
...
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

------------------------------------------------------------------------------
"Rohn" wrote:

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.

Thanks Dave, for spending some time on this issue, its a major pain for the
users and I just can't figure out how to modify it.

Rohn

"Klatuu" wrote in message
...
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

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" wrote:

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



"Klatuu" wrote in message
...
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

------------------------------------------------------------------------------
"Rohn" wrote:

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.

I removed the DoCmd.ShowAllRecords
and it gets an error on DoCmd.FindRecord Me!txtSearch

This maybe a dumb question but, I can't just say DoCmd.HideAllRecords or
Form.NewRecord or something?


Maybe I should try your method, so that I am not handcuffed to code I can
not make work right!

So, I create a Module place this code in it and save as Search! Already
done! Then what?

Again, thanks for your help on this, Rohn

"Klatuu" wrote in message
...
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" wrote:

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



"Klatuu" wrote in message
...
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

------------------------------------------------------------------------------
"Rohn" wrote:

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.