Trying to create 2 similar records using a form and 2 subforms. (1 Viewer)

tcneo

Member
Local time
Today, 20:18
Joined
Dec 7, 2021
Messages
68
Hi everyone,

Before I ask my question, the following is a bit of context.

I'm tasked with creating a database that tracks the testing of samples (tasks) in the QC lab of a factory. The overall database will be managing the availability of lab analysts, analytical methods, test records, equipment availability, etc. A big one.

But first, I'm trying to create a form where the lab planner can create task.

This is rather straightforward. A table with Task_ID as primary, Analyst_ID, instrument_ID Test_method_ID as foreign keys and a date field and a text field for remarks.

The twist comes here. Some (but not all) tasks actually comes in 2 parts - Part A and Part B. Most of the information for both Tasks A & B will be same except for different dates (B takes place several days after A).

Now, how can I create a form that allows me to generate 2 records at once, with only different dates?

I have attached an example of what I think can achieve this. There is a Table with ID as primary and 2 fields, Info1 and Info2. In my input form, I have created 2 subforms, Table1 Left subform and Table1 Right subform. My plan is that my user can fill up Info1 field in the Left subform and it will auto-populate the corresponding Info1 field in the Right subform. Info2 would represent the dates which are meant to be different for A & B. When i close the form, 2 records will be created. Viola! Or so I thought.

Right now, I can't get the textbox for Info1 in the Right subform to "pull" the information typed in the textbox for Info1 in the Left subform. How can I make this happen.

Or is there a better way to implement this?

Thanks in advance!
 

Attachments

  • leftrighttest.accdb
    956 KB · Views: 299

June7

AWF VIP
Local time
Today, 04:18
Joined
Mar 9, 2014
Messages
4,459
Both forms are looking at same record. Edit in one will reflect in the other after edit is committed to table. Record edits are committed when: 1) close form/table/query; or 2) move to another record or another form (in this case, from left form to right form); or 3) run code to save.

Will records for both parts be entered after data for both is collected?

Is Info1 supposed to be a common identifier that will pair up Parts A and B records?

If Info2 is supposed to be a date value, why is field not holding dates?

I see your options as:
1. normal data entry to input two records in a single form
2. unbound forms and bunch of VBA to save records
3. denormalize data structure and put both parts into a single record

Advise not to use spaces in naming convention.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 20:18
Joined
May 7, 2009
Messages
16,823
see if you will like this change.
 

Attachments

  • leftrighttest.accdb
    1.1 MB · Views: 314

oleronesoftwares

Passionate Learner
Local time
Today, 05:18
Joined
Sep 22, 2014
Messages
1,140
Done.
Check the control source for the right form to see how it was done.
 

Attachments

  • leftrighttest.accdb
    996 KB · Views: 313

oleronesoftwares

Passionate Learner
Local time
Today, 05:18
Joined
Sep 22, 2014
Messages
1,140
Hi, in the attachment i sent,you might have to close and re-open the form to see the changes reflected on the right subform.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 08:18
Joined
Feb 19, 2002
Messages
36,889
@tcneo I don't think any of us understand what you want to do including the two who created solutions.
If tests can be done in multiple parts, you need two tables. The parent table defines the test and the child table records the results. Keep in mind that once you have more than one, you have many and many requires a child table.

Normally we do not create empty records ahead of time. But, we might if the records are always required. So, in your case, we might write code to run an append query in the AfterInsert event of the parent record. The append query can append rows with only the dates and leave the results fields to be filled. OR, the results rows can be entered at the time the results are known. That means you won't be creating empty records which might never be used. We would have had a better idea if your proposed solution had actually included real data fields.

You have assumed a solution and asked us how to implement it. Two people didn't question your assumption and just provided solutions. Two other people didn't accept your assumption and asked additional questions hoping to determine if this is actually the best solution. Even if the best solution is to actually append a duplicate row, it would not be done the way you are envisioning it. arnel created something closer to what you probably need.
 

tcneo

Member
Local time
Today, 20:18
Joined
Dec 7, 2021
Messages
68
Both forms are looking at same record. Edit in one will reflect in the other after edit is committed to table. Record edits are committed when: 1) close form/table/query; or 2) move to another record or another form (in this case, from left form to right form); or 3) run code to save.

Will records for both parts be entered after data for both is collected?

Is Info1 supposed to be a common identifier that will pair up Parts A and B records?

If Info2 is supposed to be a date value, why is field not holding dates?

I see your options as:
1. normal data entry to input two records in a single form
2. unbound forms and bunch of VBA to save records
3. denormalize data structure and put both parts into a single record

Advise not to use spaces in naming convention.
Dear June7,

