Searching for a record

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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
 
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.

solex said:
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

pokdbz said:
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
Click to expand...
Click to expand...
 
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


pokdbz said:
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.

solex said:
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

pokdbz said:
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
Click to expand...
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
Click to expand...
Click to expand...
Click to expand...
 
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

solex said:
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


pokdbz said:
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.

solex said:
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
Click to expand...
Click to expand...
Click to expand...
 
Make sure you have a reference to "Microsoft DAO #.## Object Library" in
your database. When viewing code go to the \\Tools\References menu


pokdbz said:
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

solex said:
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


pokdbz said:
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
Click to expand...
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
Click to expand...
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
Click to expand...
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
Click to expand...
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
Click to expand...
Click to expand...
Click to expand...
 
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


pokdbz said:
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

solex said:
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
Click to expand...
Click to expand...
Click to expand...
 
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


pokdbz said:
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,
Click to expand...
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
Click to expand...
of
an
edit
form. The problem with your code is that you run a query but
Click to expand...
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
Click to expand...
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
Click to expand...
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
Click to expand...
Click to expand...
Click to expand...
 
Thanks for the help it works now. The only thing is when you enter a value
that is not in the DB it goes to the debugger and says invalid use of NULL on
this statement:
subjectnum = DLookup("[SubjectNum]", "Patient_Registry", "[SubjectNum]=" &
txtSearch.Value)

It is not getting to the message Subject Number not found if it can't find it.

Do you have any idea of how to fix this?
Thanks for the help

solex said:
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
Click to expand...
Click to expand...
Click to expand...
 
Did you include the check that I supplied in the attached code?

If IsNull(txtSearch.Value) Then Exit Sub

pokdbz said:
Thanks for the help it works now. The only thing is when you enter a value
that is not in the DB it goes to the debugger and says invalid use of NULL on
this statement:
subjectnum = DLookup("[SubjectNum]", "Patient_Registry", "[SubjectNum]=" &
txtSearch.Value)

It is not getting to the message Subject Number not found if it can't find it.

Do you have any idea of how to fix this?
Thanks for the help

solex said:
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
Click to expand...
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
Click to expand...
by
a
person.

If you could explain this a little better it would help out alot.
thanks

:

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
Click to expand...
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
Click to expand...
record.
If
there
is that would be what I was looking for.

:

I gather from your code that you want to set the current
Click to expand...
record
of
an
edit
form. The problem with your code is that you run a query
Click to expand...
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.
Click to expand...
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
Click to expand...
Click to expand...
Click to expand...
 
Yes I did

solex said:
Did you include the check that I supplied in the attached code?

If IsNull(txtSearch.Value) Then Exit Sub

pokdbz said:
Thanks for the help it works now. The only thing is when you enter a value
that is not in the DB it goes to the debugger and says invalid use of NULL on
this statement:
subjectnum = DLookup("[SubjectNum]", "Patient_Registry", "[SubjectNum]=" &
txtSearch.Value)

It is not getting to the message Subject Number not found if it can't find it.

Do you have any idea of how to fix this?
Thanks for the help

solex said:
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


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

:

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
Click to expand...
Click to expand...
Click to expand...
 
Then that should do the trick! if not there is a problem some where else,
is the field SubjectNum a long field or is it a string field?

pokdbz said:
Yes I did

solex said:
Did you include the check that I supplied in the attached code?

If IsNull(txtSearch.Value) Then Exit Sub

pokdbz said:
Thanks for the help it works now. The only thing is when you enter a value
that is not in the DB it goes to the debugger and says invalid use of
Click to expand...
NULL
on
this statement:
subjectnum = DLookup("[SubjectNum]", "Patient_Registry", "[SubjectNum]=" &
txtSearch.Value)

It is not getting to the message Subject Number not found if it can't
Click to expand...
find
it.
Do you have any idea of how to fix this?
Thanks for the help

:

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
Click to expand...
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


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
Click to expand...
a
person
enters a number txtSearch for the SubjectNum.
When I do find the subject number I need to display the
Click to expand...
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
Click to expand...
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
Click to expand...
that
it
knows to look for that subject number in the text box that was
Click to expand...
entered
by
a
person.

If you could explain this a little better it would help out alot.
thanks

