Run code ONLY on record change/close (1 Viewer)

[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.

 

[Pat Hartman]

I want to run some code when you change record or exit the form

The AfterUpdate event of the form runs AFTER a record is saved. What you are missing as arnel suggested is that Access saves the record when you tell it to but also when Access determines that it needs to save. One of those situations is when focus moves from the main form to the subform. Obviously, the subform records are dependent on the main form records so Access helps you out here and forces the save to maintain a proper relationship.

You also say you want to run the code when the form closes. Depending on what you are trying to do, the Close event might work but you might need to use the unload event. We don't have enough information.

Is it possible to add code to the default navbar?

No.

Why is running the code when focus changes from the main form to the subform as long as the main form record was actually updated?