Prev: Display msg when value chgs to zero.
Next: Referring to text control in a form
From: CharlesD on 21 May 2010 23:31
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


From: Dennis on 21 May 2010 23:47


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

From: Linq Adams via AccessMonster.com on 22 May 2010 00:14
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/Forums.aspx/access-formscoding/201005/1

From: CharlesD on 22 May 2010 06:47
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/Forums.aspx/access-formscoding/201005/1
>
> .
>
From: CharlesD on 22 May 2010 07:08
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
>
 |  Next  |  Last
Pages: 1 2
Prev: Display msg when value chgs to zero.
Next: Referring to text control in a form