Results 1 to 8 of 8
  1. #1
    Join Date
    Nov 2003
    Posts
    66

    How to validate mandatory fields in a new record on data entry form

    Hi all,

    This is something I would have thought was fairly basic, but it's turning into a big headache - hopefully someone knows how to solve it....

    I have a data entry form that has 4 fields that can't be left blank. (I'm not fussy where the validation rule lies, whether on the underlying table, or the form itself.)
    The form is made up of a main form where actual new data is entered, and a subform that's present for display purposes, but that contains information I need to record alongside the newly entered data. I've an append query that runs at the click of a 'save' button which collates the info on the display subform, plus the newly entered data. In order to get the append query to work, I've a 'saverecord' command that runs to put the newly entered data into a temporary table that the append query uses. (Hands up who's confused). This all actually works fine..!

    Here's the problem:

    1) Using 'mandatory' validation at table level - If the user attempts to save the record having left a required field blank, the error message the system displays has a 'debug' button that opens up the code, highlighting 'DoCmd.RunCommand acCmdSaveRecord'. If I take that code out, and put the 'saverecord' command in a macro instead, it gives me the 'action failed' dialog with the 'halt' button, when the field is left blank. Not exactly graceful error handling in either instance....

    2) So, I tried validating each field individually on the form itself.
    When the form opens up, it's in data entry mode, displaying a blank record. I put a validation rule on the fields in question with corresponding warning text, and the problem is that the rule doesn't seem to kick in unless I type something in the field, delete it, and THEN go to the next field. Error message appears fine in that instance. But when the form opens up first, and I just tab through the empty fields I don't get the error messages I need on the required fields.

    Has anyone seen this problem before?
    I was thinking of maybe putting another button on the form that could be used before the 'saverecord' button (avoiding the problem in 1 above) that would run validation code on the fields in question. I've very little VB coding experience and dunno where to start writing it, though.....

    Sorry about the essay. Hope someone has some ideas on how to get around this irritating prob...

    Cheers
    Dave

  2. #2
    Join Date
    Feb 2002
    Posts
    2,232
    What event are you using to validate (beforeupdate ?) ?

  3. #3
    Join Date
    Nov 2003
    Posts
    1,487
    Try this in the BeforeUpdate event of your data entry FORM:

    If IsNull(Me.MandFielod1) or IsNull(Me.MandField2) or IsNull(Me.MandField3) then
    MsgBox"HEY...Come on...Place data in ALL the fields! "
    Cancel = True
    End If


    Of course...you'll be a little more creative with your message

    Here is another method:

    Dim a$, Ctrl As Control
    For Each Ctrl In Me.Form
    If IsNull(Ctrl) Then
    a$ = "You Need to enter data in ALL fields"
    Ctrl.SetFocus
    Cancel = True
    End If
    Next Ctrl
    If a$ <> "" Then MsgBox a$

    This will Check ALL fields on your form.

    Or you could do it this way:

    Dim a$, Ctrl As Control
    For Each Ctrl In Me.Form
    If Ctrl.ControlType = acTextBox And IsNull(Ctrl) Then
    a$ = "You Need to enter data in ALL fields"
    Ctrl.SetFocus
    Cancel = True
    End If
    Next Ctrl
    If a$ <> "" Then MsgBox a$

    This will check all TextBoxes ONLY on your data entry form.
    Remember...place any code into your form's BeforeUpdate event.
    Last edited by CyberLynx; 11-30-03 at 03:06.

  4. #4
    Join Date
    Nov 2003
    Posts
    66
    Thanks for the responses, guys.

    rnealejr - To validate the individual form fields I made a conditional macro (ie where value of the field = "", display the msgbox). Tried it on onLostFocus, beforeupdate, afterupdate, and even tried it on the onGotFocus event of the NEXT field, but still only works if something is typed in the field, and then deleted.

    CyberLynx - tried your code on the beforeupdate event of the form, and I'm still getting a conflict with the 'saverecord' command I have on the form. I need this to be present to put the new details into a temporary table that the append query on the form uses. I'm getting a "Run-Time error 2501 - the RunCommand Action was canceled" dialog box with a nice chunky debug button. It doesn't like the 'Cancel = True'.
    It's fair enough if the 'save' command is canceled, just wish Access didn't have to scream and shout about it.....

    The only thing I can think of is to put the validation code you gave me on a new button on the form, something like 'Are your details correct?'. This would run the code, keeping it seperate from the 'saverecord' command. Once clicked, and if the code doesn't find any problems, I could just unlock the 'save' button.
    Does this seem like an acceptable workaround? It's an extra click for the user, but I need to make sure I get all the required info for report generation later on.....

    D

  5. #5
    Join Date
    Aug 2003
    Location
    Belgrade
    Posts
    68

    Re: How to validate mandatory fields in a new record on data entry form

    Originally posted by daver
    go to the next field. Error message appears fine in that instance. But when the form opens up first, and I just tab through the empty fields I don't get the error messages I need on the required fields.

    Has anyone seen this problem before?
    This is normal behaviour, as Access waits for you to finish editing all the fields of the record before it decides whether the data is acceptable or not. If you want a message to appear as sooon as you tab out of a blank field that should not be left blank, then you must use the LostFocus event of the text box. If the box is empty, then you set the focus back to that box.

    But usually this doesn't work very well, as this way you don't let the user leave the box before filling it. What if he (or she) has no idea what to write into the box and just wants to close the form?

    As one poster suggested, a better idea is to use the BeforeUpdate event of the Form object and test whether there is any inacceptable data. If there is, set Cancel = True in order to prevent writing data into the database.

    I use code such as this:

    Sub Form_BeforeUpdate
    Dim strErrors As String

    If <control1 no good data> Then
    strErrors = strErrors & vbCrLf & "Missing or unacceptable data in control 1"
    endif

    If <control2 no good data> Then
    strErrors = strErrors & vbCrLf & "Missing or unacceptable data in control 2"
    endif
    ...
    ...
    If <controlN no good data> Then
    strErrors = strErrors & vbCrLf & "Missing or unacceptable data in control N"
    Endif

    If strErrors <> "" Then
    MsgBox "Errors while attempting to save data:" & strErrors
    Cancel = True
    EndIf

    End Sub

  6. #6
    Join Date
    Nov 2003
    Posts
    66
    >>>If you want a message to appear as sooon as you tab out of a >>>blank field that should not be left blank, then you must use the >>>LostFocus event of the text box.

    Tried that, mashinovodja, but as I said, only time it works is if something is first typed into the field, deleted, and then I tab out of the field. If I just open the form, and tab through the blank fields, the error message doesn't appear.

    >>>a better idea is to use the BeforeUpdate event of the Form object >>>and test whether there is any inacceptable data. If there is, set >>>Cancel = True in order to prevent writing data into the database.

    Makes sense, but can't use this either, because of the 'saverecord' command I have on the form that's needed for the append query. The 'Cancel = True' conflicts with the save command, and Access throws up the run-time error....

    No problem, though - I've used a different workaround that seems to be working OK.
    Thanks for the help!

    D

  7. #7
    Join Date
    Nov 2003
    Posts
    1,487

    Smile

    daver,

    You could take one the the coded methods I described earlier and place it into a function (as I have done below) then call that function from both the FORM's 'BeforeUpdate' event and the 'Click' event of your Save Record button (or just from your Save button and forget about the BeforeUpdate event all together).

    For example:
    Copy the following function into the Declarations Section of your data entry form.

    Code:
    Private Function FieldsAreNotAllProcessed() As Boolean
         Dim a$, Ctrl As Control
         For Each Ctrl In Me.Form
               If Ctrl.ControlType = acTextBox And IsNull(Ctrl) Then
                   a$ = "You Need to enter data in ALL fields"
                   Ctrl.SetFocus
                   FieldsAreNotProcessed = True
                   Exit For
              End If
         Next Ctrl
         If a$ <> "" Then MsgBox a$
    End Function
    Now...copy and paste the following code into the 'Click' event for you Save Record button (of course, any other code you may have in this event will also need to be added). For example:

    Code:
    Private Sub MySaveRecordButton_Click()
         If FieldsAreNotAllProcessed = True Then 
               Exit Sub
         Else
               On Error Goto Whoops
               DoCmd.RunCommand acCmdSaveRecord 
               '.......any other code.....
         End if
    Exit_This:     
         Exit Sub
    Whoops:
        MsgBox Err.Description
        Resume Exit_This
    End Sub
    AND.....
    If you decide to also place a trap into the Form's 'BeforeUpdate' event, then you may want to place the following code into that event as well. Again, any other code you may already have in that event will need to be added as well but the line shown in this example should be the FIRST line in the event procedure:

    Code:
    Private Sub Form_BeforeUpdate(Cancel As Integer)
         Cancel = FieldsAreNotAllProcessed
         '.....any other code....
    End Sub
    Hope this helps...
    Last edited by CyberLynx; 12-01-03 at 17:07.

  8. #8
    Join Date
    Nov 2003
    Posts
    66
    Cheers for that, Cyberlynx - genuinely appreciate the help. This site's turning into a lifesaver....
    Gonna stick with my workaround on this one, tho - It only involves one extra click for the user. Being a VB noob, I figure it'll take a while to manipulate your code, so I'm cutting my losses and movin on. I've to present a demo of this system in a week and have a load more to finish b4 then. Believe me, I'll have ye hounded with plenty more dumb questions before the week is out..!

    Thanks again
    Dave

Posting Permissions

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