VBA search coding problem

[Guest]

Hi, I'm trying to develop a search function for my form, and found a similar
code for the website. After changing it, I've almost made the search work but
the problem I have right now is that the pop up function does not
differentiate between a correct ID and an incorrect ID. I think it has to do
with my If.. Else code. It will not do the 2 tasks in sequences. Below is my
code.

rivate Sub Search_Click()
Dim strPatient_ID As String
Dim strSearch As String

'Check txtSearch for Null value or Nill Entry first.

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

'Performs the search using value entered into txtSearch
'and evaluates this against values in strStudentID

DoCmd.ShowAllRecords
DoCmd.GoToControl ("Patient ID")
DoCmd.FindRecord Me!IDsearch

Patient_ID.SetFocus
strPatientID = Patient_ID.Text
IDsearch.SetFocus
strSearch = IDsearch.Text

'If matching record found sets focus in strPatientID
'and clears search control

If strPatient_ID <> strSearch Then
MsgBox "Match Found: " & strSearch & "" _
, , "Congratulations!"
IDsearch.SetFocus
IDsearch = ""
'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!"
IDsearch.SetFocus
End If
End Sub

The problem starts at the Comment "If matching record found sets focus in
strPatientID 'and clears search control". The If Else function doesnt work.
In this set up, it'll going do the "If" task regardless if I type in an
existing ID or not.
Can anyone tell me what I wrote wrong? Thank you very much

 

[strive4peace]

'~~~~~~~~~~~~~~~
If Len(Trim(nz(Me.IDsearch,""))) = 0 Then
MsgBox "Please enter a value to search for", _
vbOKOnly, _
"Invalid Search Criterion!"
Me.IDsearch.SetFocus
Exit Sub
end if

'save current record if changes were made
If me.dirty then me.dirty = false

Me.RecordsetClone.FindFirst _
"[Patient ID]= '" & Me.IDsearch & "'"

If Not Me.RecordsetClone.NoMatch Then
Me.Bookmark = Me.RecordsetClone.Bookmark
End If

'~~~~~~~~~~~~~~~

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

 

[Guest]

Hi,
Thanks for the new code. I replaced your code with mine but it still doesn't
work.
Can you actually explain to me what your code means? Thanks

'~~~~~~~~~~~~~~~
If Len(Trim(nz(Me.IDsearch,""))) = 0 Then
MsgBox "Please enter a value to search for", _
vbOKOnly, _
"Invalid Search Criterion!"
Me.IDsearch.SetFocus
Exit Sub
end if

'save current record if changes were made
If me.dirty then me.dirty = false

Me.RecordsetClone.FindFirst _
"[Patient ID]= '" & Me.IDsearch & "'"

If Not Me.RecordsetClone.NoMatch Then
Me.Bookmark = Me.RecordsetClone.Bookmark
End If

'~~~~~~~~~~~~~~~

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

Hi, I'm trying to develop a search function for my form, and found a similar
code for the website. After changing it, I've almost made the search work but
the problem I have right now is that the pop up function does not
differentiate between a correct ID and an incorrect ID. I think it has to do
with my If.. Else code. It will not do the 2 tasks in sequences. Below is my
code.

rivate Sub Search_Click()
Dim strPatient_ID As String
Dim strSearch As String

'Check txtSearch for Null value or Nill Entry first.

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

'Performs the search using value entered into txtSearch
'and evaluates this against values in strStudentID

DoCmd.ShowAllRecords
DoCmd.GoToControl ("Patient ID")
DoCmd.FindRecord Me!IDsearch

Patient_ID.SetFocus
strPatientID = Patient_ID.Text
IDsearch.SetFocus
strSearch = IDsearch.Text

'If matching record found sets focus in strPatientID
'and clears search control

If strPatient_ID <> strSearch Then
MsgBox "Match Found: " & strSearch & "" _
, , "Congratulations!"
IDsearch.SetFocus
IDsearch = ""
'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!"
IDsearch.SetFocus
End If
End Sub

The problem starts at the Comment "If matching record found sets focus in
strPatientID 'and clears search control". The If Else function doesnt work.
In this set up, it'll going do the "If" task regardless if I type in an
existing ID or not.
Can anyone tell me what I wrote wrong? Thank you very much

 

[strive4peace]

From your code, I gathered that IDsearch is text

Len(Trim(nz(Me.IDsearch,""))) = 0

NZ is convert null to 0 (numbers), an empty string "" for text, or
whatever is specified in the optional second argument

Trim removes leading and training spaces

Len gets the length of a text string

usually, I just use if IsNull(me.controlname)

~~~~

If me.dirty then me.dirty = false

if changes have been made to the record, it's "Dirty" property will be
true -- telling the Dirty property to be false is one way to save a record

if is a good idea to save the current record before moving the record
pointer

~~~~~~~~~~~~
Me.RecordsetClone.FindFirst _
"[Patient ID]= '" & Me.IDsearch & "'"

using a copy (clone) of the recordset that the form is using, find the
record where the field named [Patient ID] is equal to what is chosen in
the IDsearch control. If the [Patient ID] is text, it needs to be
delimited with quotes

If you do not have a field of this name, make the appropriate change

~~~~~~~~~~~~

If Not Me.RecordsetClone.NoMatch Then
Me.Bookmark = Me.RecordsetClone.Bookmark
End If

if the search was successful (Not NoMatch), use the Bookmark property to
find the same record on the form


Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

Hi,
Thanks for the new code. I replaced your code with mine but it still doesn't
work.
Can you actually explain to me what your code means? Thanks

