By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,480 Members | 2,953 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,480 IT Pros & Developers. It's quick & easy.

Preventing fields from being left blank when form closed

100+
P: 109
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

Expand|Select|Wrap|Line Numbers
  1. Private Sub btnBack_Click()
  2. If IsNull(Forms![Frm_Case]![Frm_Individual_Details].Form![Per_Referral_Date]) Then
  3.   If MsgBox("You must enter a referral date." & Chr(13) & Chr(10) & Chr(13) & Chr(10) & _
  4.   "Press 'OK' to return and enter a date.", _
  5.   vbOKOnly, "Referral Date Required") _
  6.   = vbOKOnly And Forms![Frm_Case]![Frm_Individual_Details].Form![Per_Referral_Date].SetFocus Then
  7.   End If
  8. Else
  9.   DoCmd.Close
  10. End If
  11.  
  12. If IsNull(Forms![Frm_Case]![Frm_Individual_Details].Form![cboReferredFrom]) Then
  13.     If MsgBox("You must specify where the client was referred from." & Chr(13) & Chr(10) & Chr(13) & Chr(10) & _
  14.     "Press 'OK' to return and select a referral service.", _
  15.     vbOKOnly, "Referred From Service Required") _
  16.     = vbOKOnly And Forms![Frm_Case]![Frm_Individual_Details].Form![cboReferredFrom].SetFocus Then
  17.     End If
  18. Else
  19.     DoCmd.Close
  20. End If
  21.  
  22. End Sub
I need to add code for one more field. Any pointers/help greatly appreciated!
Mar 31 '10 #1

✓ answered by 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.
    Expand|Select|Wrap|Line Numbers
    1. Private Sub Form_Unload(Cancel As Integer)
    2. On Error GoTo Err_Form_Unload
    3. Dim cbo As ComboBox
    4. Dim txt As TextBox
    5.  
    6. Set cbo = Me!Frm_Individual_Details.Form![cboReferredFrom]
    7. Set txt = Me!Frm_Individual_Details.Form![Per_Referral_Date]
    8.  
    9. If IsNull(cbo) Then
    10.   MsgBox "You must supply Referred From information", vbExclamation, "No Referral"
    11.     cbo.SetFocus
    12.       cbo.Dropdown
    13.         Cancel = True
    14. ElseIf IsNull(txt) Then
    15.   MsgBox "You must supply a Referral Date", vbExclamation, "No Referral Date"
    16.     txt.SetFocus
    17.       Cancel = True
    18. End If
    19.  
    20. Exit_Form_Unload:
    21.   Exit Sub
    22.  
    23. Err_Form_Unload:
    24.   MsgBox Err.Description, vbExclamation, "Error in Form_Unload()"
    25.     Resume Exit_Form_Unload
    26. 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.
    Expand|Select|Wrap|Line Numbers
    1. Private Sub btnBack_Click()
    2. On Error GoTo Err_btnCloseForm_Click
    3.  
    4.     DoCmd.Close
    5.  
    6. Exit_btnCloseForm_Click:
    7.   Exit Sub
    8.  
    9. Err_btnCloseForm_Click:
    10.   If Err.Number <> 2501 Then    'NOT the Form's Close cancelled
    11.     MsgBox Err.Description & Err.Number, vbExclamation, "Error in btnCloseForm_Click()"
    12.   End If
    13.     Resume Exit_btnCloseForm_Click
    14. End Sub

Share this Question
Share on Google+
15 Replies


ADezii
Expert 5K+
P: 8,597
For the 2nd Test, your syntax is incorrect, namely vbOKOnly is not a valid MsgBox Result, and you cannot tack on the And condition:
Expand|Select|Wrap|Line Numbers
  1. If IsNull(Forms![Frm_Case]![Frm_Individual_Details].Form![cboReferredFrom]) Then
  2.   If MsgBox("You must specify where the client was referred from." & vbCrLf & vbCrLf & _
  3.     "Press 'OK' to return and select a referral service.", _
  4.     vbOKOnly, "Referred From Service Required") = vbOK Then
  5.       [Frm_Case]![Frm_Individual_Details].Form![cboReferredFrom].SetFocus
  6.   End If
  7. Else
  8.     DoCmd.Close
  9. End If
P.S. - This type code normally resides in the Form's BeforeUpdate() Event.
Mar 31 '10 #2

100+
P: 109
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?
Mar 31 '10 #3

ADezii
Expert 5K+
P: 8,597
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.
Mar 31 '10 #4

100+
P: 109
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!
Mar 31 '10 #5

