Hello!

(I originally posted this using a vet database analogy, because it
looks to me like the same setup as what I really need the database
for. Anyway, I hope I have provided enough detail here about the real
use of the database that the errors I am making are really obvious. I
tend to over-analogize.)


At our daily Quality Review Program (QRP), we review applicant records
for discrepancies. We then turn that list of discrepancies over to be
corrected. The next day, we do the same thing to another group of
applicant records. On same days, we review 10 records; on others, we
review 200. So, I thought that we can database/userform this instead
of using individual Excel workbooks by day that we keep for a year.
(One database file rather than 365 separate workbooks or one workbook
with 365 tabs.)

So, we have a QRP table:
tblQRP with QRPID (autonumber) and QRPDate (Date)

We have an applicant table:
tblApplicant with ApplicantID (Autonumber), ApplicantSSN (Text),
ApplicantLName (Text), QRPID (Number)

We have a Discrepancy table:

tblDiscrepancy with DiscrepancyID (Autonumber), Discrepancy (Text) and
ApplicantID (Number)

There is a mainform with tblQRP as its Record Source and the QRPDate
field and two subforms:

Subform control: subfrmApplicant (name) and Source Object
(frmApplicant) with LMF (QRPID) and LCF (QRPID)
Form: Record Source (tblApplicant)
Three textbox controls: ApplicantSSN, ApplicantLName, and ApplicantID
(Control Source is the same as the Name.)

Subform control: subfrmDiscrepancy (name) and Source Object
(frmDiscrepancy) with LMF (QRPID) and LCF (ApplicantID)
Form: Record Source (SELECT tblDiscrepancy.* FROM tblDiscrepancy ORDER
BY [tblDiscrepancy].Discrepancy DESC
Two textbox controls: DiscrepancyID and Discrepancy (Control Source is
the same as the Name)

What I would like to happen is to enter the QRP date in the mainform,
then enter the applicant's SSN and name into Subform 1, and then enter
the discrepancies for that applicant into Subform 2. Back to subform 1
for the second applicant for the same date, and then the subform 2
should be blank. It works mostly, but it is still showing the
discrepancies from the first applicant instead of clearing with each
applicant.

Looking at this setup, what am I doing wrong?

VR/Lost