Is it possible to use the same table for both subforms?

[SusanCoder]

Thank you in advance for your advice.



I have a mature, existing continuous subform named ServicesSubTable subform supported by a query from table ServicesSubTable (I inherited the subfrm and table names!). The subfrm has the following controls with their table attributes: UniqueID (identifies a person, primary key, required, AutoNumber), DateOfService (required, Date), Service (required, text) ServiceType (required, text), ILSpecialistName (required, text).

My client has requested a new continuous subfrmTrsnMeetings that allows record add, update and deletes. They want the following controls with their table attributes: TrsnID (row ID, co-primary key, required, Number?), UniqueID (co-primary key, required, AutoNumber), TrsnMeetingDate (not required, Date), TrsnMeetingDateSpec (not required, Text), TrsnMeetingCompleted (not required, Date), TrsnMeetingCompletedSpec (not required, Text), TaskEmailSent (not required, Date), TaskEmailSentSpec (not required, Text), NextMeetingEmailSent (not required, Date), NextMeetingEmailSentSpec (not required, Text).

Here's the requirement that is also stumping me...they want only the TrsnMeetingDate and it corresponding TrsnMeetingDateSpec data on this subfrm to show up on the ServicesTable subfm, where the subfrmTrsnMeetings' Date value would be the ServiceSubTable DateOfService value, and the subfrmTrsnMeetings' subfrxxxSpec value would be the ServiceSubTable ILSpecialistName value, with Service = "Service", ServiceType = "Transition Meeting".

The client wants the subfrmTrsnMeetings fields/controls in this specific order in a row so a value for each field with can be added, deleted, or updated independently. Not every Date field with it's corresponding Spec (ILSpecialistName) field will be populated. The client wants the Date values and corresponding xxxSpec values for only the TrsnMeetingDate and it corresponding TrsnMeetingDateSpec to be reflected on the ServiceSubTable subform every time those values in the subfrmTrsnMeetings change.

My question: If I add the TrsnID field to the table ServicesSubTable is it even possible to use the table to store data for and populate the subfrmTrsnMeetings? And wouldn't the data only be added, deleted, or updated on the subfrmTrsnMeetings where the TrsnID would be incremented with vba code?

[mike60smart]

What you are suggesting in this paragraph is just wrong:-

"My client has requested a new continuous subfrmTrsnMeetings that allows record add, update and deletes. They want the following controls with their table attributes: TrsnID (row ID, co-primary key, required, Number?), UniqueID (co-primary key, required, AutoNumber), TrsnMeetingDate (not required, Date), TrsnMeetingDateSpec (not required, Text), TrsnMeetingCompleted (not required, Date), TrsnMeetingCompletedSpec (not required, Text), TaskEmailSent (not required, Date), TaskEmailSentSpec (not required, Text), NextMeetingEmailSent (not required, Date), NextMeetingEmailSentSpec (not required, Text)."

This section is a Repeating Group and should be records in a related table vice fields in the Transaction Table:
"TaskEmailSent (not required, Date), TaskEmailSentSpec (not required, Text), NextMeetingEmailSent (not required, Date), NextMeetingEmailSentSpec (not required, Text)."

[Bob Fitz]

I have a mature, existing continuous subform named ServicesSubTable subform supported by a query from table ServicesSubTable

Can you tell us about the master form that this subform is on?
Can you post a copy of your db?

[June7]

Should "to show up on the ServicesTable subfm" be "to show up on the ServicesSubTable subform"?

These Trsn fields are in another table? How are these tables related? What does " subfrxxxSpec" mean? I am not following purpose of "co-primary" key setup.

If you want to provide db for analysis, follow instructions at bottom of my post.

[SusanCoder]

Can you tell us about the master form that this subform is on?
Can you post a copy of your db?

Bob Fitz, mike60smart, and there's one other person who responded...thanks to all who replied to my thread. Sorry it wasn't clear. I've created an attachment with snapshots of forms and info about the tables, etc. Some of the subform's data fields and label names have been hidden. I can't share the DB because of confidentiality and proprietary obligations.

Please refer to the attachment. Warning: I know some of the table and form names are bit wackadoo, but please don't hate. The creators were good people. I inherited this database and kept on going with a new naming convention for anything I created. I bolded object names in the attachment and below for ease of understanding. Yes, ServicesSubTable subform is the complete name of a subform that I inherited. I use the prefixes "subfrm" and "tbl" and no spaces for the object names I create.

My two options, as I see them, to create the new continuous subform subfrmTrsnMeetings:

1. Use a query with existing table ServicesSubTable to populate the form. Add new fields as needed to the ServicesSubTable and store the new subform subfrmTrsnMeetings' TrsnMeetingCompleted field value for ServiceType as "Transition Meetings", because the ServicesSubTable already contains some of these records historically, and they are also displayed in the subform ServiceSubTable subform.

• ​This is problematic for me because the new subform seems to require a composite primary key to keep the data ordered and in the correct rows. The table ServicesSubTable doesn't have a primary key or a composite primary key. Also, the new subform doesn't display all or part of the data from the ServicesSubTable, rather a row displays 4 iterations of DateOfService and ILSpecialistName fields, in pairs, where the ServiceType equals a different value for each of the four pairs. I don't know if this will even work when loading the data to the form. There are many existing queries, forms, and reports that use the ServicesSubTable's ServiceType field, including thousands of historical records.

2. Create a new table tblTransitionMeetings with the fields used in the new subfrmTrsnMeetings and use a query of the new table for the record source for the new subform.

• This is problematic for me because of having one of the data fields and it's record stored on two tables. The data from one of the DateOfService and ILSpecialistName pairs (TrsnMtgCompleted and TrsnMtgCompletedSpec where ServiceType = "Transition Meeting") needs to also be stored on the ServicesSubTable. It's the only one of the pairs that has to be on both tables. I'm not sure where in the subfrmTrsnMeetings to capture the current record values and then the updated records values to update the ServicesSubTable, or how that is done. And I've always designed tables and forms so data fields and their records reside on one table only.

New subfrmTransitionMeeting 6.24.2024.pdf

Thanks for your advice on which option to choose in creating the new subfrmTrsnMeetings. I appreciate you!

Susan

[Welshgasman]

No attachment?

[SusanCoder]

Thank you, Welshgasman. I didn't realize the attachment hadn't uploaded. The attachment is now showing in the thread.