title: MS Access sub-subform data entry
body: <p>I am building a communication/task logging system which logs incoming communications and assigns tasks based on them.</p>
  
  <p>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 <code>PrimaryNoteTableID</code> which links to a static lookup table which identifies the primary table which the note applies to, and an <code>PrimaryTablePK</code> field which stores the PK of the record within that table it applies to.</p>
  
  <p>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 (<a href="http://www.techrepublic.com/blog/10-things/10-ways-to-prevent-access-database-corruption/" rel="nofollow">see here</a>), which is what I have done.</p>
  
  <p>When adding new communications and tasks, the user will usually want to add the following:</p>
  
  <ul>
  <li>Details of the person the communication is with.</li>
  <li>The subject of the communication.</li>
  <li>The type of action required and the deadline for that action.</li>
  <li>A note with details of what has been discussed etc.</li>
  </ul>
  
  <p>With that in mind I have:</p>
  
  <p><code>tblCommTaskLog</code> with the fields <code>CommDate</code>, <code>ActionRequiredTypeID(FK)</code>, <code>ActionDeadlineDays</code>, <code>CommAccountID(FK)</code> etc.</p>
  
  <p><code>tblNote</code> with: <code>NoteID(PK)</code>, <code>PrimaryNoteTableID(FK)</code>, <code>PrimaryTablePK(FK)</code>, <code>EnteredByUserID(FK)</code>, <code>EntryDate</code>.</p>
  
  <p><code>tblNoteText</code> with <code>NoteTextID(PK)</code>, <code>NoteID(FK)</code>, <code>NoteText(Memo)</code></p>
  
  <p>Some of these are foreign keys to other tables which should be self-explanatory.</p>
  
  <p>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:</p>
  
  <ul>
  <li>You're locked into displaying things in a certain way if you want to use a subform, or a subform of a subform.</li>
  <li>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.</li>
  <li>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.</li>
  </ul>
  
  <p>I'd like to just have the fields from <code>tblCommTaskLog</code> and the <code>NoteText</code> field on one form, so that the user fills in the details from <code>tblCommTaskLog</code> (ActionRequired etc.) and the note text, hits a "Save" button, and then via code the items are saved correctly, such that:</p>
  
  <ul>
  <li>A new record is created in <code>tblCommTaskLog</code> with e.g. a PK of <code>72</code> (Autonumber).</li>
  <li>A new record is created in <code>tblNote</code> with a <code>PrimaryNoteTableID</code> of <code>4</code> which corresponds to <code>tblCommTaskLog</code>, a <code>PrimaryTablePK</code> of <code>72</code> and a PK <code>NoteID</code> e.g. <code>422</code> (Autonumber).</li>
  <li>A new record is created in <code>tblNoteText</code> with a <code>NoteID</code> of 422.</li>
  </ul>
  
  <p>I think what I need is a main form with the fields from <code>tblCommTask</code> with an unbound text field to take the input for <code>NoteText</code>. I just can't figure out how to create a new record in <code>tblNote</code>, get the AutonumberPK from that field and insert it into the FK of <code>tblNoteText</code> 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?</p>