Required field warning when closing form

[randle]

Hi

I have searched through the forums and although found relevant threads, none have helped me out.

Basically I have a form which has a field ("Status") that I've set as required from the sourcing table.

This form has a button that makes various changes to the data shown and one of them is to blank the required field. I then want it to complain that nothing's present if the form is closed but I'm aware that unless the field get's focus and data selected then deleted, it won't complain.

From searching other threads I've got the following VBA code in the form's BeforeUpdate section:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.Status = "" Or IsNull(Me.Status) Then
    MsgBox "STATUS IS A REQUIRED FIELD", vbCritical + vbOKOnly + vbDefaultButton2, "MISSING DATA"
      Me.Status.SetFocus
      Cancel = True
      Exit Sub
    End If
End Sub

but nothing happens at all when the form's closed. There's not even an error message to say the code's wrong and just lets me close it leaving this field blank.

Any ideas where I'm going wrong?

TIA

 

[missinglinq]

...I'm aware that unless the field get's focus and data selected then deleted, it won't complain.

Actually, this isn't true! What is true is that if the only data manipulation that occurs is thru code, such as using your button, the Form_BeforeUpdate event won't fire; you'll need to explicitly call it, possibly at the end of your button code, or in this same place, use the statement

Me.Dirty = True

which I think will force the Form_BeforeUpdate event to fire when leaving the record/closing the form.

 

[randle]

Ok thanks that makes sense however these are macros on the button so how would I go about adding the Me.Dirty = True statement? Looking at the current code for this button it reads:

Private Sub Command60_Click()
On Error GoTo Err_Command60_Click

    If Me.Dirty Then Me.Dirty = False
    DoCmd.Close
Exit_Command60_Click:
    Exit Sub
Err_Command60_Click:
    MsgBox Err.Description
    Resume Exit_Command60_Click
    
End Sub

I've tried changing the "Me.Dirty = False" to "True" but this made no difference.

 

[randle]

Any ideas on this?

 

[ghudson]

Private Sub Form_BeforeUpdate(Cancel As Integer)
   If Me.Status = "" Or IsNull(Me.Status) Then
      MsgBox "STATUS IS A REQUIRED FIELD", vbCritical + vbOKOnly + vbDefaultButton2, "MISSING DATA"
      Me.Status.SetFocus
      Cancel = True
      Exit Sub
   End If
End Sub

Try your above code in the forms Unload event instead. The user will not be able to close the form if the Status field is empty. Once you prove that works then add it back to the forms Before Update event to also catch it there.

 

[randle]

Hi

Well at least something's now happening....I've added the code to the "On Unload" event but this give's me

compile error: Method or data member not found

with ".SetFocus" from the 4th line highlighted Blue. When clicking Ok, the "Private Sub Form_Unload(Cancel As Integer)" line is highlighted Yellow!!

Code currently looks like this now

Private Sub Form_Unload(Cancel As Integer)
If Me.Status = "" Or IsNull(Me.Status) Then
    MsgBox "STATUS IS A REQUIRED FIELD", vbCritical + vbOKOnly + vbDefaultButton2, "MISSING DATA"
      Me.Status.SetFocus
      Cancel = True
      Exit Sub
    End If
End Sub

Excuse my coding ignorance.