Search Feature

No, you don't create a module. You just replace the code you have in the sub
with my code. Be aware it was written in this message box, so it may have
errors we will have to work out.
--
Dave Hargis, Microsoft Access MVP


"Rohn" wrote:

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.

I replaced the code and I get a Compile Error on:

If Nz(Me.txtSearch), vbNullString = vbNullString Then

It is the only thing in the VB window that is red.


"Klatuu" wrote in message
news
No, you don't create a module. You just replace the code you have in the
sub
with my code. Be aware it was written in this message box, so it may have
errors we will have to work out.
--
Dave Hargis, Microsoft Access MVP


"Rohn" wrote:

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

Told you this might happen g
Should be:
If Nz(Me.txtSearch, vbNullString) = vbNullString Then
--
Dave Hargis, Microsoft Access MVP


"Rohn" wrote:

I replaced the code and I get a Compile Error on:

If Nz(Me.txtSearch), vbNullString = vbNullString Then

It is the only thing in the VB window that is red.


"Klatuu" wrote in message
news
No, you don't create a module. You just replace the code you have in the
sub
with my code. Be aware it was written in this message box, so it may have
errors we will have to work out.
--
Dave Hargis, Microsoft Access MVP


"Rohn" wrote:

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

Made a few tweaks; had errors with Criterion, end with, missing end if. Now
have an error: Run time error - Object doesn't support this property or
method, on:

..FindFirst "[Unit_ID] = " & .txtSearch

BTW, the Unit_ID field has a TEXT field property and the data is always
Alphanumeric.
I think it is getting closer to working!!! This is awesome!

"Klatuu" wrote in message
...
Told you this might happen g
Should be:
If Nz(Me.txtSearch, vbNullString) = vbNullString Then
--
Dave Hargis, Microsoft Access MVP


"Rohn" wrote:

I replaced the code and I get a Compile Error on:

If Nz(Me.txtSearch), vbNullString = vbNullString Then

It is the only thing in the VB window that is red.


"Klatuu" wrote in message
news
No, you don't create a module. You just replace the code you have in
the
sub
with my code. Be aware it was written in this message box, so it may
have
errors we will have to work out.
--
Dave Hargis, Microsoft Access MVP


"Rohn" wrote:

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

Did some tweaks when some minor errors came up, like: End If missing, End
With, quote on Criterion but then had a Run-time error '438': Object doesn't
support this property or method, on:
.FindFirst "[Unit_ID] = " & .txtSearch & ""

BTW, the UNIT_ID field is a text property and populated with alphanumeric
data.

Thanks, Rohn


"Klatuu" wrote in message
...
Told you this might happen g
Should be:
If Nz(Me.txtSearch, vbNullString) = vbNullString Then
--
Dave Hargis, Microsoft Access MVP


"Rohn" wrote:

I replaced the code and I get a Compile Error on:

If Nz(Me.txtSearch), vbNullString = vbNullString Then

It is the only thing in the VB window that is red.


"Klatuu" wrote in message
news
No, you don't create a module. You just replace the code you have in
the
sub
with my code. Be aware it was written in this message box, so it may
have
errors we will have to work out.
--
Dave Hargis, Microsoft Access MVP


"Rohn" wrote:

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

Another whoopsy, sorry, but it is hard to get it right without VBE correcting
me as I go. This line:
.FindFirst "[Unit_ID] = " & .txtSearch
Should be
.FindFirst "[Unit_ID] = " & Me.txtSearch
--
Dave Hargis, Microsoft Access MVP


"Rohn" wrote:

Made a few tweaks; had errors with Criterion, end with, missing end if. Now
have an error: Run time error - Object doesn't support this property or
method, on:

..FindFirst "[Unit_ID] = " & .txtSearch

BTW, the Unit_ID field has a TEXT field property and the data is always
Alphanumeric.
I think it is getting closer to working!!! This is awesome!

"Klatuu" wrote in message
...
Told you this might happen g
Should be:
If Nz(Me.txtSearch, vbNullString) = vbNullString Then
--
Dave Hargis, Microsoft Access MVP


"Rohn" wrote:

I replaced the code and I get a Compile Error on:

If Nz(Me.txtSearch), vbNullString = vbNullString Then

It is the only thing in the VB window that is red.


"Klatuu" wrote in message
news No, you don't create a module. You just replace the code you have in
the
sub
with my code. Be aware it was written in this message box, so it may
have
errors we will have to work out.
--
Dave Hargis, Microsoft Access MVP


"Rohn" wrote:

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

great, I added the revised wording. No errors but the search button is not
finding existing values! Here is the revised code.

'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
Else
With Me.RecordsetClone
'Use this if the table field Unit_ID is numberic
.FindFirst "[Unit_ID] = " & Me.txtSearch


'Use this if the table field Unit_ID is text
.FindFirst "[Unit_ID] = """ & Me.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 If
End Sub


"Klatuu" wrote in message
news
Another whoopsy, sorry, but it is hard to get it right without VBE
correcting
me as I go. This line:
.FindFirst "[Unit_ID] = " & .txtSearch
Should be
.FindFirst "[Unit_ID] = " & Me.txtSearch
--
Dave Hargis, Microsoft Access MVP

You are using both versions of what should be one line.

'Use this if the table field Unit_ID is numberic
.FindFirst "[Unit_ID] = " & Me.txtSearch


