Search Feature

[Rohn]

txtSearch is the text box on the same form as the button with this VB
control.

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

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!

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


:

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


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

 

[Rohn]

Just found that I had a space between the Me and the period. So the error
went away but it still does not see the current records? It is trying to
work

The correct message box comes up when the txtSearch field is blank but
existing data is not being found?

thanks, Rohn

txtSearch is the text box on the same form as the button with this VB
control.

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

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!

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

 

[Klatuu]

That doesn't make any sense, then.
Try going into the VB Editor and commenting out that line. Then type it in
again and when you type Me. The intellisense dropdown should have txtSearch
in it. If it doesn't then there is a naming problem. If it does, then
select it and see what happens.
--
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!

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

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


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

 

[Rohn]

Intellisense has txtSearch in the list! I selected it and it doesn't work
as it is or with the added & """" at the end?

Any other ideas?

That doesn't make any sense, then.
Try going into the VB Editor and commenting out that line. Then type it
in
again and when you type Me. The intellisense dropdown should have
txtSearch
in it. If it doesn't then there is a naming problem. If it does, then
select it and see what happens.
--
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!

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


:

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


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

 

[Klatuu]

Save the mdb and do a compact and repair.
There is something wrong with the form.
--
Dave Hargis, Microsoft Access MVP

Intellisense has txtSearch in the list! I selected it and it doesn't work
as it is or with the added & """" at the end?

Any other ideas?

That doesn't make any sense, then.
Try going into the VB Editor and commenting out that line. Then type it
in
again and when you type Me. The intellisense dropdown should have
txtSearch
in it. If it doesn't then there is a naming problem. If it does, then
select it and see what happens.
--
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!

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


:

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


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

 

[Rohn]

I am hosed!!!!!! Compact and Repair did nothing!

An I stream line the Search to just do the search and eliminate the popup
boxes if that is adding some complications to the functionality!

Thanks for your help so far!
Rohn

Save the mdb and do a compact and repair.
There is something wrong with the form.
--
Dave Hargis, Microsoft Access MVP

Intellisense has txtSearch in the list! I selected it and it doesn't
work
as it is or with the added & """" at the end?

Any other ideas?

That doesn't make any sense, then.
Try going into the VB Editor and commenting out that line. Then type
it
in
again and when you type Me. The intellisense dropdown should have
txtSearch
in it. If it doesn't then there is a naming problem. If it does, then
select it and see what happens.
--
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!

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


:

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


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

 

[Klatuu]

I Think I got the number of quotes correct, but just as a text, change this
line:
.FindFirst "[Unit_ID] = """ & Me.txtSearch & """"

To:
.FindFirst "[Unit_ID] = '" & Me.txtSearch & "'"
Expanded for clarity:
.FindFirst "[Unit_ID] = ' " & Me.txtSearch & " ' "

It shouldn't make a difference, but just in case I got the quotes wrong.


--
Dave Hargis, Microsoft Access MVP

I am hosed!!!!!! Compact and Repair did nothing!

An I stream line the Search to just do the search and eliminate the popup
boxes if that is adding some complications to the functionality!

Thanks for your help so far!
Rohn

Save the mdb and do a compact and repair.
There is something wrong with the form.
--
Dave Hargis, Microsoft Access MVP

Intellisense has txtSearch in the list! I selected it and it doesn't
work
as it is or with the added & """" at the end?

Any other ideas?

That doesn't make any sense, then.
Try going into the VB Editor and commenting out that line. Then type
it
in
again and when you type Me. The intellisense dropdown should have
txtSearch
in it. If it doesn't then there is a naming problem. If it does, then
select it and see what happens.
--
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!

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


:

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


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

 

[Rohn]

Dave,
I removed the entire line and put in a stripped down version: .FindFirst
[Unit_ID] = Me.txtSearch with no luck but

my error is: Run-time error 3421: Data type conversion error. I think it
is trying to work!

I even tried the form.control wording: .FindFirst
"[(F)FRDF_CONTACT.Unit_ID] = ' " & Me.txtSearch & " ' "

but didn't have any luck.

