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 |