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

--
Larry Linson, Microsoft Office Access MVP
Co-author: "Microsoft Access Small Business Solutions", published by Wiley
Access newsgroup support is alive and well in USENET
comp.databases.ms-access



"Rob M" <(E-Mail Removed)> wrote in message
news:a9440cff-a63f-4c6d-8a59-(E-Mail Removed)...
> 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

Rob M <(E-Mail Removed)> wrote in
news:a9440cff-a63f-4c6d-8a59-(E-Mail Removed)
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 & ")"


--
Bob Q.
PA is y I've altered my address.

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



> 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 & ")"
>
>
> --
> Bob Q.
> PA is y I've altered my address.