Searching for record using query

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

Guest

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

I don't really follow the program as some of the comments do not seem to
correspond to the code. Still, if I understand correctly, this is the
sort of approach I think you are getting at:


' validate user input systematically
If isnull(txtSearch) then
' nothing entered
MsgBox "Try entering something to search for

'
' this may be overkill, but it's better to be safe than
' sorry
elseif not isnumeric(txtSearch) Then
' illegal search
MsgBox "You must enter proper number!"

'
' dcount probably works better here because it is bound
' to return a number so you don't have to mess about with
' checking null values returned by DLookup(...)
elseif 0 = dcount("*", "patient_registry", _
"subjectnum = " & txtSearch) then
' no existing record
MsgBox "No, it's not there at all"

else
' okay, there is a record, set the form filter
with me
' haven't checked the syntax because I hardly ever use
' filter properties
.filter = "Subjectnum = " & txtSearch
.filteron = true
.refresh
end with

' signal a successful search to the user
AddPatient.Caption = "Save patient"

end if

Hope that helps


Tim F
 
I am not really understanding what you are saying also. Let me try to
clarify what I would like. The code that was provided was used to search for
a record using a SubjectNum. The code that I provided works fine for that.
What I am trying to do now is change it around a little or write new code.

I have a Query that puts a persons last name and first name in the same
field. Also in the query there is a field that is the corresponding
SubjectNum for the person.

What I would like to do is have a combo box which I already have with the
patients Last name and first name like Doe John already in it. The combo box
is setup fine which is cboSearch. What I want to do is search forthe
patients name then with the corresponding Subject number display the persons
information from the table Patient_Registry which also has a persons
SubjectNum in it.

I hope this help.
 
What I would like to do is have a combo box which I already have with
the patients Last name and first name like Doe John already in it.
The combo box is setup fine which is cboSearch. What I want to do is
search forthe patients name then with the corresponding Subject number
display the persons information from the table Patient_Registry which
also has a persons SubjectNum in it.
Click to expand...

The code you posted did nothing like this. There was no (apparent)
reference to any query, any firstname or lastname fields, and no
displaying. You had a txtSearch (presumably a TextBox) and were setting
the Filter property of a form.

Still, here is my guess on what you may want to do:

' validate the input
If cboSearch.ListIndex =-1 Then
' no selection
msgbox "Try clicking the combo, silly"
Exit sub

End if

' look for existing records with the same first and last names. We'll
' copy the format presumably used in the cbo box to avoid all the
' mucking about with Instr() and so on
'
strWhere = "LastName & "", "" & FirstName = """ & cboSearch & """"

' this is for debugging: lose it once everything is working
MsgBox strWhere, "Looking for record"

' and count them up: could be none or one or many
wCount = DCount("*", "Patient_Registry", strWhere)

Select Case wCount
Case 0
' no existing records
WarnUserOfNoRecords

Case 1
' exactly one record: display it
DisplayOneRecord

Case Else
' multiple people with same name: do
' something else
OfferUserChoiceOfRecordsToDisplay

End Case

I'm afraid I still don't know where your query fits in, but I hope that
helps a bit.

Tim F
 
I think that is what I was looking for. Sorry about not clearly defining
what I wanted or had. Thanks for the help
 
I have another question. The FirstName and LastName have a SubjectNum. How
do I get the filter to display the patients information? Can I use the
FirstName and LastName to display it or can I use the SubjectNum. The code
below has the SubjectNum in it but I don't think that it is pulling the
SubjectNum because in the debugger it has 0 everytime.

How do I get it to use the SubjectNum? Or use the FirstName and LastName to
filter it onto the page?

Thanks for the help

Dim strWhere As String
Dim subjectnum As Long
Dim wCount As Integer

If cboSearch.ListIndex = -1 Then
' no selection
MsgBox "Try clicking the combo, silly"
Exit Sub

End If


strWhere = "LastName & "" "" & FirstName = """ & cboSearch & """"

wCount = DCount("*", "Patient_Registry", strWhere)

Select Case wCount
Case 0
MsgBox ("Patient not found")

Case 1
MsgBox ("found: " & subjectnum & "")
Me.Filter = "[SubjectNum] = " & subjectnum
Me.FilterOn = True
Me.Refresh
Case Else
' multiple people with same name: do
' something else

MsgBox ("Many")
End Select
 
The FirstName and LastName have a SubjectNum. How
do I get the filter to display the patients information?
Click to expand...

It really depends what you want from the "display"...

You could have a form based on the Patient_Registry table, and set its
..Filter property to "SubectNum = 1093" and I thought this was what you
were doing in the first code example.

You could push the values onto a form using a Label or an unbound
Textbox

You could really make the user take notice by putting up a Message Box

' make sure the row exists first, or else trap the NULL
' error somehow!
dwSN = DLookUp("SubjectNum", "PatientRegistry", strWhere)

' using a label
lblSubjectNum.Caption = Format(dwSN, "000000")

' using a text box
txtSubjectNum.Value = Format(dwSN, "000000")

' using a message box with user feedback...
wAns = MsgBox( "This record was found: " & format(dwSN, "000") & _
"Is that the right one?", _
"Searching", _
vbYesNo)

if wAns = vbYes Then
' etc


The right answer really depends on your context. Hope that helps a bit


Tim F
 
Yes the filter properties was SubjectNum and that is what I was doing in the
first example. The DLookUp that you provided was what I was looking for. I
think that I am set now. I really appreciate all of the help.
Thanks
 
Yes the filter properties was SubjectNum and that is what I was doing
in the first example. The DLookUp that you provided was what I was
looking for.
Click to expand...

Glad it helped

All the best


Tim F
 
Back
Top