Cycle through records to find a value

[Rob M]

Hi everyone,

I'm wondering whether someone can help with this.

I have a patient database that handles referrals from multiple
clinics. On main patient form, that contains a Referrals subform, I'm
trying to flag whether a referral to a specific clinic has been made.
The VBA code I have so far looks something like this:

--
Dim PatientIDTemp as integer
Dim ClinicFlag as boolean

PatientIDTemp = Me![PatientID]

NumberOfReferrals = DCount("[ClinicID]", "Referrals", "[PatientID] = "
& PatientIDTemp)

For b = 1 To NumberOfReferrals
(now I want to cycle through all the referrals for that patient, and
simply flag if the ClinicID = 8 (e.g., ClinicFlag = True)
Next b
--

It's easy enough to flag when there's only one referral (e.g., If
Referrals![ClinicID] = X Then ClinicFlag = True). The problem is that
I'm not sure how to cycle through all of the referrals to find the
particular one in question. Can anyone assist with coding this? I
may be way off base with what I have so far. I'd be really grateful
for any help.

Many thanks,
Rob

 

[Access Developer]

Why don't you include the Clinic in the Link Master Fields and Link Child
Fields of the Subform Control, and save yourself a lot of trouble... or, if
you want to show all referrals, but with particular ones checked, you can
use a Calculated Field to return the Boolean value that would set a Check
Box,

 

[Bob Quintal]

m:

Hi everyone,

I'm wondering whether someone can help with this.

I have a patient database that handles referrals from multiple
clinics. On main patient form, that contains a Referrals subform,
I'm trying to flag whether a referral to a specific clinic has
been made. The VBA code I have so far looks something like this:

--
Dim PatientIDTemp as integer
Dim ClinicFlag as boolean

PatientIDTemp = Me![PatientID]

NumberOfReferrals = DCount("[ClinicID]", "Referrals", "[PatientID]
= " & PatientIDTemp)

For b = 1 To NumberOfReferrals
(now I want to cycle through all the referrals for that
patient, and
simply flag if the ClinicID = 8 (e.g., ClinicFlag = True)
Next b
--

It's easy enough to flag when there's only one referral (e.g., If
Referrals![ClinicID] = X Then ClinicFlag = True). The problem is
that I'm not sure how to cycle through all of the referrals to
find the particular one in question. Can anyone assist with
coding this? I may be way off base with what I have so far. I'd
be really grateful for any help.

Many thanks,
Rob

why not expand the dLookup to count your ClinicID so that if it's
there your answer is 1, else it's 0

NumberOfReferrals = DCount("[ClinicID]", "Referrals", _
"[PatientID] = "& PatientIDTemp _
& " AND [ClinicID] = " & X & ")"

 

[Rob M]

Thank you Bob Q, this suggestion worked perfectly!! I really
appreciate your help.