Preventing fields from being left blank when form closed

[hedges98]

I am struggling with something that is probably quite simple. When I close a form, if certain fields are left empty then I want a message to pop up to tell the user these fields are required and then they can go back and fill them in.

Below is the code (which I found and modified slightly) and it works when used to check ONE field but when I added code for an additional field to be checked, it returns an error (run-time error 2450) - Access can't find the form 'Frm_Case' referred to in a macro expression or Visual Basic code

Private Sub btnBack_Click()
If IsNull(Forms![Frm_Case]![Frm_Individual_Details].Form![Per_Referral_Date]) Then
  If MsgBox("You must enter a referral date." & Chr(13) & Chr(10) & Chr(13) & Chr(10) & _
  "Press 'OK' to return and enter a date.", _
  vbOKOnly, "Referral Date Required") _
  = vbOKOnly And Forms![Frm_Case]![Frm_Individual_Details].Form![Per_Referral_Date].SetFocus Then
  End If
Else
  DoCmd.Close
End If
 
If IsNull(Forms![Frm_Case]![Frm_Individual_Details].Form![cboReferredFrom]) Then
    If MsgBox("You must specify where the client was referred from." & Chr(13) & Chr(10) & Chr(13) & Chr(10) & _
    "Press 'OK' to return and select a referral service.", _
    vbOKOnly, "Referred From Service Required") _
    = vbOKOnly And Forms![Frm_Case]![Frm_Individual_Details].Form![cboReferredFrom].SetFocus Then
    End If
Else
    DoCmd.Close
End If
 
End Sub

I need to add code for one more field. Any pointers/help greatly appreciated!

[ADezii]

For the 2nd Test, your syntax is incorrect, namely vbOKOnly is not a valid MsgBox Result, and you cannot tack on the And condition:

If IsNull(Forms![Frm_Case]![Frm_Individual_Details].Form![cboReferredFrom]) Then
  If MsgBox("You must specify where the client was referred from." & vbCrLf & vbCrLf & _
    "Press 'OK' to return and select a referral service.", _
    vbOKOnly, "Referred From Service Required") = vbOK Then
      [Frm_Case]![Frm_Individual_Details].Form![cboReferredFrom].SetFocus
  End If
Else
    DoCmd.Close
End If

P.S. - This type code normally resides in the Form's BeforeUpdate() Event.

[hedges98]

I'm a little confused - if vbOKOnly is not a valid MsgBox Result and And cannot be tacked onto the condition, why does it work in the first test and not the second?

Would it be wiser to move all of this code to the Form's BeforeUpdate() Event as per your suggestion?

[ADezii]

I'm a little confused - if vbOKOnly is not a valid MsgBox Result and And cannot be tacked onto the condition, why does it work in the first test and not the second?

vbOKOnly is a MsgBox 'Argument' and evaluates to 0 while vbOK is a MsgBox Result and evaluates to 1. What you are doing is evaluating the Return results of the Msgbox Function and testing to see if it = 0 (vbOKOnly) which will always evaluate to False since 0 is not a valid Msgbox Result. You then tack on an And condition to evaluate the Result of the SetFocus Method which makes no sense to me. I could be looking at this from the wrong angle, see what other Members have to say.

Would it be wiser to move all of this code to the Form's BeforeUpdate() Event as per your suggestion?

I think so, but you may have to set the Cancel Argument to true in order to negate the Update of the Form.

[hedges98]

Putting the code into the Before Update event of the Form returns an error message (2501) - the Close action was cancelled.

I tried to understand what you've explained in the post above but it went a bit over my head so I'm going to leave this for today and give it a look tomorrow with a clear head to see if I grasp it then.

Cheers!

[hedges98]

Right, I've had a better look at this and have been looking at the BeforeUpdate of the form and found this code which I have used instead of the code previously posted (I also realised I was overcomplicatin g referencing the fields in the code!)

If Len(Me!cboReferredFrom & vbNullString) = 0 Then
  MsgBox "Please select where the client was referred from"
  Cancel = True
  Me!cboReferredFrom.SetFocus
End If

The only problem is, the form still closes if the field is blank. I thought the 'Cancel = True' part would prevent that?

Does it have something to do with me having a button that is used to close the form (OnClick even of button) also?

Another point, the fields are all in a subform on a form. I've tried putting the BeforeUpdate code in the subform (which works but still closes the form) and the form (which does nothing).

[Belimisimus]

@hedges98
Check this post, I think you can find answer there...
http://bytes.com/topic/access/answer...ons-can-posted

[hedges98]

Thank you!

Okay so, Cancel = True doesn't relate to the closing of the form. I've tried a few things from that link but the form is still closing after the MsgBox pops up asking for the field to be filled in!

[hedges98]

Still struggling with this if anyone has any pointers!

[ADezii]

Can you Upload the DB?