Hi,

I have a form that is bound to a table (tblPatient). I have tried using
CRTL F as the method of searching the table. It does not work well moving
off the search field after a find and other issues. I have tried adding the
following code to allow a search on the MEDICARE field in the table, but the
form does not display the found record. I would appreciate any help to
understand how to search and dsipaly a found record. Below is my code.

Thanks,

Private Sub cmdFind_Click()
Dim dbs As Database, rstPatient As Recordset
Dim txtBilling_No As String

On Error GoTo ErrorHandler


Set dbs = CurrentDb
Set rstPatient = dbs.OpenRecordset("tblPatient", dbOpenDynaset, dbSeeChanges)

txtBilling_No = InputBox("Please Enter Billing Number", "Patient Find")
If IsNull(txtBilling_No) Then
MsgBox ("No Billing Number Entered - Please Enter a Valid Number")
Else
' MsgBox ("Patient Number " & txtBilling_No)
rstPatient.FindFirst "[MEDICARE] = ""txtBilling_No"" "
If Not (rstPatient.BOF And rstPatient.EOF) Then
Me.Bookmark = rstPatient.Bookmark
Me.Refresh
Else
MsgBox ("Patient Not Found - Please Enter a New Number")
End If
End If

GoTo Exit_cmdFind_Click

ErrorHandler:
MsgBox LTrim(RTrim(Me.NAME)) + "." + "Patient Find - " + "Error: " +
AccessError(Err.Number)

Exit_cmdFind_Click:
rstPatient.Close
Set dbs = Nothing
Set rstPatient = Nothing
End Sub

Charles,

I’m not quite sure for what you are searching. I’m guessing that you are
searching for the patient using the patient’s number.

Try using a combo box.

Put a combo box for Patient ID on your form. The control source for this
field should be unbound. The Row Source Type is Table/Query. The Row Source
should be an SQL stmt something like “SELECT tblPatient.PatientID,
tblPatient.PatientName, FROM tblPatient;

On the combo box’s After Update event, put in your code

Set dbs = CurrentDb
Set rstPatient = dbs.OpenRecordset("tblPatient", dbOpenDynaset, dbSeeChanges)

rstPatient.FindFirst "[MEDICARE] = ""txtBilling_No"" "
If Not (rstPatient.BOF And rstPatient.EOF) Then
Me.Bookmark = rstPatient.Bookmark
Me.Refresh
Else
MsgBox ("Patient Not Found - Please Enter a New Number")
End If
rstPatient.Close
Set dbs = Nothing
Set rstPatient = Nothing
End Sub

Dennis

Hi,

I changed my code to the following:

Private Sub cmdFind_Click()
Dim dbs As Database, rstPatient As Recordset
Dim txtBilling_No, strQuote As String
strQuote = Chr$(34)
On Error GoTo ErrorHandler


Set dbs = CurrentDb
Set rstPatient = dbs.OpenRecordset("tblPatient", dbOpenDynaset, dbSeeChanges)

txtBilling_No = Trim(InputBox("Please Enter Billing Number", "Patient Find"))
If IsNull(txtBilling_No) Then
MsgBox ("No Billing Number Entered - Please Enter a Valid Number")
Else
rstPatient.FindFirst "[MEDICARE] = " & strQuote & txtBilling_No & strQuote
If Not (rstPatient.NoMatch) Then
MsgBox ("Patient Number " & str(rstPatient!apkPATIENT))
Me.Bookmark = rstPatient.Bookmark
Me.Refresh
Else
MsgBox ("Patient Not Found - Please Enter a New Number")
End If
End If

GoTo Exit_cmdFind_Click

ErrorHandler:
MsgBox LTrim(RTrim(Me.NAME)) + "." + "Patient Find - " + "Error: " +
AccessError(Err.Number)

Exit_cmdFind_Click:
rstPatient.Close
Set dbs = Nothing
Set rstPatient = Nothing
End Sub

The correct record is found as the rstPatient!apkPatient is correct. The
rstPatient.Bookmark is a "?". Why would the bookmark not be valid?

Thanks,


"Linq Adams via AccessMonster.com" wrote:

