MS Access sub-subform data entry

Question

I am building a communication/task logging system which logs incoming communications and assigns tasks based on them.

My database has a notes system, so that the users can make notes about a variety of things - e.g. a customer record might have a note which says "This customer always orders their own window frame glass". This is all handled via a single "Notes" table which has a PrimaryNoteTableID which links to a static lookup table which identifies the primary table which the note applies to, and an PrimaryTablePK field which stores the PK of the record within that table it applies to.

In order to avoid corruption, it is advised to keep memo fields in a table of their own with a 1:1 relationship to their parent table (see here), which is what I have done.

When adding new communications and tasks, the user will usually want to add the following:

• Details of the person the communication is with.
• The subject of the communication.
• The type of action required and the deadline for that action.
• A note with details of what has been discussed etc.

With that in mind I have:

tblCommTaskLog with the fields CommDate, ActionRequiredTypeID(FK), ActionDeadlineDays, CommAccountID(FK) etc.

tblNote with: NoteID(PK), PrimaryNoteTableID(FK), PrimaryTablePK(FK), EnteredByUserID(FK), EntryDate.

tblNoteText with NoteTextID(PK), NoteID(FK), NoteText(Memo)

Some of these are foreign keys to other tables which should be self-explanatory.

Essentially what I'm running into problems with is the form for entry with these three tables. I find that the Access handling of subforms can be pretty stupid, such that:

• You're locked into displaying things in a certain way if you want to use a subform, or a subform of a subform.
• Access saves records before you've clicked to save. It also saves records in subforms which can't be handled with a simple "me.undo" in the form's code and results in you having to find the record in the subform and delete it with code.
• The subforms act in stupid ways insofar as the way they assign autonumber PKs and pick those up in the links between child and master forms.

I'd like to just have the fields from tblCommTaskLog and the NoteText field on one form, so that the user fills in the details from tblCommTaskLog (ActionRequired etc.) and the note text, hits a "Save" button, and then via code the items are saved correctly, such that:

• A new record is created in tblCommTaskLog with e.g. a PK of 72 (Autonumber).
• A new record is created in tblNote with a PrimaryNoteTableID of 4 which corresponds to tblCommTaskLog, a PrimaryTablePK of 72 and a PK NoteID e.g. 422 (Autonumber).
• A new record is created in tblNoteText with a NoteID of 422.

I think what I need is a main form with the fields from tblCommTask with an unbound text field to take the input for NoteText. I just can't figure out how to create a new record in tblNote, get the AutonumberPK from that field and insert it into the FK of tblNoteText such that everything is linked together correctly with the right FKs. Can I do this with an SQL INSERT and then a SELECT TOP on the Autonumber field? Would I be better off doing this with Recordsets and Lastmodified? Is there some other method I'm not thinking of?

Answer 1

@whatEvil:

you can still make it as 1:M. if you are allowed to alter your primary tables, alter them and add

1. new column pk_guid : String(38). (can also be 36 if you don't use {})
2. Add a before insert trigger to automatically add a GUID for each new record.
3. something like: for each rows before insert set pk_guid = get_uuid()

in your tbl_note just have

1. Note_id : pk
2. origin_guid : string(38) fK
3. notes :
4. added_by
5. .....

now you can simply enforce 1:M relationship. GUID are "Globally Unique Identifier". All of your primary tables will produce an unique key where you can simply join them at any stage without thinking of which table it belongs to or how to save parent table name.

of course you need a custom function to get GUIDs: use this code to create one

Public Function GET_UUID() As String
    With CreateObject("Scriptlet.TypeLib")
        GET_UUID = VBA.Left(.GUID, 38)
    End With
End Function

Edit: The idea is to have a global unique id in each of your primary table (GUID/ UUID). Which then will be used to join your primary and note table. Note: you need to add a trigger (before insert) to each of your primary table to produce a GUID. Also note, MS access call it DataMacro (check if your version support datamacro) more about datamacro : https://support.office.com/en-ca/article/Create-a-data-macro-b1b94bca-4f17-47ad-a66d-f296ef834200?ui=en-US&rs=en-CA&ad=CA

why this way? currently your primary tables are producing Auto-number it is possible that one or more table will have same ID number. After all, autonumber is only unique throughout a table not throughout the entire database. To avoid duplicating you are adding another key (table identification key) to identify to which primary table the foreign key belongs to. this sounds all good until you make one wrong update to your primary table with wrong value to the note ID.

by using GUID you will be able to simply maintain the 1:m relationship no matter how many primary tables are participating. Also I personally like the idea with GUID in case if your application grows and need real unique ids throughout your database. its totally up to you which way you want to go, i just explained one method..