'~~~~~~~~~~~~~~~
If Len(Trim(nz(Me.IDsearch,""))) = 0 Then
MsgBox "Please enter a value to search for", _
vbOKOnly, _
"Invalid Search Criterion!"
Me.IDsearch.SetFocus
Exit Sub
end if

'save current record if changes were made
If me.dirty then me.dirty = false

Me.RecordsetClone.FindFirst _
"[Patient ID]= '" & Me.IDsearch & "'"

If Not Me.RecordsetClone.NoMatch Then
Me.Bookmark = Me.RecordsetClone.Bookmark
End If

'~~~~~~~~~~~~~~~

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

Hi, I'm trying to develop a search function for my form, and found a similar
code for the website. After changing it, I've almost made the search work but
the problem I have right now is that the pop up function does not
differentiate between a correct ID and an incorrect ID. I think it has to do
with my If.. Else code. It will not do the 2 tasks in sequences. Below is my
code.

rivate Sub Search_Click()
Dim strPatient_ID As String
Dim strSearch As String

'Check txtSearch for Null value or Nill Entry first.

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

'Performs the search using value entered into txtSearch
'and evaluates this against values in strStudentID

DoCmd.ShowAllRecords
DoCmd.GoToControl ("Patient ID")
DoCmd.FindRecord Me!IDsearch

Patient_ID.SetFocus
strPatientID = Patient_ID.Text
IDsearch.SetFocus
strSearch = IDsearch.Text

'If matching record found sets focus in strPatientID
'and clears search control

If strPatient_ID <> strSearch Then
MsgBox "Match Found: " & strSearch & "" _
, , "Congratulations!"
IDsearch.SetFocus
IDsearch = ""
'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!"
IDsearch.SetFocus
End If
End Sub

The problem starts at the Comment "If matching record found sets focus in
strPatientID 'and clears search control". The If Else function doesnt work.
In this set up, it'll going do the "If" task regardless if I type in an
existing ID or not.
Can anyone tell me what I wrote wrong? Thank you very much

 

[Allen Browne]

A bound form has a RecordsetClone - another set of pointers to the same
records. You can search for the record in the clone set, and test NoMatch to
see whether it was found. If it's not found, you can notify the user without
having moved away from the current record. If it is found, you can set the
form's Bookmark to the bookmark of the found record in the clone set.

Crystal's code takes care of all of that:
- checking the user entered something to search for;
- explicitly saving the record if edits are in progress (since it must be
saved before you can go to another records);
- finding the first match in the RecordsetClone of the form;
- testing if a match was found;
- moving to that record if it was found.

I recommend you take her approach.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hi,
Thanks for the new code. I replaced your code with mine but it still
doesn't
work.
Can you actually explain to me what your code means? Thanks

'~~~~~~~~~~~~~~~
If Len(Trim(nz(Me.IDsearch,""))) = 0 Then
MsgBox "Please enter a value to search for", _
vbOKOnly, _
"Invalid Search Criterion!"
Me.IDsearch.SetFocus
Exit Sub
end if

'save current record if changes were made
If me.dirty then me.dirty = false

Me.RecordsetClone.FindFirst _
"[Patient ID]= '" & Me.IDsearch & "'"

If Not Me.RecordsetClone.NoMatch Then
Me.Bookmark = Me.RecordsetClone.Bookmark
End If

'~~~~~~~~~~~~~~~

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

Hi, I'm trying to develop a search function for my form, and found a
similar
code for the website. After changing it, I've almost made the search
work but
the problem I have right now is that the pop up function does not
differentiate between a correct ID and an incorrect ID. I think it has
to do
with my If.. Else code. It will not do the 2 tasks in sequences. Below
is my
code.

rivate Sub Search_Click()
Dim strPatient_ID As String
Dim strSearch As String

'Check txtSearch for Null value or Nill Entry first.

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

'Performs the search using value entered into txtSearch
'and evaluates this against values in strStudentID

DoCmd.ShowAllRecords
DoCmd.GoToControl ("Patient ID")
DoCmd.FindRecord Me!IDsearch

Patient_ID.SetFocus
strPatientID = Patient_ID.Text
IDsearch.SetFocus
strSearch = IDsearch.Text

'If matching record found sets focus in strPatientID
'and clears search control

If strPatient_ID <> strSearch Then
MsgBox "Match Found: " & strSearch & "" _
, , "Congratulations!"
IDsearch.SetFocus
IDsearch = ""
'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!"
IDsearch.SetFocus
End If
End Sub

The problem starts at the Comment "If matching record found sets focus
in
strPatientID 'and clears search control". The If Else function doesnt
work.
In this set up, it'll going do the "If" task regardless if I type in an
existing ID or not.
Can anyone tell me what I wrote wrong? Thank you very much

 

[Guest]

thank you for your response but the question is that the codes right now does
not tell the data entry person that the ID does not exist.
Do you mind explain what problem the If Else statement that I had that made
it that it only runs parts of the statement?
I tried that code in its original database and it works... so really want to
know what I did wrong. thanks

A bound form has a RecordsetClone - another set of pointers to the same
records. You can search for the record in the clone set, and test NoMatch to
see whether it was found. If it's not found, you can notify the user without
having moved away from the current record. If it is found, you can set the
form's Bookmark to the bookmark of the found record in the clone set.

Crystal's code takes care of all of that:
- checking the user entered something to search for;
- explicitly saving the record if edits are in progress (since it must be
saved before you can go to another records);
- finding the first match in the RecordsetClone of the form;
- testing if a match was found;
- moving to that record if it was found.

I recommend you take her approach.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hi,
Thanks for the new code. I replaced your code with mine but it still
doesn't
work.
Can you actually explain to me what your code means? Thanks

