So I'll explain to you everything that I'm doing and then let you know what I need help with b/c maybe I should be doing this a different way.
I am trying to create a database for all patient samples that come into the lab that I work for. I'm using access to do this. When a sample comes into the lab, the tech has to record all the information on to a paper log. The tech labels it with a unique number (moving forward I will call this the Sample Number), no two samples ever have the same sample number. Then on that log the tech will also record what the testing is being done for and then the methods/equipment/procedures going to be done to do the testing
Ex. patient sample comes in, needs to be tested for Antibody A, and a DNA extraction, PCR and a Lab Screen Class 1 test will all be performed.
So what I have so far is:
Table 1: Patient Sample Information (including demographics of each patient sample, investigation type (Platelet or Antibody), methods/procedures going to be used to test the sample.
Form 1:This is where the techs will actually input all that data (replacing the paper log). 3 sections 1) Patient info 2) what they need to find out 3) How they're going to do it
Table 2: Results for all testing performed
Form 2: This I am currently working on and I'm stuck! Lol!
The sample number is the primary key in both these tables and I've created a 1-1 relationship between them.
THE BIG ISSUE: Form 2!
Form 2 needs to be associated with form 1 to some extent. I need fields from form 1 to be in form 2 but only as read only and some only if the data was entered in form 1, otherwise I dont want the empty visible fields there.
3 Sections for Form 2:
Section 1: Patient info (Want this to autopopulate from form 1)
Section 2: Testing performed (I want only those values to autopopulate if there were selected)
Section 3: Results (This is actual data entry for form 2, all results need to tabulate in Table 2)
I want the tech to be able to enter a sample number and the record for that sample to pop up and sections 1 and 2 will be visible so you know you have the right sample you are entering results for. So display that information and then in section 3, enter the results.
So now, only the data you enter in form 2, I want it to appear in Table 2. I don't want the sections 1 and 2 to repeat in Table 2 since they are already linked with the same sample number.
What I've been able to do:
In form 2 I am able to have fields from Tables 1 and 2 there, but I do not know how to associate them so the data in Table 1 populates that info into Form 2 as visible. Also, I don't know how to do a lookup for the sample number and have that record pop up with the populated data in form 2.
Essentially, when data is entered in form 1 a record is created in Table 1. Now I want that to also create a record in table 2 but the only field in table 2 to have data populate would be the sample number b/c the rest of the fields are for results. so a record with a sample number only and the other fields blank be simultaneously created in table 2 when form 1 is completed.. when form 2 opens I want the tech to be able to enter a sample number (which should already exist in table 2 ) see data from table 1 (somehow associate that data to populate as read only) and then enter results data which gets recorded in Table 2.
I hope this makes sense. I have no programming experience, I am learning a bunch of this stuff as I go, I've done basic coding in my first form, nothing crazy, second form I feel will be crazy just because of how i want everything. It should be possible though right???
Please ask me any questions if you are confused. And thank-you so much! I appreciate any and all insight you have on this.
Also, maybe I need to approach this with subforms?? I made a copy of form 1 and trying to put form 2 as a subform in it, but I still have the issue of seeing everything in form 1. I only want the values that were initially entered in form 1 to populate as visible in the copy of form 1. I would prefer to have 2 separate forms but I don't know what would work best.
Thanks again!