Bypass Form_BeforeUpdate validation when switching to a subform (1 Viewer)

Notiophilus

Registered User.
Local time
Today, 09:40
Joined
Jan 18, 2015
Messages
42
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.

Code:
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)
 
Last edited:

theDBguy

I’m here to help
Staff member
Local time
Today, 01:40
Joined
Oct 29, 2018
Messages
21,357
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

Registered User.
Local time
Today, 09:40
Joined
Jan 18, 2015
Messages
42
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

I’m here to help
Staff member
Local time
Today, 01:40
Joined
Oct 29, 2018
Messages
21,357
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

Registered User.
Local time
Today, 11:40
Joined
Dec 7, 2018
Messages
481
Form_BeforeUpdate (Cancel = True)
This is new event of Form for me!
It should be like this::
Code:
Private Sub Form_BeforeUpdate(Cancel As Integer)
    ' some operations ...
End Sub
 

Notiophilus

Registered User.
Local time
Today, 09:40
Joined
Jan 18, 2015
Messages
42
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

I’m here to help
Staff member
Local time
Today, 01:40
Joined
Oct 29, 2018
Messages
21,357
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

Registered User.
Local time
Today, 09:40
Joined
Jan 18, 2015
Messages
42
Eeesh, I feared as much. Oh well, I'll leave it as an option for the future. Thanks!
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 04:40
Joined
Feb 19, 2002
Messages
42,970
My problem is that switching to a subform triggers BeforeUpdate (and thus the validation). How can I get around it?
In a relational database (not just Access), you CANNOT save a child record without first saving the parent record.

WHY would you ever want to save a parent record with incomplete/invalid data?

You should probably rethink your logic and perhaps redesign your tables if data is in the wrong table. I have never in 50+ years encountered a situation where I needed to add a child record in the middle of creating a parent record but I'm willing to listen.
 

Notiophilus

Registered User.
Local time
Today, 09:40
Joined
Jan 18, 2015
Messages
42
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.)
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 04:40
Joined
Feb 19, 2002
Messages
42,970
Authors/stories is a m-m relationship. An author may write many stories and a story may have multiple co-authors. On your data entry form, one will be the main form and the other will be the subform. You could have two ways of looking at this. The story with a subform for authors or the Author with a subform for stories. It is even rational to have both options. So, you open the author form and either find the author or add him. Once the form is positioned to the author, you can add the story to the subfom if it isn't already there.

I would start my data entry with the item that is most likely to already exist and that is author. On the subform, you would have a combo to choose the story. If the story isn't there, you could use the not in list event to open a form where you can enter the info about the story. That code will update the combo so when the popup form closes, the story is available to choose to assign to the author.

Or you can do it the other way around and start with the story. The key is using the not in list event for the combo in the subform to let you add the other side of the m-m if it doesn't already exist.

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.

I've attached a m-m example that shows how the relationship works from both sides. I'm not sure if it has the notinlist event implemented because I personally hate that kind of sloppy input but you can add it if necessary and it makes sense for your application..

I would add the author/story. Then if the story/author is not shown in the combo. I would close the form and open the relationship from the other side to finish it. 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.
 

Attachments

  • ManyToMany20210414.zip
    1.5 MB · Views: 241

Notiophilus

Registered User.
Local time
Today, 09:40
Joined
Jan 18, 2015
Messages
42
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.
 
Last edited:

Users who are viewing this thread

Top Bottom