'~~~~~~~~~~~~~~~
If Len(Trim(nz(Me.IDsearch,""))) = 0 Then
MsgBox "Please enter a value to search for", _
vbOKOnly, _
"Invalid Search Criterion!"
Me.IDsearch.SetFocus
Exit Sub
end if

'save current record if changes were made
If me.dirty then me.dirty = false

Me.RecordsetClone.FindFirst _
"[Patient ID]= '" & Me.IDsearch & "'"

If Not Me.RecordsetClone.NoMatch Then
Me.Bookmark = Me.RecordsetClone.Bookmark
End If

'~~~~~~~~~~~~~~~

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*



esca007 wrote:
Hi, I'm trying to develop a search function for my form, and found a
similar
code for the website. After changing it, I've almost made the search
work but
the problem I have right now is that the pop up function does not
differentiate between a correct ID and an incorrect ID. I think it has
to do
with my If.. Else code. It will not do the 2 tasks in sequences. Below
is my
code.

rivate Sub Search_Click()
Dim strPatient_ID As String
Dim strSearch As String

'Check txtSearch for Null value or Nill Entry first.

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

'Performs the search using value entered into txtSearch
'and evaluates this against values in strStudentID

DoCmd.ShowAllRecords
DoCmd.GoToControl ("Patient ID")
DoCmd.FindRecord Me!IDsearch

Patient_ID.SetFocus
strPatientID = Patient_ID.Text
IDsearch.SetFocus
strSearch = IDsearch.Text

'If matching record found sets focus in strPatientID
'and clears search control

If strPatient_ID <> strSearch Then
MsgBox "Match Found: " & strSearch & "" _
, , "Congratulations!"
IDsearch.SetFocus
IDsearch = ""
'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!"
IDsearch.SetFocus
End If
End Sub

The problem starts at the Comment "If matching record found sets focus
in
strPatientID 'and clears search control". The If Else function doesnt
work.
In this set up, it'll going do the "If" task regardless if I type in an
existing ID or not.
Can anyone tell me what I wrote wrong? Thank you very much

 

[Guest]

thank you for your response but the question is that the codes right now does
not tell the data entry person that the ID does not exist.
Do you mind explain what problem the If Else statement that I had that made
it that it only runs parts of the statement?
I tried If else code in its original database source and it works... so
really want to know what I did wrong. thanks

From your code, I gathered that IDsearch is text

Len(Trim(nz(Me.IDsearch,""))) = 0

NZ is convert null to 0 (numbers), an empty string "" for text, or
whatever is specified in the optional second argument

Trim removes leading and training spaces

Len gets the length of a text string

usually, I just use if IsNull(me.controlname)

~~~~

If me.dirty then me.dirty = false

if changes have been made to the record, it's "Dirty" property will be
true -- telling the Dirty property to be false is one way to save a record

if is a good idea to save the current record before moving the record
pointer

~~~~~~~~~~~~
Me.RecordsetClone.FindFirst _
"[Patient ID]= '" & Me.IDsearch & "'"

using a copy (clone) of the recordset that the form is using, find the
record where the field named [Patient ID] is equal to what is chosen in
the IDsearch control. If the [Patient ID] is text, it needs to be
delimited with quotes

If you do not have a field of this name, make the appropriate change

~~~~~~~~~~~~

If Not Me.RecordsetClone.NoMatch Then
Me.Bookmark = Me.RecordsetClone.Bookmark
End If

if the search was successful (Not NoMatch), use the Bookmark property to
find the same record on the form


Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

Hi,
Thanks for the new code. I replaced your code with mine but it still doesn't
work.
Can you actually explain to me what your code means? Thanks

'~~~~~~~~~~~~~~~
If Len(Trim(nz(Me.IDsearch,""))) = 0 Then
MsgBox "Please enter a value to search for", _
vbOKOnly, _
"Invalid Search Criterion!"
Me.IDsearch.SetFocus
Exit Sub
end if

'save current record if changes were made
If me.dirty then me.dirty = false

Me.RecordsetClone.FindFirst _
"[Patient ID]= '" & Me.IDsearch & "'"

If Not Me.RecordsetClone.NoMatch Then
Me.Bookmark = Me.RecordsetClone.Bookmark
End If

'~~~~~~~~~~~~~~~

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*



esca007 wrote:
Hi, I'm trying to develop a search function for my form, and found a similar
code for the website. After changing it, I've almost made the search work but
the problem I have right now is that the pop up function does not
differentiate between a correct ID and an incorrect ID. I think it has to do
with my If.. Else code. It will not do the 2 tasks in sequences. Below is my
code.

rivate Sub Search_Click()
Dim strPatient_ID As String
Dim strSearch As String

'Check txtSearch for Null value or Nill Entry first.

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

'Performs the search using value entered into txtSearch
'and evaluates this against values in strStudentID

DoCmd.ShowAllRecords
DoCmd.GoToControl ("Patient ID")
DoCmd.FindRecord Me!IDsearch

Patient_ID.SetFocus
strPatientID = Patient_ID.Text
IDsearch.SetFocus
strSearch = IDsearch.Text

'If matching record found sets focus in strPatientID
'and clears search control

If strPatient_ID <> strSearch Then
MsgBox "Match Found: " & strSearch & "" _
, , "Congratulations!"
IDsearch.SetFocus
IDsearch = ""
'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!"
IDsearch.SetFocus
End If
End Sub

The problem starts at the Comment "If matching record found sets focus in
strPatientID 'and clears search control". The If Else function doesnt work.
In this set up, it'll going do the "If" task regardless if I type in an
existing ID or not.
Can anyone tell me what I wrote wrong? Thank you very much

 

[strive4peace]

Use a combobox to pick the code and then they can only choose one that
DOES exists

