Search code examples
ms-accesstabsms-access-2007subforms

Unique lab entry populated across multiple tabs

I am creating a DB from scratch using my 2 weeks of experience with MS Access 2007 (what the hospital has...) in order to track research patients. The basic flow of information will be such that from a start screen, the person can either enter a new patient or find an old patient (each from a button) - Got those parts... (They go to a Patient Demographics form)

Upon finding the patient, they can either select a previous visit from a combo box (got that part) or create a new visit (from a button that then pops-up an addition form for the combo box) - Got that part... (They go to the Visit Form)

This is where my problem is. Once on the Visit Form, we need to be able to generate Lab entries (with each lab entry having multiple tables to store the data). I have created the form ( Patient info area and 12 tabs each with different subforms in datasheet view (except one)) and I can get current 'fake' results that I entered in the tables to come up in the tabs. My problem lies in getting it so that when we enter a new lab date in any one of the tabs (which DOES generate a new unique LabID) to have it populate that date across the other 11 tabs (all associated with that unique LabID) and record in each base table. If the Visit has no labs associated with it, my subforms should be just blank datasheets with the ability to put in a new labID. Otherwise if we're already in a visit, the subform datasheets should display all previous unique LabID's and values associated with that visit AND have a line to enter a new lab incident.
Oh yeah.... We also have to be able to go back into those other lab records and edit/add to them as results become available (pathology takes much longer than others - etc.). However if the LabID and associated date populates across each of the forms in all 12 tabs there should be a blank space waiting for that info when it becomes available. So that might not be much of an issue.

Essentially a Unique Patient will generate Mulitiple Unique Visits --> Each Visit can generate 1 or more Unique LabID's which are then associated with MANY lab data points (all unique TO THAT Lab ID). Confused yet? If I've got it right in my head, it's a one to many to many to one relationship?

Here's part of my relations table:

Here's the Visits Form:

For each of the subforms in the tabs of the Visits Form I have the 'Link master Fields' set to VisitID, and the 'Link Child Fields' set to Lab ID. I have managed to do all of this with a surprising little amount of VBA coding, but here's the coding that gets me from the combo box on the patient demographics page: 

Private Sub VisitCBO_Change()
    Dim ptWHERE As String
    ptWHERE = "[VisitID] = " & Me.VisitCBO.Value
    DoCmd.OpenForm "NewVisitsF", acNormal, , ptWHERE, acFormEdit
    VisitCBO = Null
End Sub

As of right now, there is no code in my builder for the visits page. The subforms are based on queries pulling from the lab group tables and the labID tables.

I have been scouring the web and the Access 2007 Bible for answers for a bout a week, but I can admit when I'm WAY over my head and stuck... Specialists, please help! - I can provide a link to the DB if needed.

Solution

  • Is VisitsT your parent form and each of your subforms somehow go through an LabsT form. Your parent/child should link your main VisitsT data to each of your subforms, but right now it looks like you're going through LabsT and linking the wrong field. From your join relationships, it looks like LabID = LabID (parent/child), however you don't have them linked. Each of your subtables should have a unique key (perhaps LABID and VISITS) and it looks like you tried to do this with that middle LabsT form. This info should be repeated on every subform. Then your parent/child can be on VisitID only.