Prev: Subform Causes Main Form to show same record Multiple times
Next: One listbox works but the others don't
From: John W. Vinson on 27 Mar 2010 20:10 On Sat, 27 Mar 2010 16:18:35 -0700 (PDT), Lostguy <cpocpo(a)mail.com> wrote: >Sir, > >Yes, I missed that one. > >tblVisit >VisitIDpk, autonumber >VisitDate, date/time >DogIDfk, number > >Then I linked all the IDpks to their respective IDfks. > >Can you see anything else missing? > >VR/Lost So there's one and only one dog on any visitdate? Surely not. What are the Recordsources of your form, subform, and sub-subform? What are their master/child link fields? -- John W. Vinson [MVP]
From: Lostguy on 27 Mar 2010 21:21 Sir, Hopefully, these tables are the correct base to build on: tblDog DogIDpk, Autonumber DogName, Text tblDogCondition DogIDfk, number ConditionIDfk, number tblCondition ConditionIDpk, Autonumber ConditionDesc, text tblDogVisit tblDogVisitpk, autonumber DogIDfk, number VisitIDfk tblVisit VisitIDpk, Autonumber VisitDate, Date/Time I am working on the Record Source and Master/Child portion of your response. Thanks for your time! VR/Lost
From: John W. Vinson on 28 Mar 2010 16:11 On Sat, 27 Mar 2010 18:21:04 -0700 (PDT), Lostguy <cpocpo(a)mail.com> wrote: >tblDogVisit >tblDogVisitpk, autonumber >DogIDfk, number >VisitIDfk > >tblVisit >VisitIDpk, Autonumber >VisitDate, Date/Time > >I am working on the Record Source and Master/Child portion of your >response. Thanks for your time! I'd simply put a date/time field in tblDogVisit rather than having an entire table and form containing nothing but a date. You don't need to keep any information that is specific to March 29, as a date, independent of any dogs or visits. I think you're overnormalizing here! You can certainly base a form on a query selecting a date or a range of dates. -- John W. Vinson [MVP]
From: Lostguy on 28 Mar 2010 20:47 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
First
|
Prev
|
Pages: 1 2 Prev: Subform Causes Main Form to show same record Multiple times Next: One listbox works but the others don't |