a control to be used for searching will be unbound (no ControlSource),
so whatever record is on the screen will not be changed. I also like to
change the background color of search combos so that the user knows this
is not part of the data

"Do you mind explain what problem the If Else statement that I had that
made it that it only runs parts of the statement? "

you had:

If IsNull(Me![IDsearch]) Or (Me![IDsearch]) = "" then

if IDsearch is nothing (null), you cannot compare that to a value...
that is why I converted what you had to

Len(Trim(nz(Me.IDsearch,""))) = 0

Now, it appears that the real problem is that the user may enter an
invalid patient id... using a combobox will solve this

Anyway, IMHO, you should not be using a text field to relate data in
your tables. Conventionally, using ID in the fieldname implies that
fieldname was created using an autonumber field in the main table and is
defined to be a long integer in related tables. To avoid confusion, if
you have a "Patient ID" that is their hospital number, consider using a
different fieldname for that like "PatientNumber" or "PatientNum" (I
like to keep names short)

Combobox Example

* Under no circumstances should you store names in more than one place.
For instance, if you have a People table, define a PID (or PeopleID)
autonumber field. Then, in other tables, when you want to identify a
person, you can use the key field. One way to do this…

Create an autonumber field in the People table -->

PID, autonumber

then, in the other tables...
PID, long, DefaultValue = Null

Then, when you want to put data in (which should be done from a form),
you can set it up to pick names from a list but store the PID.

create a combobox control

Name --> PID

ControlSource --> PID

RowSource -->
SELECT
PID,
LastName & ", " & Firstname AS Fullname,
BirthDate
FROM People
ORDER BY LastName, Firstname

BoundColumn --> 1

ColumnCount --> 3

columnWidths --> 0;2;1
(etc for however many columns you have -- the ID column will be hidden)

ListWidth --> 3
(should add up to the sum of the column widths)

if you have a listbox, sometimes you need to make the width .01 more
than the sum of the columns to prevent the horizontal scrollbar from
appearing.

PID will be stored in the form RecordSource while showing you names from
another table... a MUCH better and more reliable method.

If you want to show other information from your combobox in other
controls, you can use calculated fields. For instance

textbox:
Name --> BirthDate
ControlSource --> = PID.column(2)

The reason that column 2 is referenced instead of column 3 is that
column indexes start with 0, not 1, in Access.



Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

ps... thanks, Allen

thank you for your response but the question is that the codes right now does
not tell the data entry person that the ID does not exist.
Do you mind explain what problem the If Else statement that I had that made
it that it only runs parts of the statement?
I tried that code in its original database and it works... so really want to
know what I did wrong. thanks

A bound form has a RecordsetClone - another set of pointers to the same
records. You can search for the record in the clone set, and test NoMatch to
see whether it was found. If it's not found, you can notify the user without
having moved away from the current record. If it is found, you can set the
form's Bookmark to the bookmark of the found record in the clone set.

Crystal's code takes care of all of that:
- checking the user entered something to search for;
- explicitly saving the record if edits are in progress (since it must be
saved before you can go to another records);
- finding the first match in the RecordsetClone of the form;
- testing if a match was found;
- moving to that record if it was found.

I recommend you take her approach.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hi,
Thanks for the new code. I replaced your code with mine but it still
doesn't
work.
Can you actually explain to me what your code means? Thanks

:

'~~~~~~~~~~~~~~~
If Len(Trim(nz(Me.IDsearch,""))) = 0 Then
MsgBox "Please enter a value to search for", _
vbOKOnly, _
"Invalid Search Criterion!"
Me.IDsearch.SetFocus
Exit Sub
end if

'save current record if changes were made
If me.dirty then me.dirty = false

Me.RecordsetClone.FindFirst _
"[Patient ID]= '" & Me.IDsearch & "'"

If Not Me.RecordsetClone.NoMatch Then
Me.Bookmark = Me.RecordsetClone.Bookmark
End If

'~~~~~~~~~~~~~~~

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*



esca007 wrote:
Hi, I'm trying to develop a search function for my form, and found a
similar
code for the website. After changing it, I've almost made the search
work but
the problem I have right now is that the pop up function does not
differentiate between a correct ID and an incorrect ID. I think it has
to do
with my If.. Else code. It will not do the 2 tasks in sequences. Below
is my
code.

rivate Sub Search_Click()
Dim strPatient_ID As String
Dim strSearch As String

'Check txtSearch for Null value or Nill Entry first.

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

'Performs the search using value entered into txtSearch
'and evaluates this against values in strStudentID

DoCmd.ShowAllRecords
DoCmd.GoToControl ("Patient ID")
DoCmd.FindRecord Me!IDsearch

Patient_ID.SetFocus
strPatientID = Patient_ID.Text
IDsearch.SetFocus
strSearch = IDsearch.Text

'If matching record found sets focus in strPatientID
'and clears search control

If strPatient_ID <> strSearch Then
MsgBox "Match Found: " & strSearch & "" _
, , "Congratulations!"
IDsearch.SetFocus
IDsearch = ""
'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!"
IDsearch.SetFocus
End If
End Sub

The problem starts at the Comment "If matching record found sets focus
in
strPatientID 'and clears search control". The If Else function doesnt
work.
In this set up, it'll going do the "If" task regardless if I type in an
existing ID or not.
Can anyone tell me what I wrote wrong? Thank you very much

 

[Guest]

Thank you very much

Use a combobox to pick the code and then they can only choose one that
DOES exists

a control to be used for searching will be unbound (no ControlSource),
so whatever record is on the screen will not be changed. I also like to
change the background color of search combos so that the user knows this
is not part of the data

