Access is all about databinding.
First get a form setup (you can use the wizard) that is based on your
Patient Table, lets assume the following since you did not provide the
fields or the table definition:
Table: PatientInformation
Fields:
SubjectNum (primary key)
FirstName
LastName
BirthDate
Create a text box in the header of the form "txtSearch" (you could also do
this with a combo box but that is another story) In the button code I
suggest this:
Private Sub Search_Click()
Dim subjectNum as Long
' You can do some error checking here
' Is there a entered value, is it numeric
If IsNull(txtSearch.Value) Then Exit Sub
' Make sure the entered subjectnum exists in the database
subjectNum = DLookup("[SubjectNum]", "PatientInformation",
"[SubjectNum]=" & txtSearch.Value)
If subjectNum > 0 Then
' Move the record pointer to the entered subject num
Me.Filter = "[SubjectNum] = " & subjectNum
Me.FilterOn = True
Me.Refresh
Else
Call MsgBox("Subject Number not found!")
End If
End Sub
pokdbz said:
Yes that did clear up that problem.
I am getting a message box saying: Item not found in this collection.
Maybe I am not understanding this right. Let me explain what I am trying to
do.
I have a button which pressed needs to search in a text box which a person
enters a number txtSearch for the SubjectNum.
When I do find the subject number I need to display the information that
goes along with that in some text boxes such as patients name and birthdate.
What is the simplest way to go about this.
Maybe I don't have the query setup right here is an example of what I have
in the query.
SubjectNum FirstName
Patient_Registy Patient_Registry
Is there something that I have to do to the SubjectNum field so that it
knows to look for that subject number in the text box that was entered by a
person.
If you could explain this a little better it would help out alot.
thanks
solex said:
Make sure you have a reference to "Microsoft DAO #.## Object Library" in
your database. When viewing code go to the \\Tools\References menu
It is giving me a Compile Error
User defined type not defined
Do you know what is the problem with it?
It is pointing to this statement:
Dim qdf As DAO.QueryDef
:
There are a number of approaches:
(1) instead of a search box make it a drop down with all Subjects, with
the
SubjectNum as the bound column then you can get rid of the querydef
(2) use dlookup instead of the querydef
Is there an easier way than to run a query to search for a record. If
there
is that would be what I was looking for.
:
I gather from your code that you want to set the current record of
an
edit
form. The problem with your code is that you run a query but you do
not
pass the return value of the query to the form. A suggested
approach
based
on what you have already done:
Private Sub Search_Click()
Dim qdf As DAO.QueryDef
Dim rs As DAO.Recordset
On Error GoTo ErrorHandler
If txtSearch.Visible = False Then
lblSearch.Visible = True
txtSearch.Visible = True
End If
Set qdf = CurrentDb.QueryDefs("SearchSubjectNum")
qdf.Parameters("whichSubjetNum").Value = Val(txtSearch.Value)
Set rs = qdf.OpenRecordset
If Not(rs.EOF()) Then
Me.Filter = "[SubjectNum] = " & rs(0).Value
Me.FilterOn = True
Me.Refresh
Else
Call MsgBox("Subject Number not found!")
End If
rs.Close
qdf.Close
ExitHandler:
Set rs = Nothing
Set qdf = Nothing
Exit Sub
ErrorHandler:
MsgBox Err.description
Resume ExitHandler
End Sub
I need help searching for a record in a table.
The table: Patient Registry
The field: SubjectNum
I have a text box where the number is to be input I just don't
know
how
to
get it to seach by the press of the button.
I don't know if I am even going about this the right way. If you
have
any
other suggestions just let me know. Thanks for the help.
Here is what I have so far:
Private Sub Search_Click()
On Error GoTo Err_Search_Click
Dim qdf As DAO.QueryDef
Dim rs As DAO.Recordset
If txtSearch.Visible = False Then
lblSearch.Visible = True
txtSearch.Visible = True
End If
Set qdf = CurrentDb.QueryDefs("SearchSubjectNum")
qdf.Parameters("whichSubjetNum").Value = Val(txtSearch.Value)
Set rs = qdf.OpenRecordset
Screen.PreviousControl.SetFocus
DoCmd.DoMenuItem "", acEditMenu, 10, , acMenuVer70
Exit_Search_Click:
Exit Sub
Err_Search_Click:
MsgBox Err.description
Resume Exit_Search_Click
End Sub