:

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
Click to expand...
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
Click to expand...
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
Click to expand...
Click to expand...
Click to expand...
 
It is a long integer

solex said:
Then that should do the trick! if not there is a problem some where else,
is the field SubjectNum a long field or is it a string field?

pokdbz said:
Yes I did

solex said:
Did you include the check that I supplied in the attached code?

If IsNull(txtSearch.Value) Then Exit Sub

Thanks for the help it works now. The only thing is when you enter a
value
that is not in the DB it goes to the debugger and says invalid use of NULL
on
this statement:
subjectnum = DLookup("[SubjectNum]", "Patient_Registry", "[SubjectNum]=" &
txtSearch.Value)

It is not getting to the message Subject Number not found if it can't find
it.

Do you have any idea of how to fix this?
Thanks for the help

:

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


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

:

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
Click to expand...
Click to expand...
Click to expand...
 
I found the problem:
the If subjectnum > 0 Then statement should be above the DLookup statement.
Thanks for all of your help with this.


pokdbz said:
It is a long integer

solex said:
Then that should do the trick! if not there is a problem some where else,
is the field SubjectNum a long field or is it a string field?

pokdbz said:
Yes I did

:

Did you include the check that I supplied in the attached code?

If IsNull(txtSearch.Value) Then Exit Sub

Thanks for the help it works now. The only thing is when you enter a
value
that is not in the DB it goes to the debugger and says invalid use of NULL
on
this statement:
subjectnum = DLookup("[SubjectNum]", "Patient_Registry", "[SubjectNum]=" &
txtSearch.Value)

It is not getting to the message Subject Number not found if it can't find
it.

Do you have any idea of how to fix this?
Thanks for the help

:

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


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

:

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
Click to expand...
Click to expand...
Click to expand...
 
I need some more help a little bit different searching help

I setup a query called NameQuery which as fields of:
expr1: which is a persons last name then first name
SubjectNum: subject number

I have a combo box cboSearch which us using the query NameQuery to get the
persons last name then first name.

The code below is what I used to have just to search by a SubjectNum in the
table Patient_Registry

What I would like to do is when a persons name is selected in the cboSearch
and a button is pushed it then displays the persons information from the
Patient_Registry. How can I modify the code below to do this, or do I have
to start over completely.

Thanks for the help

Private Sub Search_Click()
Dim subjectnum As Long

If IsNull(txtSearch.Value) Or (DLookup("[SubjectNum]",
"Patient_Registry", "[SubjectNum]=" & txtSearch.Value)) <> 0 Then 'Exit Sub
' checks to see if random number is duplicated

subjectnum = DLookup("[SubjectNum]", "Patient_Registry",
"[SubjectNum]=" & txtSearch.Value)
' Make sure the entered subjectnum exists in the database

' Move the record pointer to the entered subject num
Me.Filter = "[SubjectNum] = " & subjectnum
Me.FilterOn = True
Me.Refresh

txtSearch.Value = 1
AddPatient.Caption = "Save Patient's Information"

Else
If txtSearch.Value <> 0 Then
MsgBox ("Subject Number not found!")
Else
txtSearch.Value = 0
End If
End If

End Sub


solex said:
Then that should do the trick! if not there is a problem some where else,
is the field SubjectNum a long field or is it a string field?

pokdbz said:
Yes I did

solex said:
Did you include the check that I supplied in the attached code?

If IsNull(txtSearch.Value) Then Exit Sub

Thanks for the help it works now. The only thing is when you enter a
value
that is not in the DB it goes to the debugger and says invalid use of NULL
on
this statement:
subjectnum = DLookup("[SubjectNum]", "Patient_Registry", "[SubjectNum]=" &
txtSearch.Value)

It is not getting to the message Subject Number not found if it can't find
it.

Do you have any idea of how to fix this?
Thanks for the help

:

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


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

:

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
Click to expand...
Click to expand...
Click to expand...
 
What I would like to do is when a persons name is selected in the
cboSearch and a button is pushed it then displays the persons
information from the Patient_Registry. How can I modify the code
below to do this, or do I have to start over completely.
Click to expand...

Answered above in the original thread.

Some of us have jobs to go to during the day....


Tim F
 
Back
Top