"Do you mind explain what problem the If Else statement that I had that
made it that it only runs parts of the statement? "

you had:

If IsNull(Me![IDsearch]) Or (Me![IDsearch]) = "" then

if IDsearch is nothing (null), you cannot compare that to a value...
that is why I converted what you had to

Len(Trim(nz(Me.IDsearch,""))) = 0

Now, it appears that the real problem is that the user may enter an
invalid patient id... using a combobox will solve this

Anyway, IMHO, you should not be using a text field to relate data in
your tables. Conventionally, using ID in the fieldname implies that
fieldname was created using an autonumber field in the main table and is
defined to be a long integer in related tables. To avoid confusion, if
you have a "Patient ID" that is their hospital number, consider using a
different fieldname for that like "PatientNumber" or "PatientNum" (I
like to keep names short)

Combobox Example

* Under no circumstances should you store names in more than one place.
For instance, if you have a People table, define a PID (or PeopleID)
autonumber field. Then, in other tables, when you want to identify a
person, you can use the key field. One way to do this…

Create an autonumber field in the People table -->

PID, autonumber

then, in the other tables...
PID, long, DefaultValue = Null

Then, when you want to put data in (which should be done from a form),
you can set it up to pick names from a list but store the PID.

create a combobox control

Name --> PID

ControlSource --> PID

RowSource -->
SELECT
PID,
LastName & ", " & Firstname AS Fullname,
BirthDate
FROM People
ORDER BY LastName, Firstname

BoundColumn --> 1

ColumnCount --> 3

columnWidths --> 0;2;1
(etc for however many columns you have -- the ID column will be hidden)

ListWidth --> 3
(should add up to the sum of the column widths)

if you have a listbox, sometimes you need to make the width .01 more
than the sum of the columns to prevent the horizontal scrollbar from
appearing.

PID will be stored in the form RecordSource while showing you names from
another table... a MUCH better and more reliable method.

If you want to show other information from your combobox in other
controls, you can use calculated fields. For instance

textbox:
Name --> BirthDate
ControlSource --> = PID.column(2)

The reason that column 2 is referenced instead of column 3 is that
column indexes start with 0, not 1, in Access.



Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

ps... thanks, Allen

thank you for your response but the question is that the codes right now does
not tell the data entry person that the ID does not exist.
Do you mind explain what problem the If Else statement that I had that made
it that it only runs parts of the statement?
I tried that code in its original database and it works... so really want to
know what I did wrong. thanks

A bound form has a RecordsetClone - another set of pointers to the same
records. You can search for the record in the clone set, and test NoMatch to
see whether it was found. If it's not found, you can notify the user without
having moved away from the current record. If it is found, you can set the
form's Bookmark to the bookmark of the found record in the clone set.

Crystal's code takes care of all of that:
- checking the user entered something to search for;
- explicitly saving the record if edits are in progress (since it must be
saved before you can go to another records);
- finding the first match in the RecordsetClone of the form;
- testing if a match was found;
- moving to that record if it was found.

I recommend you take her approach.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hi,
Thanks for the new code. I replaced your code with mine but it still
doesn't
work.
Can you actually explain to me what your code means? Thanks

:

'~~~~~~~~~~~~~~~
If Len(Trim(nz(Me.IDsearch,""))) = 0 Then
MsgBox "Please enter a value to search for", _
vbOKOnly, _
"Invalid Search Criterion!"
Me.IDsearch.SetFocus
Exit Sub
end if

'save current record if changes were made
If me.dirty then me.dirty = false

Me.RecordsetClone.FindFirst _
"[Patient ID]= '" & Me.IDsearch & "'"

If Not Me.RecordsetClone.NoMatch Then
Me.Bookmark = Me.RecordsetClone.Bookmark
End If

'~~~~~~~~~~~~~~~

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*



esca007 wrote:
Hi, I'm trying to develop a search function for my form, and found a
similar
code for the website. After changing it, I've almost made the search
work but
the problem I have right now is that the pop up function does not
differentiate between a correct ID and an incorrect ID. I think it has
to do
with my If.. Else code. It will not do the 2 tasks in sequences. Below
is my
code.

rivate Sub Search_Click()
Dim strPatient_ID As String
Dim strSearch As String

'Check txtSearch for Null value or Nill Entry first.

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

'Performs the search using value entered into txtSearch
'and evaluates this against values in strStudentID

DoCmd.ShowAllRecords
DoCmd.GoToControl ("Patient ID")
DoCmd.FindRecord Me!IDsearch

Patient_ID.SetFocus
strPatientID = Patient_ID.Text
IDsearch.SetFocus
strSearch = IDsearch.Text

'If matching record found sets focus in strPatientID
'and clears search control

If strPatient_ID <> strSearch Then
MsgBox "Match Found: " & strSearch & "" _
, , "Congratulations!"
IDsearch.SetFocus
IDsearch = ""
'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!"
IDsearch.SetFocus
End If
End Sub

The problem starts at the Comment "If matching record found sets focus
in
strPatientID 'and clears search control". The If Else function doesnt
work.
In this set up, it'll going do the "If" task regardless if I type in an
existing ID or not.
Can anyone tell me what I wrote wrong? Thank you very much

 

[strive4peace]

you're welcome happy to help

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

Thank you very much

Use a combobox to pick the code and then they can only choose one that
DOES exists

a control to be used for searching will be unbound (no ControlSource),
so whatever record is on the screen will not be changed. I also like to
change the background color of search combos so that the user knows this
is not part of the data

"Do you mind explain what problem the If Else statement that I had that
made it that it only runs parts of the statement? "

you had:

If IsNull(Me![IDsearch]) Or (Me![IDsearch]) = "" then

