Run code ONLY on record change/close

[Notiophilus]

Access 2010. I want to run some code when you change record or exit the form:

• check if title AND summary AND link are blank, and if they are discard the record
• check if record is a duplicate and then do stuff
• if everything is OK, then commit unbound fields

Form_BeforeUpdate / AfterUpdate fire when you change records, but as they also fire whenever you set focus to a subform, tabbing through the form fields = tabbing through subform field = code fires, resulting in inappropriate alerts.

The answer seems to be to move the code to my custom navigation buttons + the Form_Unload event. However, I also want to keep Access's default navigation bar (arrows + search box), because I'm obstinate. Is it possible to add code to the default navbar? It doesn't seem to have its own event.

I don't like this solution very much, but it seems like the only option. Am I approaching the problem the right way?

 

[Ranman256]

dump the BeforeUpdate / AfterUpdate code. (you see why)
instead, I have a IsValidForm() test before save.
I do my checks via code (not in the field property)
Easier to change. All in 1 spot...

usage:
if IsValidForm() then docmd.openquery "qaAddNewRecord"

Private Function IsValidForm() As Boolean
Dim vMsg
Select Case True
   Case IsNull(dtpWeekOf)
      vMsg = "Date field missing"
   Case IsNull(cboUser)
      vMsg = "Teacher name is missing"
   Case IsNull(cboSubj)
      vMsg = "Subject field is missing"
End Select
If vMsg <> "" Then MsgBox vMsg, vbCritical, "Required Field"
IsValidForm = vMsg = ""
End Function

 

[arnelgp]

if you have a subform, you can't suspend the
saving of the main form. it will automatically be
saved when you set Focus the the subform or switch
focus on another form.