Dcount Hell -- going to first record on close

[Ian]

Here is the code I'm using to count records before closing a form (if the
count = 0 I'd like it to do something)

Private Sub Form_Close()
Dim ChkIt As Integer
Me.ClientID.SetFocus
'qry_cntactappt has where clause for form.frm_client.clientID to sync with
active form
ChkIt = DCount("apptID", "qry_cntactappt")
If ChkIt = 0 Then
MsgBox ("Make this hell stop")
Else
MsgBox ("Shoot Me in Foot now")
End If
End Sub

Before the form closes it bounces back to the first record rather than the
(what was) active form. Any idea why it's doing that?

 

[Allen Browne]

The form's Close event is too late.

If you want to ensure valid data was entered in a bound form, do the count
in the form's BeforeUpdate event (remembering that if this is a new record,
it's not yet part of the count.)

If you want to verify something after the record has been saved, use the
AfterUpdate event of the form. (Form's properties; not those of a control.)

The data may still be there if you use the form's Unload event instead of
Close, but I'm not sure if it would be the record you expect (e.g. the user
may have tabbed to another record, or the new one.)