if IDsearch is nothing (null), you cannot compare that to a value...
that is why I converted what you had to

Len(Trim(nz(Me.IDsearch,""))) = 0

Now, it appears that the real problem is that the user may enter an
invalid patient id... using a combobox will solve this

Anyway, IMHO, you should not be using a text field to relate data in
your tables. Conventionally, using ID in the fieldname implies that
fieldname was created using an autonumber field in the main table and is
defined to be a long integer in related tables. To avoid confusion, if
you have a "Patient ID" that is their hospital number, consider using a
different fieldname for that like "PatientNumber" or "PatientNum" (I
like to keep names short)

Combobox Example

* Under no circumstances should you store names in more than one place.
For instance, if you have a People table, define a PID (or PeopleID)
autonumber field. Then, in other tables, when you want to identify a
person, you can use the key field. One way to do this…

Create an autonumber field in the People table -->

PID, autonumber

then, in the other tables...
PID, long, DefaultValue = Null

Then, when you want to put data in (which should be done from a form),
you can set it up to pick names from a list but store the PID.

create a combobox control

Name --> PID

ControlSource --> PID

RowSource -->
SELECT
PID,
LastName & ", " & Firstname AS Fullname,
BirthDate
FROM People
ORDER BY LastName, Firstname

BoundColumn --> 1

ColumnCount --> 3

columnWidths --> 0;2;1
(etc for however many columns you have -- the ID column will be hidden)

ListWidth --> 3
(should add up to the sum of the column widths)

if you have a listbox, sometimes you need to make the width .01 more
than the sum of the columns to prevent the horizontal scrollbar from
appearing.

PID will be stored in the form RecordSource while showing you names from
another table... a MUCH better and more reliable method.

If you want to show other information from your combobox in other
controls, you can use calculated fields. For instance

textbox:
Name --> BirthDate
ControlSource --> = PID.column(2)

The reason that column 2 is referenced instead of column 3 is that
column indexes start with 0, not 1, in Access.



Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

ps... thanks, Allen

thank you for your response but the question is that the codes right now does
not tell the data entry person that the ID does not exist.
Do you mind explain what problem the If Else statement that I had that made
it that it only runs parts of the statement?
I tried that code in its original database and it works... so really want to
know what I did wrong. thanks

:

A bound form has a RecordsetClone - another set of pointers to the same
records. You can search for the record in the clone set, and test NoMatch to
see whether it was found. If it's not found, you can notify the user without
having moved away from the current record. If it is found, you can set the
form's Bookmark to the bookmark of the found record in the clone set.

Crystal's code takes care of all of that:
- checking the user entered something to search for;
- explicitly saving the record if edits are in progress (since it must be
saved before you can go to another records);
- finding the first match in the RecordsetClone of the form;
- testing if a match was found;
- moving to that record if it was found.

I recommend you take her approach.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hi,
Thanks for the new code. I replaced your code with mine but it still
doesn't
work.
Can you actually explain to me what your code means? Thanks

:

'~~~~~~~~~~~~~~~
If Len(Trim(nz(Me.IDsearch,""))) = 0 Then
MsgBox "Please enter a value to search for", _
vbOKOnly, _
"Invalid Search Criterion!"
Me.IDsearch.SetFocus
Exit Sub
end if

'save current record if changes were made
If me.dirty then me.dirty = false

Me.RecordsetClone.FindFirst _
"[Patient ID]= '" & Me.IDsearch & "'"

If Not Me.RecordsetClone.NoMatch Then
Me.Bookmark = Me.RecordsetClone.Bookmark
End If

'~~~~~~~~~~~~~~~

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*



esca007 wrote:
Hi, I'm trying to develop a search function for my form, and found a
similar
code for the website. After changing it, I've almost made the search
work but
the problem I have right now is that the pop up function does not
differentiate between a correct ID and an incorrect ID. I think it has
to do
with my If.. Else code. It will not do the 2 tasks in sequences. Below
is my
code.

rivate Sub Search_Click()
Dim strPatient_ID As String
Dim strSearch As String

'Check txtSearch for Null value or Nill Entry first.

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

'Performs the search using value entered into txtSearch
'and evaluates this against values in strStudentID

DoCmd.ShowAllRecords
DoCmd.GoToControl ("Patient ID")
DoCmd.FindRecord Me!IDsearch

Patient_ID.SetFocus
strPatientID = Patient_ID.Text
IDsearch.SetFocus
strSearch = IDsearch.Text

'If matching record found sets focus in strPatientID
'and clears search control

If strPatient_ID <> strSearch Then
MsgBox "Match Found: " & strSearch & "" _
, , "Congratulations!"
IDsearch.SetFocus
IDsearch = ""
'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!"
IDsearch.SetFocus
End If
End Sub

The problem starts at the Comment "If matching record found sets focus
in
strPatientID 'and clears search control". The If Else function doesnt
work.
In this set up, it'll going do the "If" task regardless if I type in an
existing ID or not.
Can anyone tell me what I wrote wrong? Thank you very much

 

[Guest]

Hi Crystal,
I have a problem with the code you gave me. When I run it, it shows an error
saying
"Data type mismatched in criteria expression"
and debug highlights this part.

Me.RecordsetClone.FindFirst _
"[Patient ID]= '" & Me.IDsearch & "'"

Do you know the error comes up? It didn't happen before. thanks

esca007

you're welcome happy to help

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

Thank you very much

Use a combobox to pick the code and then they can only choose one that
DOES exists

a control to be used for searching will be unbound (no ControlSource),
so whatever record is on the screen will not be changed. I also like to
change the background color of search combos so that the user knows this
is not part of the data

