Cycle through records to find a value


R

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
 
Ad

Advertisements

A

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

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 & ")"
 
Ad

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top