I have a scientific database that takes measurements from different forms all linked to the same entityID. My problem is that, while I have an autonumber ID for the main table just like an invoice structure, the sub sample forms can and should dirty the main ID. I want to increment the main id from any of the subforms when I enter the first measurement on any subform, but I don't want to increment the Main ID when I access one of the other child subforms and enter additional measurements for the current , and if the main id is dirty I don't want any of the 3 subforms to advance the autonumber EntityID, which means I can't seemingly use DMax on any subforms where new entries could be made. but once one of the child subforms is dirty I need to reference the main entityid once generated from the other child forms once dirtied. anyone?

|obsid auto pk|
|obsnum long pk| |number| main experiment number|***herein lies the DMax problem***

|meas1id| |number| -required
|obsnum| |number| fk -required
|meas1num| |number|

meas2id number not required 
meas2num number

meas3id number not required 
meas3num number

CodePudding user response：

You are not being clear if the main form (and record) will have "some" kind of edit.

If you on an existing record, then the main form (and record) will of course already have a record.

If you go to a new main form record, then YES, before you jump or switch focus to any of the subforms, the main record will have to become "dirty". In 99% of cases, it would be VERY VERY rare that no data or nothing at all is to be entered into the main form record first, right?

So, Access will automatic ALWAYS save the main record for you (if it is dirty), and that will thus also generate the autonumber PK for you also.

It is I suppose possible that you are jumping to a new main record, and NO editing is or will occur on the main form, and of course if a user moves focus to a subform, then YES you are in trouble.

This is a VERY rare setup.

What I would do to the main table then is add a new column, call it CreateDate.

Then you can add this event to the main form:

If the user is to type, or do anything, then we can use the OnInsert event, say like this:

Private Sub Form_BeforeInsert(Cancel As Integer)

   If IsNull(Me!CreateDate) Then
      Me!CreateDate = Date
   End If

End Sub

So, this will "always" setup a column in the main form that gets dirty.

However, if we navigate to a new blank main record and type NOTHING, then the automatic save of the main record can't occur. So, for each subform, you can add this for the on-enter even of the subform control.

Private Sub SbubformColors_Enter()

   If IsNull(Me!CreateDate) Then
      Me!CreateDate = Date
   End If

End Sub

Another way? remove the navigation buttons, and have your OWN button that jumps the form to a new record and then sets (dirty) that CreateDate column in code. And once again, then any focus change to a subform will trigger the automatic save of the main form record first.

So, your issue can ONLY occur if navigation to a blank main new record occurs, and ZERO editing occurs. In ALL other cases, the create automatic save of the main record will occur for you without code.

If for some strange reason you do allow navigation to a new main record AND also no editing will occur, then you need to setup that "on enter" code for EACH of the sub form controls, and dirty the record with the above code (e.g. this).

Private Sub SbubformColors_Enter()

   If IsNull(Me!CreateDate) Then
      Me!CreateDate = Date
   End If

End Sub

So, if you have 4 subforms, then you can cut paste the above code inside the above. The above will ensure that the main record exists or becomes dirty, and after the above event, the main form will be saved automatic (and thus create the autonumber PK), and then you are free to edit use the subform.

I suppose there are a number of ways to approach the above. So, maybe in place of adding a new column CreateDate, you might have some existing column that could be set a value and thus "force" the dirty of the record.

Edit:

Keep in mind that Access can and will setup maintain the auto number, and for all the tables in the sub form(s), we assume that those tables have a column that points back to the main record. In other words, each of the child forms tables is assumed to have a column in their tables. Perhaps called main_ID or some such.

To have access automatic "maintain" and insert/setup/maintain the main form/record autonumber id?

In each sub form control, you set the link master, and link child field setting. Once you do this, then no code at all is required.

In fact, as noted, if the main record exists, or in all cases the main record will be create (say by going to new record), then as long as this record becomes dirty (say by user editing, or our above code), then all sub forms (and sub tables) will automatic be maintain.

You DO NOT need to use dmax, and in fact you need ZERO code, UNLESS the issue is that the main form will never see ANY editing when you just moved to a new record.

In design mode of the main form, view the property sheet for each of teh sub forms. You see (and want to) ensure these settings are correct: