Bypass Form_BeforeUpdate validation when switching to a subform

[Notiophilus]

I have a bound parent form with two subforms. If the user tries to leave a record without having entered certain key data (title, summary, link), the form challenges them: they can either cancel and stay on the record to enter the missing data, or keep going, and the incomplete record will be deleted.

My problem is that switching to a subform triggers BeforeUpdate (and thus the validation). How can I get around it?
Form_Dirty > [click or tab to the subform] > Form_BeforeUpdate (validation check) > fsubSubformControl_Enter

There are several points on my form where I might need to force a save (e.g. to create a child record). If this happens, I set a flag in an unbound textbox txtAllowSave, and use this to get around validation:
SomeEvent forcing a save [set txtAllowSave = "yes"] > BeforeUpdate [if txtAllowUpdate = "yes", skip validation] > AfterUpdate > return to SomeEvent

Unfortunately this doesn't work for the subforms, as the subform control Enter event happens after BeforeUpdate.

Private Sub SaveWithoutValidation
    'Force a save without triggering the validation in BeforeUpdate.
    Me.txtAllowSave = "yes"
    Me.txtTitle.SetFocus
    Me.Dirty = True
    Me.Dirty = False               'triggers BeforeUpdate
    Me.txtAllowSave = ""       'after the save, reset, so that incomplete records will still be challenged on exit.
    Me.Dirty = True
End Sub

Form_BeforeUpdate (Cancel As Integer)
    If Me.txtAllowSave = "yes" Then Exit Sub     'skip validation and save as normal
    If [key fields are OK] Then Exit Sub

    Select Case msgbox "Key fields are empty. The record will not be saved. Proceed?" Then
        Case vbNo
             Cancel = True     'abort whatever triggered the update and keep editing
        Case vbYes
             'if it's a new record, undo it, otherwise delete
    End Select
End Sub

Private Sub ThisEventForcesASave
    If msgbox "create a child?" = true Then
         Call SaveWithoutValidation
         'create a child
    End If
End Sub

I think I'm at a dead end here. Is there an event that happens between clicking on / tabbing to fsubSubformControl and BeforeUpdate firing? Can anyone suggest a better way of doing things?

(I can sort of get around it by making a custom messagebox with options "keep editing" [cancel = true], "discard current record" [undo or delete record], "proceed" [exit sub and ignore validation]. I just don't like this option very much.)

Edited to fix brainfart in code - BeforeUpdate(Cancel As Integer)

 

[theDBguy]

Just to make sure I understand, you have a validation routine to make sure the data is valid, but you also allow the data to be invalid sometimes?

 

[Notiophilus]

Yep. The only time I really want validation is when I leave the record, either by closing the form or navigating to another record.

(e.g. some other parts of the form create child records, and in such cases I force a SaveWithoutValidation. This works fine.)

 

[theDBguy]

Yep. The only time I really want validation is when I leave the record, either by closing the form or navigating to another record.

(e.g. some other parts of the form create child records, and in such cases I force a SaveWithoutValidation. This works fine.)

I see. Okay, since the BeforeUpdate event fires when you leave the record, which includes going to the subform, I suppose what you're left with is to prompt the user for an option to ignore the validation and continue on to the subform or complete the required entries on the main form first before proceeding.

 

[Eugene-LS]

Form_BeforeUpdate (Cancel = True)

This is new event of Form for me!
It should be like this::

Private Sub Form_BeforeUpdate(Cancel As Integer)
    ' some operations ...
End Sub

 

[Notiophilus]

hahaha whoops yeah, that's an error in my post - I was on autopilot. Actual code is okay.

I see. Okay, since the BeforeUpdate event fires when you leave the record, which includes going to the subform, I suppose what you're left with is to prompt the user for an option to ignore the validation and continue on to the subform or complete the required entries on the main form first before proceeding.

and yep, that's all I can see. It just feels a little awkward to give the user the choice to delete the record (if they're just clicking on a subform) or not to delete the record (if they're navigating away, deletion should be automatic)

1. warning, key fields missing!
   1. cancel, go back and keep editing
   2. ignore warning
      1. and delete record
      2. and don't delete record

 

[theDBguy]

hahaha whoops yeah, that's an error in my post - I was on autopilot. Actual code is okay.


and yep, that's all I can see. It just feels a little awkward to give the user the choice to delete the record (if they're just clicking on a subform) or not to delete the record (if they're navigating away, deletion should be automatic)

1. warning, key fields missing!
   1. cancel, go back and keep editing
   2. ignore warning
      1. and delete record
      2. and don't delete record

I think the other option is to use unbound forms, but you'll probably have the same dilemma, only in the opposite direction (auto saving). Good luck!

 

[Notiophilus]

Eeesh, I feared as much. Oh well, I'll leave it as an option for the future. Thanks!

 

[Notiophilus]

The table structure, heavily simplified, is as follows:
tblStory [PK: storyID, title, summary, words]
tblStoryAuthor [storyID, authorID] - multiple authors allowed per story, although normally there's only one
tblAuthor [PK: authorID, name]
tblStoryCategory [storyID, categoryID] - multiple categories allowed per story, although normally there's only one
tblCategory [PK: categoryID, name]

frmStories has the following controls: Title, Summary, Words, Author and Category (the latter two on separate subforms, bound to tblStoryAuthor and tblStoryCategory).
For a story to be valid, it must have a title OR a summary. Either is good.

If I enter first the title and then the author, the form passes validation and everything is fine. However, because I'm lazy, I entered the author first, then went to look up the title. Boom - a reason to save the parent record. I also have a button, cmdMultiAuthor, which opens a popup to add multiple authors, but this requires a storyID, which doesn't exist until the parent has been saved. Could I just force myself to enter a title or summary before touching the author or category? Perhaps, but I don't want to.

(I could, I suppose, make the subforms into unbound controls - that would definitely solve my problem, but it seems a shame at this point. Looks like I'll have to consider it.)

 

[Notiophilus]

REGARDLESS of which form you make the main form, the subform is ALWAYS the junction table. It is NEVER the other side of the m-m.

That's what I said in my previous comment. The subform is bound to tblStoryAuthor (the junction table). And yeah, I'm using NotInList - almost every story I add has a new author, so it would be a pain to have to add each one separately.

My problem with the not in list event is that people get sloppy and enter typos rather than actually use search features to find the item they are misspelling and so they just enter the typo if you make it too easy for them.

...although you definitely have a point here! Since this database is only theoretically for other users (I doubt I'll ever be satisfied it's good enough to share), I'm mostly trying to stupid-proof it against myself.

Thanks for the file, btw - it's a good learning tool.