I Think I got the number of quotes correct, but just as a text, change this
line:
.FindFirst "[Unit_ID] = """ & Me.txtSearch & """"

To:
.FindFirst "[Unit_ID] = '" & Me.txtSearch & "'"
Expanded for clarity:
.FindFirst "[Unit_ID] = ' " & Me.txtSearch & " ' "

It shouldn't make a difference, but just in case I got the quotes wrong.


--
Dave Hargis, Microsoft Access MVP

I am hosed!!!!!! Compact and Repair did nothing!

An I stream line the Search to just do the search and eliminate the popup
boxes if that is adding some complications to the functionality!

Thanks for your help so far!
Rohn

Save the mdb and do a compact and repair.
There is something wrong with the form.
--
Dave Hargis, Microsoft Access MVP


:

Intellisense has txtSearch in the list! I selected it and it doesn't
work
as it is or with the added & """" at the end?

Any other ideas?

That doesn't make any sense, then.
Try going into the VB Editor and commenting out that line. Then
type
it
in
again and when you type Me. The intellisense dropdown should have
txtSearch
in it. If it doesn't then there is a naming problem. If it does,
then
select it and see what happens.
--
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!

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


:

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


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

 

[Rohn]

Here is a question! The form is set to Data Entry on initial open will
that affect this type of code?

I Think I got the number of quotes correct, but just as a text, change this
line:
.FindFirst "[Unit_ID] = """ & Me.txtSearch & """"

To:
.FindFirst "[Unit_ID] = '" & Me.txtSearch & "'"
Expanded for clarity:
.FindFirst "[Unit_ID] = ' " & Me.txtSearch & " ' "

It shouldn't make a difference, but just in case I got the quotes wrong.


--
Dave Hargis, Microsoft Access MVP

I am hosed!!!!!! Compact and Repair did nothing!

An I stream line the Search to just do the search and eliminate the popup
boxes if that is adding some complications to the functionality!

Thanks for your help so far!
Rohn

Save the mdb and do a compact and repair.
There is something wrong with the form.
--
Dave Hargis, Microsoft Access MVP


:

Intellisense has txtSearch in the list! I selected it and it doesn't
work
as it is or with the added & """" at the end?

Any other ideas?

That doesn't make any sense, then.
Try going into the VB Editor and commenting out that line. Then
type
it
in
again and when you type Me. The intellisense dropdown should have
txtSearch
in it. If it doesn't then there is a naming problem. If it does,
then
select it and see what happens.
--
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!

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


:

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


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

 

[Rohn]

I set both the Main form and Sub Form to No DataEntry to allow all records
to be visible via the form navigation and ran the new code, the popup
actually says "record found" but it does not change the current record set
to that found record? This is very close, I just can't tell what the
problem is.

I really appreicate all the help you have given on this one. I have learned
alot from you about how these code actually work.

Thanks again for the assistance.
Rohn

Here is a question! The form is set to Data Entry on initial open will
that affect this type of code?

I Think I got the number of quotes correct, but just as a text, change
this
line:
.FindFirst "[Unit_ID] = """ & Me.txtSearch & """"

To:
.FindFirst "[Unit_ID] = '" & Me.txtSearch & "'"
Expanded for clarity:
.FindFirst "[Unit_ID] = ' " & Me.txtSearch & " ' "

It shouldn't make a difference, but just in case I got the quotes wrong.


--
Dave Hargis, Microsoft Access MVP

I am hosed!!!!!! Compact and Repair did nothing!

An I stream line the Search to just do the search and eliminate the
popup
boxes if that is adding some complications to the functionality!

Thanks for your help so far!
Rohn

Save the mdb and do a compact and repair.
There is something wrong with the form.
--
Dave Hargis, Microsoft Access MVP


:

Intellisense has txtSearch in the list! I selected it and it
doesn't
work
as it is or with the added & """" at the end?

Any other ideas?

That doesn't make any sense, then.
Try going into the VB Editor and commenting out that line. Then
type
it
in
again and when you type Me. The intellisense dropdown should have
txtSearch
in it. If it doesn't then there is a naming problem. If it does,
then
select it and see what happens.
--
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!

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


:

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


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