This
subjectnum = DLookup("[ScreenID]", "Patient_Registry", _
"[ScreenID]=" & txtSearchNum.Value & ") AND " _
& "([StudyTitle]=" & cboSearch2.Value & ")")
doesn't make sense to me.
If I'm reading it right, it looks up the value of ScreenId for the first
record in Patient_Registry where the values of ScreenID and StudyTitle
match the values in the textbox and combobox. In other words, you're
looking up something you already know.
What are you actually trying to achieve?
Also, which line in your code is producing the error, and what are the
error number and error message?
Well it is going into the debugger saying there is a runtime-error. Here are
the actual fields and talbes that I am using. Do you have any idea what the
problem is? Also how doe I put it in to filter it onto the screen?
Thank you for your help you have been very patient with me. I really
appreciate it.
subjectnum = DLookup("[ScreenID]", "Patient_Registry", "[ScreenID]=" &
txtSearchNum.Value & ") AND " & "([StudyTitle]=" & cboSearch2.Value & ")")
Me.Filter = "[ScreenID] = " & subjectnum
Me.FilterOn = True
Me.Refresh
John Nurick said:
That or something similar. If geneticIDnumber or CRFNumber are text
fields and not number fields, you'll need to set them off with
apostrophes, e.g. this assumes CRFNumber is actually a text field:
subjectnum = DLookup("[geneticIDnumber]", "Patient_Registry",
"[geneticIDnumber]=" & txtSearchNum.Value & ") AND " & "([CRFNumber]='"
& txtSomeOther.Value & "')")
But the real test is, does it work?
On Mon, 10 Jan 2005 06:31:03 -0800, pokdbz
Is this what the Dlookup should be like?
subjectnum = DLookup("[geneticIDnumber]", "Patient_Registry",
"[geneticIDnumber]=" & txtSearchNum.Value & ") AND " & "([CRFNumber]=" &
txtSomeOther.Value & ")")
:
If I understand right, just take the "where" argument from the DCount()
expression I posted (i.e. from
"([geneticIDnumber]
to the last " before
) > 0 Then
and use this in your call to DLookup(). You'll need to replace my
"txtsomeother" with the actual name of your second text box.
On Wed, 5 Jan 2005 06:05:06 -0800, pokdbz
I have to fields geneticIDnumber and studyCRFnumber. What I have is 2
seperate text boxes for someone to enter this information. So what ever
someone types in it has to find the exact match for both and display that
record. Does that make sense? I have it doing it for 1 just need to know
how to add the second criteria.
Thanks
:
I don't understand quite what you're trying to do, but if you want to
check for the existence of records that meet two criteria you can use
DCount(), something like
If IsNull(txtSearchNum.Value) Or _
DCount("*", "Patient_Registry", _
"([geneticIDnumber]=" & txtSearchNum.Value & ") AND " _
& "([CRFNumber]=" & txtSomeOther.Value & ")") > 0 Then
(DLookup("[geneticIDnumber]",
"Patient_Registry", "[geneticIDnumber]=" & txtSearchNum.Value)) <> 0 Then
On Tue, 4 Jan 2005 07:03:08 -0800, pokdbz
I have the code below which searches for a geneticIDnumber (1 field) how do
make it so it seaches for both fields (say geneiticIdnumber and CRFnumber) at
the same time and provide 1 result from 2 text fields.
If IsNull(txtSearchNum.Value) Or (DLookup("[geneticIDnumber]",
"Patient_Registry", "[geneticIDnumber]=" & txtSearchNum.Value)) <> 0 Then
'Exit Sub
subjectnum = DLookup("[geneticIDnumber]", "Patient_Registry",
"[geneticIDnumber]=" & txtSearchNum.Value)
' Make sure the entered subjectnum exists in the database
' Move the record pointer to the entered subject num
Me.Filter = "[geneticIDnumber] = " & subjectnum
Me.FilterOn = True
Me.Refresh
txtSearch.Value = 1
AddPatient.Caption = "Save Patient's Information"
Sex.SetFocus
Else
MsgBox "Genetic ID number not found."
End If