100+
P: 109
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!)
Expand|Select|Wrap|Line Numbers
  1. If Len(Me!cboReferredFrom & vbNullString) = 0 Then
  2.   MsgBox "Please select where the client was referred from"
  3.   Cancel = True
  4.   Me!cboReferredFrom.SetFocus
  5. 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).
Apr 1 '10 #6

Belimisimus
P: 18
@hedges98
Check this post, I think you can find answer there...
http://bytes.com/topic/access/answer...ons-can-posted
Apr 1 '10 #7

100+
P: 109
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!
Apr 1 '10 #8

100+
P: 109
Still struggling with this if anyone has any pointers!
Apr 6 '10 #9

ADezii
Expert 5K+
P: 8,597
Can you Upload the DB?
Apr 6 '10 #10

100+
P: 109
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
Attached Files
File Type: zip CSF_Alcohol_Interventions NEW v3.zip (633.3 KB, 177 views)
Apr 6 '10 #11

ADezii
Expert 5K+
P: 8,597
I'll llok at it later, but I really can't do much without the Password (LOL).
Apr 6 '10 #12

100+
P: 109
D'oh! Did that last time. I'll PM you it now!
Apr 6 '10 #13

Expert 100+
P: 344
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.

Expand|Select|Wrap|Line Numbers
  1. If Not Form_Check_NULL(Me, strError) Then
  2.     InCompleteFields = strError
  3. Else
  4.     InCompleteFields = ""
  5. End If
  6.  
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
Expand|Select|Wrap|Line Numbers
  1. Public Function Form_Check_NULL(ByVal frm As Form, ByRef strError As String) As Boolean
  2. On Error GoTo Form_Check_NULL_Err
  3. Dim ctl As Control
  4. Dim Form_Valid As Boolean
  5. Form_Valid = True
  6. strError = ""
  7. For Each ctl In frm.Controls
  8.     Select Case ctl.ControlType
  9.     Case acTextBox, acComboBox, acListBox, acOptionGroup, acCheckBox
  10.         If ctl.Name <> "cboFindRec" And IsNull(ctl.Value) And ctl.Enabled = True And ctl.Visible = True And ctl.Locked = False Then
  11.             strError = strError & TranFieldName(ctl.Name) & "/"
  12.             Form_Valid = False
  13.         End If
  14.     End Select
  15. Next ctl
  16. Form_Check_NULL = Form_Valid
  17. Form_Check_NULL_Exit:
  18.    Exit Function
  19. Form_Check_NULL_Err:
  20.    MsgBox Err.Description & " in Form_Check_NULL"
  21.    Resume Form_Check_NULL_Exit
  22. End Function
  23.  
  24.  
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.
Apr 6 '10 #14

ADezii
Expert 5K+
P: 8,597
  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.
    Expand|Select|Wrap|Line Numbers
    1. Private Sub Form_Unload(Cancel As Integer)
    2. On Error GoTo Err_Form_Unload
    3. Dim cbo As ComboBox
    4. Dim txt As TextBox
    5.  
    6. Set cbo = Me!Frm_Individual_Details.Form![cboReferredFrom]
    7. Set txt = Me!Frm_Individual_Details.Form![Per_Referral_Date]
    8.  
    9. If IsNull(cbo) Then
    10.   MsgBox "You must supply Referred From information", vbExclamation, "No Referral"
    11.     cbo.SetFocus
    12.       cbo.Dropdown
    13.         Cancel = True
    14. ElseIf IsNull(txt) Then
    15.   MsgBox "You must supply a Referral Date", vbExclamation, "No Referral Date"
    16.     txt.SetFocus
    17.       Cancel = True
    18. End If
    19.  
    20. Exit_Form_Unload:
    21.   Exit Sub
    22.  
    23. Err_Form_Unload:
    24.   MsgBox Err.Description, vbExclamation, "Error in Form_Unload()"
    25.     Resume Exit_Form_Unload
    26. 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.
    Expand|Select|Wrap|Line Numbers
    1. Private Sub btnBack_Click()
    2. On Error GoTo Err_btnCloseForm_Click
    3.  
    4.     DoCmd.Close
    5.  
    6. Exit_btnCloseForm_Click:
    7.   Exit Sub
    8.  
    9. Err_btnCloseForm_Click:
    10.   If Err.Number <> 2501 Then    'NOT the Form's Close cancelled
    11.     MsgBox Err.Description & Err.Number, vbExclamation, "Error in btnCloseForm_Click()"
    12.   End If
    13.     Resume Exit_btnCloseForm_Click
    14. End Sub
Apr 7 '10 #15

100+
P: 109
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!
Apr 7 '10 #16

Post your reply

Sign in to post your reply or Sign up for a free account.