How do I create 2 forms, with form 2 selectively populating data from form 1 based on

[Amian]

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!

[zmbd]

Amian

That's going to take quite a while to weed thru!

On first pass, there appears to be multiple questions. To help keep threads from becoming too jumbled we ask for a single question per thread. If you would please, take a deep breath, go back thru your first post, and select the most pressing issue you have - we won’t ignore the rest (intentionally anyway (^_-) ), we just need to select a path to follow.
There's already a lot of information in the first post, so just a two sentence question/statement should do.

Because it sounds like you are in the same boat with a fellow member, you may also benefit from the information I gave in the last paragraph here: POST#4 Need help... there is a link to a very basic Access tutorial and links within Bytes.com that will help you.

[zmbd]

You didn't mention which version of Access you are working with... each has their own quirks and you'll find that most of us have upgraded to ACC2007/2010/2013.
You have several questions running there and a lot of tiny things that need to be tweaked with your database design. You need to find a good tutorial site and read thru: > Database Normalization and Table Structures.

Based on that normalization concept; here is how I would set up your database table structure:
For the following:
Each starts with the table name.
field names within the table are in square brackets []
parameters for the field follow the field name either directly or below.
[*_PK] designates the primary key.
[*_FK_*] designates a foreign key to the indicated table, usually one to many (1:M) or as designated.


tbl_patient
[patient_pk] autonumber
[patient_fname] text(25) required
[patient_lname] text(25) required
[patient_fx_sex] numeric long (because this is medical, sex may play a part due to genetic roles - useing a relationship here to limit valid entry)
[patient_FK_race] numeric long (because this is medical, race may play a part due to genetic role.)
[patient_...] additional fields for unchanging patient only information.

tbl_staff
[staff_pk] autonumber
[staff_fname] text(25) required
[staff_lname] text(25) required
[staff_...] additional fields for unchanging staff only information.

tbl_diagnostic
[diagnostic_pk] autonumber
[diagnostic_name] text(25) required
[diagnostic_lowr ange] numeric - if decimal then double else long
[diagnostic_high range] numeric - if decimal then double else long
[diagnostic_...] additional fields for unchanging diagnostic information only

tbl_specimen
[specimen_pk] autonumber
[specimen_fk_pat ient] numeric long 1:M with patient
[specimen_fk_pri ority] numeric long 1:M with priority - using a relationship here to limit valid entry)
[specimen_dateti mereceived] date/time
[specimen_fk_sta ff] numeric long 1:M with staff (you should know who received the specimen)

tbl_labresults
[labresults_pk] autonumber
[labresults_fk_s pecimen] numeric long
[labresults_fk_d iagnostic] numeric long
[labresults_fk_s taff_assignedto] numeric long
[labresults_fk_s taff_reporting] numeric long
[labresults_fk_s taff_reviewed] numeric long
[labresults_repo rteddatetime] date/time
[labresults_revi eweddatetime] date/time
[labresults_resu lt] numeric double

tbl_diagnostic and tbl_labresults assume that the results are all quantifiable as a numeric value.

You could do this with a tabbed form or a single form for most things.

You say you have no programming experience; thus, I'd go with the ParentForm(Subf orm1(Sub_Subfor m)) approach as it should help minimize the code you will need.

Parent form:
Would have the patient id, first, and last names.
This could be either as a single record or datatable display.
You could have a details form popup should you need to add a new patient to the database. This could be via a on_click event from a button or control, or you could do this via an on current event checking for new record and trapping the normal method to popup the details form. I would have all of the fields set for an on click event to bring up the details form.
In anycase, parent form would be linked to the first child form subform, "CF1" on the [patient_pk] and [specimen_fk_pat ient] fields so that you only see the patient specfic specimens.
You could even use some things from the following as weo:

• Microsoft Access / VBA Insights Sitemap
• 45.How to select Records in an Access Subform from a Combo Box
  
• 46.Cascaded Form Filtering
• 55.Multiple Combobox Filter with Clear Option
• 168.Example Filtering on a Form.

Now the trick, the second subform, this is a subform on CF1, call this grandchild form GC1. It has the tbl_labresults as the ultimate record source and is linked to CF1 on [specimen_pk] and [labresults_fk_s pecimen].

All of the forms will be driven by queries so that we can pull all of the nice human readable stuff into lookup fields (I don't use lookup fields at the table level)

So the work flow goes like this:
Either by the built in search, or by a control on the parent form the patient id is found, or the patient is found by means of first and last name. You may need additional field for something like driver's liscence. Stay away from SSN and other such sensitive information if possible.
If the patient id is not found or the person cannot be located by other id, then a new patient record is created via the detail form.
Since this will be parent current record, all of the currently logged specimens should show in CF1. Either enter a new specimen or select one. I would use the PK field as your specimen id; however, you can add a field to the table that has the unique id value; however, that either means more VBA or some other means of creating ID numbers.
Once a specimen has been selected/entered then the labresults should show in GC1 for any diagnostics scheduled. New tests can be entered into the GC1 for the currently selected specimen.
You can then develop queries to pull information by patient, specimen, etc...

The worst VBA will be traping the on_current event for newrecord and the on_click event for the parent form to bring up the details form. Strickly speaking, you don't need the details form; however, I think this main data entry form would become very busy.

Sorry, I don't have the time resources available to design you an example database; however, I hope this will give you some ideas on how to proceed with your project.