"Do you mind explain what problem the If Else statement that I had that
made it that it only runs parts of the statement? "

you had:

If IsNull(Me![IDsearch]) Or (Me![IDsearch]) = "" then

if IDsearch is nothing (null), you cannot compare that to a value...
that is why I converted what you had to

Len(Trim(nz(Me.IDsearch,""))) = 0

Now, it appears that the real problem is that the user may enter an
invalid patient id... using a combobox will solve this

Anyway, IMHO, you should not be using a text field to relate data in
your tables. Conventionally, using ID in the fieldname implies that
fieldname was created using an autonumber field in the main table and is
defined to be a long integer in related tables. To avoid confusion, if
you have a "Patient ID" that is their hospital number, consider using a
different fieldname for that like "PatientNumber" or "PatientNum" (I
like to keep names short)

Combobox Example

* Under no circumstances should you store names in more than one place.
For instance, if you have a People table, define a PID (or PeopleID)
autonumber field. Then, in other tables, when you want to identify a
person, you can use the key field. One way to do this…

Create an autonumber field in the People table -->

PID, autonumber

then, in the other tables...
PID, long, DefaultValue = Null

Then, when you want to put data in (which should be done from a form),
you can set it up to pick names from a list but store the PID.

create a combobox control

Name --> PID

ControlSource --> PID

RowSource -->
SELECT
PID,
LastName & ", " & Firstname AS Fullname,
BirthDate
FROM People
ORDER BY LastName, Firstname

BoundColumn --> 1

ColumnCount --> 3

columnWidths --> 0;2;1
(etc for however many columns you have -- the ID column will be hidden)

ListWidth --> 3
(should add up to the sum of the column widths)

if you have a listbox, sometimes you need to make the width .01 more
than the sum of the columns to prevent the horizontal scrollbar from
appearing.

PID will be stored in the form RecordSource while showing you names from
another table... a MUCH better and more reliable method.

If you want to show other information from your combobox in other
controls, you can use calculated fields. For instance

textbox:
Name --> BirthDate
ControlSource --> = PID.column(2)

The reason that column 2 is referenced instead of column 3 is that
column indexes start with 0, not 1, in Access.



Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

ps... thanks, Allen


esca007 wrote:
thank you for your response but the question is that the codes right now does
not tell the data entry person that the ID does not exist.
Do you mind explain what problem the If Else statement that I had that made
it that it only runs parts of the statement?
I tried that code in its original database and it works... so really want to
know what I did wrong. thanks

:

A bound form has a RecordsetClone - another set of pointers to the same
records. You can search for the record in the clone set, and test NoMatch to
see whether it was found. If it's not found, you can notify the user without
having moved away from the current record. If it is found, you can set the
form's Bookmark to the bookmark of the found record in the clone set.

Crystal's code takes care of all of that:
- checking the user entered something to search for;
- explicitly saving the record if edits are in progress (since it must be
saved before you can go to another records);
- finding the first match in the RecordsetClone of the form;
- testing if a match was found;
- moving to that record if it was found.

I recommend you take her approach.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hi,
Thanks for the new code. I replaced your code with mine but it still
doesn't
work.
Can you actually explain to me what your code means? Thanks

:

'~~~~~~~~~~~~~~~
If Len(Trim(nz(Me.IDsearch,""))) = 0 Then
MsgBox "Please enter a value to search for", _
vbOKOnly, _
"Invalid Search Criterion!"
Me.IDsearch.SetFocus
Exit Sub
end if

'save current record if changes were made
If me.dirty then me.dirty = false

Me.RecordsetClone.FindFirst _
"[Patient ID]= '" & Me.IDsearch & "'"

If Not Me.RecordsetClone.NoMatch Then
Me.Bookmark = Me.RecordsetClone.Bookmark
End If

'~~~~~~~~~~~~~~~

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*



esca007 wrote:
Hi, I'm trying to develop a search function for my form, and found a
similar
code for the website. After changing it, I've almost made the search
work but
the problem I have right now is that the pop up function does not
differentiate between a correct ID and an incorrect ID. I think it has
to do
with my If.. Else code. It will not do the 2 tasks in sequences. Below
is my
code.

rivate Sub Search_Click()
Dim strPatient_ID As String
Dim strSearch As String

'Check txtSearch for Null value or Nill Entry first.

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

'Performs the search using value entered into txtSearch
'and evaluates this against values in strStudentID

DoCmd.ShowAllRecords
DoCmd.GoToControl ("Patient ID")
DoCmd.FindRecord Me!IDsearch

Patient_ID.SetFocus
strPatientID = Patient_ID.Text
IDsearch.SetFocus
strSearch = IDsearch.Text

'If matching record found sets focus in strPatientID
'and clears search control

If strPatient_ID <> strSearch Then
MsgBox "Match Found: " & strSearch & "" _
, , "Congratulations!"
IDsearch.SetFocus
IDsearch = ""
'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!"
IDsearch.SetFocus
End If
End Sub

The problem starts at the Comment "If matching record found sets focus
in
strPatientID 'and clears search control". The If Else function doesnt
work.
In this set up, it'll going do the "If" task regardless if I type in an
existing ID or not.
Can anyone tell me what I wrote wrong? Thank you very much

 

[Alex Dybenko]

Hi,
if Patient ID field is a number field - then you don't need to put in
quotes:
"[Patient ID]= " & Me.IDsearch

--
Best regards,
___________
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com

Hi Crystal,
I have a problem with the code you gave me. When I run it, it shows an
error
saying
"Data type mismatched in criteria expression"
and debug highlights this part.

Me.RecordsetClone.FindFirst _
"[Patient ID]= '" & Me.IDsearch & "'"