Thanks for pointing out the issue with both forms. I had originally set them in Data Entry mode. But it was turned off in the version that I had uploaded.

I have uploaded a revised copy of the file where both subforms are in Data Entry mode. Yes, Info 2 was supposed to represent the date but since this leftright file is not the actual database i'm working on (i can't share that yet), i create a new database to try to illustrate what i'm working on.

Thanks for reminding me not to have spaces in the field names. I have amended them to Test-Info and Test-Date in my example.

Ideally, what my user wants is that after keying in Test-Info and Test-Date on the left subform, 2 records will be entered into the table. The first record will have the information as entered. The second record will have Test-Info and Test-Date will be 3-5 days later than the Test-Date in the left subform.

e.g. user keys "Acme Test 3224" in Test-Info and 25-Dec-2021 in Test-Date. 2 records in the table will be created. The first record will be as mentioned. The second one will be "Acme Test 3224" and 28-Dec-2021. It has to be be 2 records cos for later processing, both records represent 2 different tests. And the user doesn't want to key it in twice.

Lastly, I don't understand what you mean by option 1 & 3. And I do not know much VBA to try option 2.
 

tcneo

Member
Local time
Today, 20:18
Joined
Dec 7, 2021
Messages
68
@tcneo I don't think any of us understand what you want to do including the two who created solutions.
If tests can be done in multiple parts, you need two tables. The parent table defines the test and the child table records the results. Keep in mind that once you have more than one, you have many and many requires a child table.

Normally we do not create empty records ahead of time. But, we might if the records are always required. So, in your case, we might write code to run an append query in the AfterInsert event of the parent record. The append query can append rows with only the dates and leave the results fields to be filled. OR, the results rows can be entered at the time the results are known. That means you won't be creating empty records which might never be used. We would have had a better idea if your proposed solution had actually included real data fields.

You have assumed a solution and asked us how to implement it. Two people didn't question your assumption and just provided solutions. Two other people didn't accept your assumption and asked additional questions hoping to determine if this is actually the best solution. Even if the best solution is to actually append a duplicate row, it would not be done the way you are envisioning it. arnel created something closer to what you probably need.
Dear Pat,

Thanks for your reply.

Ideally, what my user wants is that after keying in Test-Info and Test-Date on the left subform, 2 records will be entered into the table. The first record will have the information as entered. The second record will have Test-Info and Test-Date will be 3-5 days later than the Test-Date in the left subform.

e.g. user keys "Acme Test 3224" in Test-Info and 25-Dec-2021 in Test-Date. 2 records in the table will be created. The first record will be as mentioned. The second one will be "Acme Test 3224" and 28-Dec-2021. It has to be be 2 records cos for later processing, both records represent 2 different tests. And the user doesn't want to key it in twice.

You are right to say that I assumed a solution and asked how to implement it. I have only started working on developing this database last month (after learning Access 10 years ago). The solution I'm asking for may not be the best or most ideal, i concede. It is what I can think of for now given my lack of experience.

I'll look at what everyone has contributed, try to understand it and get back to everyone soon.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 08:18
Joined
Feb 19, 2002
Messages
36,889
@tcneo If you read #6 again, I told you how to accomplish the goal. You would not use two side by side forms, you would use one form and an append query after the first row is entered to copy the first row, with a new date and append t.

Your mockup is out of context so we can't tell whether you need a parent table with child tables but I am leaning that way. The parent table would hold the common elements and the child table would hold the different elements, which seems to only be the date and I presume the results of the test.
 

June7

AWF VIP
Local time
Today, 04:18
Joined
Mar 9, 2014
Messages
4,459
Well, I should have also warned not to use punctuation/special characters either. Underscore is only exception.
 

tcneo

Member
Local time
Today, 20:18
Joined
Dec 7, 2021
Messages
68
Well, I should have also warned not to use punctuation/special characters either. Underscore is only exception.
thanks for the warning. i'll keep that in mind.
 

tcneo

Member
Local time
Today, 20:18
Joined
Dec 7, 2021
Messages
68
@tcneo If you read #6 again, I told you how to accomplish the goal. You would not use two side by side forms, you would use one form and an append query after the first row is entered to copy the first row, with a new date and append t.

Your mockup is out of context so we can't tell whether you need a parent table with child tables but I am leaning that way. The parent table would hold the common elements and the child table would hold the different elements, which seems to only be the date and I presume the results of the test.
Dear Pat,

Thanks. I get what you are saying. I'll give it a try.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 08:18
Joined
Feb 19, 2002
Messages
36,889
We still don't know how to structure your tables correctly because your example was bogus but good luck.
 

Users who are viewing this thread

Top Bottom