> The form doesn't display the found record, I suspect, because no record is
> found!
> I believe this syntax is incorrect
>
> rstPatient.FindFirst "[MEDICARE] = "" txtBilling_No """
>
> If txtBilling_No is defined as Text it should be:
>
> rstPatient.FindFirst "[MEDICARE] = '" & Me.txtBilling_No & "'"
>
>
> If txtBilling_No is defined as Numeric:
>
> rstPatient.FindFirst "[MEDICARE] = " & Me.txtBilling_No
>
> --
> There's ALWAYS more than one way to skin a cat!
>
> Answers/posts based on Access 2000/2003
>
> Message posted via AccessMonster.com
> http://www.accessmonster.com/Uwe/For...oding/201005/1
>
> .
>

Hi,

Thanks for the reply. I want to search the tblPatient using a patient
number the user inputs. The form should then display the found record. It
would be more versitile to me to be able to search and move to that record as
I could search on other fields in the table.

In code just posted I can find the patient's record in the rstPatient table
using the user input field, but cannot goto that record in the bound form?

Regards,

CharlesD

"Dennis" wrote:

>
>
> Charles,
>
> I’m not quite sure for what you are searching. I’m guessing that you are
> searching for the patient using the patient’s number.
>
> Try using a combo box.
>
> Put a combo box for Patient ID on your form. The control source for this
> field should be unbound. The Row Source Type is Table/Query. The Row Source
> should be an SQL stmt something like “SELECT tblPatient.PatientID,
> tblPatient.PatientName, FROM tblPatient;
>
> On the combo box’s After Update event, put in your code
>
> Set dbs = CurrentDb
> Set rstPatient = dbs.OpenRecordset("tblPatient", dbOpenDynaset, dbSeeChanges)
>
> rstPatient.FindFirst "[MEDICARE] = ""txtBilling_No"" "
> If Not (rstPatient.BOF And rstPatient.EOF) Then
> Me.Bookmark = rstPatient.Bookmark
> Me.Refresh
> Else
> MsgBox ("Patient Not Found - Please Enter a New Number")
> End If
> rstPatient.Close
> Set dbs = Nothing
> Set rstPatient = Nothing
> End Sub
>
> Dennis
>

Hi,

I changed the rstPatient recordset to a clone of the form with
Set rstPatient = Me.RecordsetClone and all works well.

Thanks for the help.

Regards,

CharlesD

"CharlesD" wrote:

> Hi,
>
> I have a form that is bound to a table (tblPatient). I have tried using
> CRTL F as the method of searching the table. It does not work well moving
> off the search field after a find and other issues. I have tried adding the
> following code to allow a search on the MEDICARE field in the table, but the
> form does not display the found record. I would appreciate any help to
> understand how to search and dsipaly a found record. Below is my code.
>
> Thanks,
>
> Private Sub cmdFind_Click()
> Dim dbs As Database, rstPatient As Recordset
> Dim txtBilling_No As String
>
> On Error GoTo ErrorHandler
>
>
> Set dbs = CurrentDb
> Set rstPatient = dbs.OpenRecordset("tblPatient", dbOpenDynaset, dbSeeChanges)
>
> txtBilling_No = InputBox("Please Enter Billing Number", "Patient Find")
> If IsNull(txtBilling_No) Then
> MsgBox ("No Billing Number Entered - Please Enter a Valid Number")
> Else
> ' MsgBox ("Patient Number " & txtBilling_No)
> rstPatient.FindFirst "[MEDICARE] = ""txtBilling_No"" "
> If Not (rstPatient.BOF And rstPatient.EOF) Then
> Me.Bookmark = rstPatient.Bookmark
> Me.Refresh
> Else
> MsgBox ("Patient Not Found - Please Enter a New Number")
> End If
> End If
>
> GoTo Exit_cmdFind_Click
>
> ErrorHandler:
> MsgBox LTrim(RTrim(Me.NAME)) + "." + "Patient Find - " + "Error: " +
> AccessError(Err.Number)
>
> Exit_cmdFind_Click:
> rstPatient.Close
> Set dbs = Nothing
> Set rstPatient = Nothing
> End Sub
>
>