Solved Data Entry Form W/Subform Creates extra records? (1 Viewer)

danbl

Registered User.
Local time
Today, 10:42
Joined
Mar 27, 2006
Messages
262
I have looked at the fields in the Perioperative Data table and don't see the repetitive fields you refer to. I can see were field descriptions may be misleading. I am not getting how adding the field Operative Type would solve the problem? :unsure: The table has three district sections with the fields; Preoperative, Interoperative, and Postoperative that are Yes/No fields for the purpose of verifying data entry.
 

June7

AWF VIP
Local time
Today, 05:12
Joined
Mar 9, 2014
Messages
5,423
Fields with a number suffix usually indicate repetitive type of data. Example: University1, University2, University3.

Are you saying your 3 distinct sections don't document the same kind of data? PQ1 and PQ8 and PQ15 aren't documenting the same thing? What are they documenting?

The problem is because of 3 subforms bound to same table, you are getting 3 separate records, each of which has many empty fields. My suggestion will still generate 3 records but not with empty fields. If you want to stay with a single record and still have 3 subforms then will need VBA to manage this or 3 tables.
 

danbl

Registered User.
Local time
Today, 10:42
Joined
Mar 27, 2006
Messages
262
Are you saying your 3 distinct sections don't document the same kind of data? The questions are either Yes/No responses and if the question does not apply then the N/A field is selected.

PQ1 and PQ8 and PQ15 aren't documenting the same thing?
NO they are not the same. Each question is defined in the description section of the table. (Some descriptions may need clarification)

What are they documenting? What is documented is whether or not the event (QUESTION) happened or not, or does it not apply for the particular visit.

Could the problem be resolved by making one form to capture all the data?, Or dividing the larger table into three separate tables and then link each table to the associated sub form? (This maybe what you 've been saying all along and I am now just catching on)
 

June7

AWF VIP
Local time
Today, 05:12
Joined
Mar 9, 2014
Messages
5,423
So PQ1 and PQ8 and PQ15 each document a different question? Not the same question asked 3 times?

If you don't want to fully normalize data structure, options have been provided. Restated:

1. single table and single subform instead of 3 and no code required

2. eliminate columns from table and use 3 subforms to create 3 records (but since you says questions are not repeated for each stage this seems not appropriate after all)

3. current table structure with 3 subforms and code to manage input to same record

4. 3 tables and 3 subforms
 

danbl

Registered User.
Local time
Today, 10:42
Joined
Mar 27, 2006
Messages
262
Options 1 & 4 seem the most logical to me as I have no experience with code.
Option 1 - this might be a large form that would require user to have to scroll through.
Option 4 - would let me use the current forms and just fix queries etc. Each table would have a primary key. My question is if this is used what is the best way to link them - Primary / Foreign key? "vid" is the primary key in the Visit table and the foreign key for the others.
 

June7

AWF VIP
Local time
Today, 05:12
Joined
Mar 9, 2014
Messages
5,423
Option 4: Certainly primary and foreign keys. Each of the 3 operative tables would link to Visit with vid.
 

danbl

Registered User.
Local time
Today, 10:42
Joined
Mar 27, 2006
Messages
262
Great ... I got one right :) The visit Id was what I thought I should use. I will work on this and repost later after I test with adding records again.

Many thanks for the help as I learn along the way
 

danbl

Registered User.
Local time
Today, 10:42
Joined
Mar 27, 2006
Messages
262
I have created the new tables / queries / forms. When I open the forms they appear with all the questions as they should. However the preop, interop, and post op forms are not visible in the sub form. ??
 

Attachments

  • formissue.zip
    1.4 MB · Views: 105

June7

AWF VIP
Local time
Today, 05:12
Joined
Mar 9, 2014
Messages
5,423
Don't use queries joining tables as RecordSource. Just reference the tables.
 

danbl

Registered User.
Local time
Today, 10:42
Joined
Mar 27, 2006
Messages
262
This leads me back to one of my earlier questions. Does this mean that the larger Perioperative table should be divided into separate Preop, Interop, and Postop tables with vid as the foreign key in each?
 

June7

AWF VIP
Local time
Today, 05:12
Joined
Mar 9, 2014
Messages
5,423
That was one of the options suggested in order to maintain 3 subforms without complicated VBA.
 

danbl

Registered User.
Local time
Today, 10:42
Joined
Mar 27, 2006
Messages
262
Okay ..... will get this done and give it a try. I am sure I will be back with additional questions :) many thanks
 

June7

AWF VIP
Local time
Today, 05:12
Joined
Mar 9, 2014
Messages
5,423
You created the 3 tables in latest version. Just change the RecordSource of 3 subforms to reference tables.
 

danbl

Registered User.
Local time
Today, 10:42
Joined
Mar 27, 2006
Messages
262
Changes made ...... still creates two records for the Preop, Interop, and postop...:unsure::cry:
 

Attachments

  • Example2.zip
    971.9 KB · Views: 103

June7

AWF VIP
Local time
Today, 05:12
Joined
Mar 9, 2014
Messages
5,423
Do you mean three records?

I am not understanding issue. Each table has 1 record for each visit. This is how it should be with your structure.

The Indicator fields are not needed but if you do want to retain, then use a DefaultValue and Lock checkbox so user can't change or even better, don't have viewable on form.

Why is there no patient ID in t_Visit? There is only a DOB for patient info. This cannot reliably retrieve records for specific patient.
 

danbl

Registered User.
Local time
Today, 10:42
Joined
Mar 27, 2006
Messages
262
If I enter data using the form(s), when you go to the individual tables (preop, interop, postop) there will be two entries for one record entered??

I get what you are saying for the default value ... just didn't do that at this point.
I opened the file I sent you and "vid" is the primary key in the table visits ?? and is the foreign key in the other tables.
 

June7

AWF VIP
Local time
Today, 05:12
Joined
Mar 9, 2014
Messages
5,423
I tested data entry. I get a new record in t_Visit and a new record in each related table. This is what I would expect. What else are you seeing?
 

danbl

Registered User.
Local time
Today, 10:42
Joined
Mar 27, 2006
Messages
262
I am TOTALLY, TOTALLY confused!!!! :unsure::unsure:

When I tested this before sending it to you, I got a duplicate in the tables I mentioned. When I opened it this morning after seeing your reply, I tried it again and as you said I got only one record in each table.

Any thoughts on how or why???
 

danbl

Registered User.
Local time
Today, 10:42
Joined
Mar 27, 2006
Messages
262
It seems that the data entry is working. However I have updated the interop form (frminterOpA) from the original form. When I added it to the data entry form it is not visible on the interop tab. When I added it to the form (frmVisitE) it is visible. How do I fix this???
 

Attachments

  • Example2.zip
    1.1 MB · Views: 101

Users who are viewing this thread

Top Bottom