'Use this if the table field Unit_ID is text
.FindFirst "[Unit_ID] = """ & Me.txtSearch & """"

As the comments say, if Unit_ID ia a numeric field in the form's record set,
use the first version and delete the second version. If it is a text field,
delete the first version and use the second version. Note the text version
encloses the search value in quotes which is correct syntax when seaching for
text. You do not use any delimiters when seaching for a numeric value. And,
FYI, if you are searching a date field, the delimiters are the # sign:
.FindFirst "[Unit_ID] = #" & Me.txtSearch & "#"

--
Dave Hargis, Microsoft Access MVP


"Rohn" wrote:

great, I added the revised wording. No errors but the search button is not
finding existing values! Here is the revised code.

'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
Else
With Me.RecordsetClone
'Use this if the table field Unit_ID is numberic
.FindFirst "[Unit_ID] = " & Me.txtSearch


'Use this if the table field Unit_ID is text
.FindFirst "[Unit_ID] = """ & Me.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 If
End Sub


"Klatuu" wrote in message
news
Another whoopsy, sorry, but it is hard to get it right without VBE
correcting
me as I go. This line:
.FindFirst "[Unit_ID] = " & .txtSearch
Should be
.FindFirst "[Unit_ID] = " & Me.txtSearch
--
Dave Hargis, Microsoft Access MVP

Thanks for the explaination of the two fields. I took out the first field
since we are searching for Alphanumeric data (like Z004 or Y142K).

The new error is a Compile Error: Invalid or Unqualified Reference on:
..txtSearch in this peice of the code.

'Use this if the table field Unit_ID is text
.FindFirst "[Unit_ID] = """ & Me.txtSearch & """"

Thanks for sticking with this issue!

"Klatuu" wrote in message
...
You are using both versions of what should be one line.

'Use this if the table field Unit_ID is numberic
.FindFirst "[Unit_ID] = " & Me.txtSearch


'Use this if the table field Unit_ID is text
.FindFirst "[Unit_ID] = """ & Me.txtSearch & """"

As the comments say, if Unit_ID ia a numeric field in the form's record
set,
use the first version and delete the second version. If it is a text
field,
delete the first version and use the second version. Note the text
version
encloses the search value in quotes which is correct syntax when seaching
for
text. You do not use any delimiters when seaching for a numeric value.
And,
FYI, if you are searching a date field, the delimiters are the # sign:
.FindFirst "[Unit_ID] = #" & Me.txtSearch & "#"

--
Dave Hargis, Microsoft Access MVP


"Rohn" wrote:

great, I added the revised wording. No errors but the search button is
not
finding existing values! Here is the revised code.

'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
Else
With Me.RecordsetClone
'Use this if the table field Unit_ID is numberic
.FindFirst "[Unit_ID] = " & Me.txtSearch


'Use this if the table field Unit_ID is text
.FindFirst "[Unit_ID] = """ & Me.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 If
End Sub


"Klatuu" wrote in message
news
Another whoopsy, sorry, but it is hard to get it right without VBE
correcting
me as I go. This line:
.FindFirst "[Unit_ID] = " & .txtSearch
Should be
.FindFirst "[Unit_ID] = " & Me.txtSearch
--
Dave Hargis, Microsoft Access MVP

Is txtSearch the name of a control on the form the code is in or it is on a
subform or different form?

--
Dave Hargis, Microsoft Access MVP


"Rohn" wrote:

Thanks for the explaination of the two fields. I took out the first field
since we are searching for Alphanumeric data (like Z004 or Y142K).

The new error is a Compile Error: Invalid or Unqualified Reference on:
..txtSearch in this peice of the code.

'Use this if the table field Unit_ID is text
.FindFirst "[Unit_ID] = """ & Me.txtSearch & """"

Thanks for sticking with this issue!

"Klatuu" wrote in message
...
You are using both versions of what should be one line.

'Use this if the table field Unit_ID is numberic
.FindFirst "[Unit_ID] = " & Me.txtSearch


'Use this if the table field Unit_ID is text
.FindFirst "[Unit_ID] = """ & Me.txtSearch & """"

As the comments say, if Unit_ID ia a numeric field in the form's record
set,
use the first version and delete the second version. If it is a text
field,
delete the first version and use the second version. Note the text
version
encloses the search value in quotes which is correct syntax when seaching
for
text. You do not use any delimiters when seaching for a numeric value.
And,
FYI, if you are searching a date field, the delimiters are the # sign:
.FindFirst "[Unit_ID] = #" & Me.txtSearch & "#"

--
Dave Hargis, Microsoft Access MVP


"Rohn" wrote:

great, I added the revised wording. No errors but the search button is
not
finding existing values! Here is the revised code.

'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
Else
With Me.RecordsetClone
'Use this if the table field Unit_ID is numberic
.FindFirst "[Unit_ID] = " & Me.txtSearch


'Use this if the table field Unit_ID is text
.FindFirst "[Unit_ID] = """ & Me.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 If
End Sub


"Klatuu" wrote in message
news Another whoopsy, sorry, but it is hard to get it right without VBE
correcting
me as I go. This line:
.FindFirst "[Unit_ID] = " & .txtSearch
Should be
.FindFirst "[Unit_ID] = " & Me.txtSearch
--
Dave Hargis, Microsoft Access MVP