Solved Data Entry Form W/Subform Creates extra records?

[danbl]

The attached database sample is to be a replacement for one currently in use.
All records from the Current database have been imported, the associated queries and reports all function the way they are supposed to. (Validated by comparing reports between the two versions ..... I have only included one months data plus entered two additional records with future dates to show the issue.)
The last step was to validate the data entry function.
The data entry form is made up of a main form, visits, and a sub form with multiple tabs. When this data entry form is used and tables are checked, each record is found in the visit table (t_Visit), the DVT table (t_DVT_Dat), the temperature table (t_temperature_monitoring), but the perioperative data table (t_Perioperative data ) shows each record three times. Why is this happening?
Some of the queries do not seem to pull the data when you check for the records; also when some reports that are generated ae blank (DVT Reports) Why is the data not being pulled?
I am completely stumped as to why this is happening. Can someone tell me why this occurs and how to fix it?

 

[June7]

Exactly which form - frmVisit?

I suspect you are getting 3 records because you have 3 forms bound to the same RecordSource, each entering a record. I would expect similar outcome with the two forms using qryAD as RecordSource. Quick fix is to have 1 Operative form and arrange controls into 3 groups. Otherwise, redesign data structure to allow for multiple records and have another field for OperativeType. Do similar for Pat and Advance Directive. Your table structure is not fully normalized.

Should not include parent table t_Visit in subform RecordSource.

Which queries are not displaying data?

What parameters should I use for testing report?

 

[danbl]

Huuummmm .......

frmVisit is the correct form.

I am far from and experienced Access user; have had lots of assistance with some of this build. I am a bit lost on what you are trying to tell me??? How would I know where to make the changes? What is confusing is that the data appended into the database produces the correct info/reports.

 

[danbl]

Okay ...... at this point I am lost as how to fix this.
1. when this was originally built no one was not aware that number suffixes were a bad thing. Not sure what would happen pulling this to another table
2. what happens when you remove the visit table from the sub queries to the rest of the functions?

 

[June7]

I edited my previous post probably after you read it, review again for suggested solution.

 

[danbl]

Having no experience with VBA that option is out. 22 Queries??? I am not sure where I would start.
I have watched several videos on forms and sub forms to create this so I am not sure how I created this multiple bound sub forms??

I would appreciate any assistance as this is the last piece to getting this revision in use.

Thanks ...DB

 

[danbl]

I am very happy to have your assistance!!! 2-3 days is not an issue and I would much prefer to have one point of contact

 

[June7]

I did say it was a quick fix, not 'best fix'. What you have can be made to work. My 'quick fix' is one way. Managing 2 or 3 subforms to edit the same record will likely involve VBA. "Best fix" is most likely redesigning data structure to allow for multiple records. You decide and I am sure guidance will be offered by someone. I am happy to defer to Pat.

 

[danbl]

I appreciate everyone's help!!!!! .. I will await Pat's reply and help

 

[June7]

It is a balancing act between normalization and ease of data entry/output. "Normalize until hurts, denormalize until it works." How far you take this is up to you. I recommend you study the topic and get a good understanding before making decision.

 

[danbl]

Pat,
Thank you for your insight and suggestions. I found out last night we will not be continuing with the database!!

However I will use this as a tool to learn from. I think using the table analyzer as a starting point maybe useful and looking at how the primary and foreign key relationships are setup. Is it possible to communicate with you directly with questions??

Thanks, Dan

 

[danbl]

I think I need to explain the why the names were done the way they are? There are three sections; Perioperative, Intraoperative, and Postoperative. The idea was to have a continuous flow or sequence.

What you are saying I believe is that the sections could be different tables and have different name sequencing? If this is the case then how would you suggest the names be different? Each question has a yes/no / NA possible answer.

 

[June7]

I understand the intent. It's the execution that fails. No one is suggesting you have 3 tables (although that is another non-normalized option). However, I did suggest you have 3 records instead of 1. This would require another field in table for operative type (Peri, Intra, Post). Populating this field can be automated by textbox DefaultValue property on each of the 3 subforms. Each subform would also have a RecordSource that filters for the appropriate OperativeType. So far design options are:

1. Current table structure with one subform with 3 grouped areas of controls for the 3 operative types OR 3 subforms and VBA code to synchronize the 3 forms to edit same record

2. Still 1 table but each operative event will be a separate record and a field will identify the operative type - allows for 3 subforms each with appropriate filter criteria

3. Three tables and 3 subforms - someday you will need all 3 tables data in one dataset and a UNION query will be needed

 

[danbl]

As I have said this database is being retired. So now this is a learning opportunity for me.
I get the idea of three operative types ... so each type would have its own sub form for data entry, (Option 2) ?

 

[June7]

Yes, and it means redesigning the one table to eliminate the repeated fields for operative types. But since I suppose the existing data must be preserved, this gets a bit tricky. This is where a UNION query will be useful.

If you are tracking 3 operative types, why is table named t_Perioperative_Data ?

 

[danbl]

It would be good to maintain the data in this new database but may not be essential as it is preserved in the current database.

t_Perioperative_Data was used because the 3 operative types make up the perioperative data. I see your point that this could be broken down to 3 separate tables and linked by primary/foreign key relationships.

 

[danbl]

This will now be an opportunity to learn as I said .... I am already looking at normalizing and what was missed previously.
I get the point of each type being its own sub form with a related query for data retrieval but my question is how does these sub forms work for data entry? and what will prevent the same thing from Happening? Is it a matter of not using the primary key/ foreign key relationship between the main form "visit" table and other tables more than once??

 

[June7]

Will still use pk/fk link for each form/subform and each subform will create a separate record. Therefore there will still be 3 child records associated with each parent (master) record but since the fields are more normalized, will not be 3 records with lots of empty fields in each.

Textbox for operative type should be set with a DefaultValue on each form for the appropriate value and then Locked so user cannot edit. Or don't even display this to user, set it as not visible.

 

[danbl]

I know have lots of time to work on this, so I would like to be able send this back to both of you in the future.
Is this a possibility

 

[June7]

Attach file to post in thread.