Do you know the error comes up? It didn't happen before. thanks

esca007

you're welcome happy to help

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

Thank you very much

:

Use a combobox to pick the code and then they can only choose one that
DOES exists

a control to be used for searching will be unbound (no ControlSource),
so whatever record is on the screen will not be changed. I also like
to
change the background color of search combos so that the user knows
this
is not part of the data

"Do you mind explain what problem the If Else statement that I had
that
made it that it only runs parts of the statement? "

you had:

If IsNull(Me![IDsearch]) Or (Me![IDsearch]) = "" then

if IDsearch is nothing (null), you cannot compare that to a value...
that is why I converted what you had to

Len(Trim(nz(Me.IDsearch,""))) = 0

Now, it appears that the real problem is that the user may enter an
invalid patient id... using a combobox will solve this

Anyway, IMHO, you should not be using a text field to relate data in
your tables. Conventionally, using ID in the fieldname implies that
fieldname was created using an autonumber field in the main table and
is
defined to be a long integer in related tables. To avoid confusion,
if
you have a "Patient ID" that is their hospital number, consider using
a
different fieldname for that like "PatientNumber" or "PatientNum" (I
like to keep names short)

Combobox Example

* Under no circumstances should you store names in more than one
place.
For instance, if you have a People table, define a PID (or
PeopleID)
autonumber field. Then, in other tables, when you want to identify a
person, you can use the key field. One way to do this…

Create an autonumber field in the People table -->

PID, autonumber

then, in the other tables...
PID, long, DefaultValue = Null

Then, when you want to put data in (which should be done from a form),
you can set it up to pick names from a list but store the PID.

create a combobox control

Name --> PID

ControlSource --> PID

RowSource -->
SELECT
PID,
LastName & ", " & Firstname AS Fullname,
BirthDate
FROM People
ORDER BY LastName, Firstname

BoundColumn --> 1

ColumnCount --> 3

columnWidths --> 0;2;1
(etc for however many columns you have -- the ID column will be
hidden)

ListWidth --> 3
(should add up to the sum of the column widths)

if you have a listbox, sometimes you need to make the width .01 more
than the sum of the columns to prevent the horizontal scrollbar from
appearing.

PID will be stored in the form RecordSource while showing you names
from
another table... a MUCH better and more reliable method.

If you want to show other information from your combobox in other
controls, you can use calculated fields. For instance

textbox:
Name --> BirthDate
ControlSource --> = PID.column(2)

The reason that column 2 is referenced instead of column 3 is that
column indexes start with 0, not 1, in Access.



Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

ps... thanks, Allen


esca007 wrote:
thank you for your response but the question is that the codes right
now does
not tell the data entry person that the ID does not exist.
Do you mind explain what problem the If Else statement that I had
that made
it that it only runs parts of the statement?
I tried that code in its original database and it works... so really
want to
know what I did wrong. thanks

:

A bound form has a RecordsetClone - another set of pointers to the
same
records. You can search for the record in the clone set, and test
NoMatch to
see whether it was found. If it's not found, you can notify the user
without
having moved away from the current record. If it is found, you can
set the
form's Bookmark to the bookmark of the found record in the clone
set.

Crystal's code takes care of all of that:
- checking the user entered something to search for;
- explicitly saving the record if edits are in progress (since it
must be
saved before you can go to another records);
- finding the first match in the RecordsetClone of the form;
- testing if a match was found;
- moving to that record if it was found.

I recommend you take her approach.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hi,
Thanks for the new code. I replaced your code with mine but it
still
doesn't
work.
Can you actually explain to me what your code means? Thanks

:

'~~~~~~~~~~~~~~~
If Len(Trim(nz(Me.IDsearch,""))) = 0 Then
MsgBox "Please enter a value to search for", _
vbOKOnly, _
"Invalid Search Criterion!"
Me.IDsearch.SetFocus
Exit Sub
end if

'save current record if changes were made
If me.dirty then me.dirty = false

Me.RecordsetClone.FindFirst _
"[Patient ID]= '" & Me.IDsearch & "'"

If Not Me.RecordsetClone.NoMatch Then
Me.Bookmark = Me.RecordsetClone.Bookmark
End If

'~~~~~~~~~~~~~~~

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*



esca007 wrote:
Hi, I'm trying to develop a search function for my form, and
found a
similar
code for the website. After changing it, I've almost made the
search
work but
the problem I have right now is that the pop up function does not
differentiate between a correct ID and an incorrect ID. I think
it has
to do
with my If.. Else code. It will not do the 2 tasks in sequences.
Below
is my
code.

rivate Sub Search_Click()
Dim strPatient_ID As String
Dim strSearch As String

'Check txtSearch for Null value or Nill Entry first.

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

'Performs the search using value entered into txtSearch
'and evaluates this against values in strStudentID

DoCmd.ShowAllRecords
DoCmd.GoToControl ("Patient ID")
DoCmd.FindRecord Me!IDsearch

Patient_ID.SetFocus
strPatientID = Patient_ID.Text
IDsearch.SetFocus
strSearch = IDsearch.Text

'If matching record found sets focus in strPatientID
'and clears search control

If strPatient_ID <> strSearch Then
MsgBox "Match Found: " & strSearch & "" _
, , "Congratulations!"
IDsearch.SetFocus
IDsearch = ""
'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!"
IDsearch.SetFocus
End If
End Sub

The problem starts at the Comment "If matching record found sets
focus
in
strPatientID 'and clears search control". The If Else function
doesnt
work.
In this set up, it'll going do the "If" task regardless if I type
in an
existing ID or not.
Can anyone tell me what I wrote wrong? Thank you very much

 

[strive4peace]

thanks, Alex

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*