Bound Form Search

[CharlesD]

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

 

[Dennis]

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

 

[Linq Adams via AccessMonster.com]

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

 

[CharlesD]

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,

 

[CharlesD]

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

 

[CharlesD]

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