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 overcomplicating 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?

[hedges98]

Sure thing! Hopefully the attached DB will work. I've added two example records.

I commented out the code I've been playing around with in the BeforeUpdate event of the Form and Click Event of the Back button.

If you select an ID number and then click the 'Update Details' button and then the 'Case Details' found on the subsequent form, it is there where I am having the problem.
The form is Frm_Case and has the subform Frm_Individual_Details and I need a message box to alert to missing details on fields cboReferredFrom and Per_Referral_Date

Hope this is enough details!

Cheers ADezii

[ADezii]

I'll llok at it later, but I really can't do much without the Password (LOL).

[hedges98]

D'oh! Did that last time. I'll PM you it now!

[Lysander]

Ok, I use this function, all the time, on many databases (Access 2003) and my solution requires 3 parts.

First of all, in the form itself, some fields are disabled if not needed. i.e. If one field asks "Does the child go to school" and the answer is no, in the after update of that field, I would have "HowFarToSchool.enabled=false" since the next question is irelevent. Thus, when the user is ready to save the record, all mandatory fields are enabled, the rest disabled.

Then I have this code in either the before_update event, or more usually in a "Save Record" button.

If Not Form_Check_NULL(Me, strError) Then
    InCompleteFields = strError
Else
    InCompleteFields = ""
End If
 

This makes InCompleteFields hold a list of all field names not filled in. If this variable is not empty, then I don't save the record and notify the user of the list of fields that need filling in.

So, what is Form_Check_NULL(Me, strError)?

This a global function, called by every form I need validating and the code is as follows

Public Function Form_Check_NULL(ByVal frm As Form, ByRef strError As String) As Boolean
On Error GoTo Form_Check_NULL_Err
Dim ctl As Control
Dim Form_Valid As Boolean
Form_Valid = True
strError = ""
For Each ctl In frm.Controls
    Select Case ctl.ControlType
    Case acTextBox, acComboBox, acListBox, acOptionGroup, acCheckBox
        If ctl.Name <> "cboFindRec" And IsNull(ctl.Value) And ctl.Enabled = True And ctl.Visible = True And ctl.Locked = False Then
            strError = strError & TranFieldName(ctl.Name) & "/"
            Form_Valid = False
        End If
    End Select
Next ctl
Form_Check_NULL = Form_Valid
Form_Check_NULL_Exit:
   Exit Function
Form_Check_NULL_Err:
   MsgBox Err.Description & " in Form_Check_NULL"
   Resume Form_Check_NULL_Exit
End Function
 
 

This goes through every control on the form, looking for input controls. This line
" If ctl.Name <> "cboFindRec" And IsNull(ctl.Value) And ctl.Enabled = True And ctl.Visible = True And ctl.Locked = False Then
"
ignores cboFindRec, a search fields, and then builds up a string of all controlnames that are null, enabled, visible and not locked.

Hope this helps


btw, this works on sub-forms as well as forms. and credit where credit is due, I did not write Form_Check_Null, it was written by my predessor, a young lady called Janjan.

[ADezii]

1. Copy and Paste the following code to the Unload() Event of Frm_Case which will enable you to Cancel the Closing of this Form after a 2-Field Validation.
   

   Private Sub Form_Unload(Cancel As Integer)
   On Error GoTo Err_Form_Unload
   Dim cbo As ComboBox
   Dim txt As TextBox
    
   Set cbo = Me!Frm_Individual_Details.Form![cboReferredFrom]
   Set txt = Me!Frm_Individual_Details.Form![Per_Referral_Date]
    
   If IsNull(cbo) Then
     MsgBox "You must supply Referred From information", vbExclamation, "No Referral"
       cbo.SetFocus
         cbo.Dropdown
           Cancel = True
   ElseIf IsNull(txt) Then
     MsgBox "You must supply a Referral Date", vbExclamation, "No Referral Date"
       txt.SetFocus
         Cancel = True
   End If
    
   Exit_Form_Unload:
     Exit Sub
    
   Err_Form_Unload:
     MsgBox Err.Description, vbExclamation, "Error in Form_Unload()"
       Resume Exit_Form_Unload
   End Sub

2. Copy and Paste the following code to the Click() Event of btnBack on Frm_Case. Notice how the Cancellation Error is Trapped and ignored.
   

   Private Sub btnBack_Click()
   On Error GoTo Err_btnCloseForm_Click
    
       DoCmd.Close
    
   Exit_btnCloseForm_Click:
     Exit Sub
    
   Err_btnCloseForm_Click:
     If Err.Number <> 2501 Then    'NOT the Form's Close cancelled
       MsgBox Err.Description & Err.Number, vbExclamation, "Error in btnCloseForm_Click()"
     End If
       Resume Exit_btnCloseForm_Click
   End Sub

[hedges98]

Lysander - thanks for the input but I think that's gone far beyond what I was trying to do. Appreciate the post though.

ADezii - nailed it again. I didn't know you could trap cancellation errors (nor had I ever needed to!) which is quite interesting. Thanks so much!