Results 1 to 11 of 11
  1. #1
    Join Date
    Aug 2004
    Posts
    34

    Unanswered: Required Fields on form

    I have a form with fields bound to a table. Some of the fields are required. On my form I have a close form button. The problem is that the user can close the form without being prompted to fill in required fields, hense not creating a record in the table. Is ther a way to prevent the form from closing if required fields are not filled in? I am new to Access. Maybe code but would it be put in the events for the close button or on the form before update event.

  2. #2
    Join Date
    Dec 2003
    Posts
    268

    Required Fields

    One thing I have done is create a collection of required fields for a form on the load event.

    dim colRequiredFields as new collection
    'fill the collection with the field's name
    colrequiredfields.add "txtField1"

    then on the close event you can cycle through the items in the collection.

    dim iCount as integer
    dim ctl as control

    for icount = 1 to colrequiredfields.count
    set ctl = me.controls(colrequirefields(icount))
    if len(trim(ctl.value)) = 0 then
    'this makes the assumption that the tag is holding the 'name' of the field so a user can understand
    msgbox "Please enter a value for " & ctl.tag , vbcritical, "Error, YOU SUCK"
    ctl.setfocus
    exit loop
    end if
    next icount

    This should work for text boxes as long as there aren't any default values that are initially stored in the text box etc.

  3. #3
    Join Date
    Nov 2003
    Posts
    1,487
    The best place to put code to handle this sort of thing is into the Form's UnLoad event. You see, if you place the handling code into your Close or Exit command button then it will not be executed if the user decides to close the Form via the Window close button [X] - you know, the small red box with a x in it in the upper right corner of your Form.

    In the Form's Unload event, you might have something like this:

    Code:
    Private Sub Form_Unload(Cancel As Integer)
    If IsNull(Me.my1stRequiredFieldName) Or IsNull(Me.my2ndRequiredFieldName) Then
    	 Cancel = True
    	 MsgBox"Some required fields have not been completed."
    End If
    End Sub
    Another way you could tackle this, especially if you have a LOT of "required fields" is to flip through all the controls on the Form (again from the Unload event) and check each control that has a specific Tag (as suggested earlier) to see if that control field has indeed been filled.

    For each of your 'Required Fields' on form, go to the properties widow for each control and in the Other tab, you will see a property named Tag. Enter a asterisk (*) into the Tag property. Now in the Form's Unoad event, copy and paste the following code:

    Code:
    Private Sub Form_Unload(Cancel As Integer)
       Dim Ctrl As Control
       For Each Ctrl In Me.Controls
    	   If Ctrl.Tag = "*" And IsNull(Ctrl) Then
    		  MsgBox"A required Field has not been filled."
    		  Cancel = True
    		  Ctrl.SetFocus
    		  Exit For
    	   End If
       Next Ctrl
    End Sub
    Last edited by CyberLynx; 11-20-04 at 15:25.

  4. #4
    Join Date
    Sep 2003
    Posts
    41

    hope we can go a little farther with this

    i have tried your code which works great on unload event but the problem is
    i use a close button not the "x" to close the form and when i hit the button it finds the records with the "*" spits out the message goes to the control to fill it in then closes the form before they can. how can i stop the docmd.close in my close button if the data hasnt been entered right. seems like the close button ignores the unload events code to cancel.

  5. #5
    Join Date
    Nov 2003
    Posts
    1,487
    Then use the code (with some modifications) in a function and call that function to check all the fields in both the Form's Unload event and the Close button's OnClick event:

    Copy and paste this function into your Form's code module:

    Code:
    Private Function CheckAllFields () As Boolean 
      Dim Ctrl As Control
      CheckAllFields = False
      For Each Ctrl In Me.Controls
    	 If Ctrl.Tag = "*" And IsNull(Ctrl) Then
    				MsgBox"A required Field has not been filled."
    		CheckAllFields = True
    		Exit For
    	 End If
      Next Ctrl
    End Function
    In the OnClick event for your close button, supply this line of code above all other code in the event:

    If CheckAllFields = True Then Exit Sub

    In the Form's Unload event, supply this line of code above all other code in the event:

    If CheckAllFields = True Then Cancel = True : Exit Sub



  6. #6
    Join Date
    Sep 2003
    Posts
    41

    works perfect

    Thank you very much works perfect.

  7. #7
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926

    Required Fields

    Another thing I use is the Format - Conditional Format on required fields. Click on the field and change the Condition to Expression for that field and then adding in a line like this:
    IsNull(Forms!CustomerForm!Company) and make the background red.

  8. #8
    Join Date
    Aug 2004
    Posts
    34
    I have tried the code and under most situations it works great, but if the user enters the form accidentally, or doesn't really want to be in this particular form, they are unable to exit because of the code linked to the "unload event" I hope this makes sense.

  9. #9
    Join Date
    Sep 2004
    Location
    Tampa, FL
    Posts
    520
    You can always add a button your meesage box for "close anyway" or the like.
    Darasen

  10. #10
    Join Date
    Aug 2004
    Posts
    34
    I am new to VB...are you suggesting that I can add a button inside the meesage box that will have code to close anyway?

  11. #11
    Join Date
    Nov 2003
    Posts
    1,487
    In the case for Falcon049, You will need to see if some condition is set. Did the user start to enter data or, simply entered the form by mistake and wants to exit without entering any data (such as would be the case in a new record).

    You may then need to implement in your code to see whether or not the form is Dirty. See Access help about the Dirty property.


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •