I am new to using code in Access, always used features in design mode. I have managed to pull a couple things off and make my way through some of this code, but one thing is holding me up. I have a form "frmISMci" that I have inserted a "add new record button" on. This has the effect of saving the current record and adding a new record. The only thing is, before the record is saved and a new record is added I would like an event procedure that would require certain fields to be "is not null" before the record is saved and in the case that certain fields are null and the record cannot yet be saved a message box esplain to the user that they must input a value for a given field. So for the save button there needs to be an event procedure "On click" that essentially tells the user the must fill out the given fields before a record can be added and the current saved. I realize I can use the required option for the fields, but that will only tell the user "you cannot go to the specified record" if left blank. Can anyone point me in the right direction. Thanks so much. -Justin

Here is an example of what I have so far. This has an effect of telling the user that the sum of all the appoprtiation fields must = 100%, but it doesnt prevent the user from saving the record. It simply pops the message box up and then saves the record. This is the code for the on click event procedure of my add new record button:

Private Sub Command168_Click()
If ([GAppropriation] + [AAppropriation] + [LAppropriation] + [IAppropriation] + [SAppropriation] + [WAppropriation] + [OAppropriation]) <> 100 Then
MsgBox "Total % Appropriation must equal 100%"
Exit Sub
End If
On Error GoTo Err_Command168_Click


    DoCmd.GoToRecord , , acNewRec

Exit_Command168_Click:
    Exit Sub

Err_Command168_Click:
    MsgBox Err.Description
    Resume Exit_Command168_Click

End Sub

Hi Justin,

Here's a version of the null check piece.

The cmdSave_Click event calls a function called ValidateControls. ValidateControls passes the name of all the textboxes on your form, plus a "friendly" name (the name that will appear in a message box) to a function called CheckForNulls using a parameter array. The parameter array allows you to send a variable number of controls to be checked for null. CheckForNulls then checks to see if one or multiple text boxes contain a null value, and returns the appropriate message to the Validate controls function, which in turn returns the message to the cmdSave_Click event, where a message box is displayed. If all the text boxes have been filled in appropriately, cmdSave_Click simply advances your form to a new record.

Just replace the textbox names (e.g., "txtName") and "friendly" names (e.g., "Name") in the ValidateControls function with the names of your text boxes and a user-friendly name for them (possibly your label caption). The ValidateControls function and the CheckForNulls function get pasted right in your form module along with the cmdSave_Click event procedure. You don't need to change anything in the CheckForNulls function.

Private Sub cmdSave_Click()

    ' Check for null values
    strOK = ValidateControls
    If Len(strOK) > 0 Then
        MsgBox strOK, vbInformation, "Required Information Missing"
        Exit Sub
    End If

    'Do your percentage check...
    If ([GAppropriation] + etc...

    ' If it all cecks out, save record
    DoCmd.GoToRecord , , acNewRec

End Sub

Private Function ValidateControls() As String

    Dim strMsg As String

    ' Text box name, user-friendly name
    strMsg = CheckForNulls(Me, _
      "txtName", "Name", _
      "txtAddress", "Address", _
      "txtPhone", "Phone")

    ValidateControls = strMsg

End Function

Public Function CheckForNulls(frm As Form, _
    ParamArray varListOfControlsAndLabels()) As String

  Dim intI As Integer
  Dim ctl As Access.Control
  Dim strMsg As String
  Dim strFirstNullControl As String
  Dim fNullFound As Boolean
  Dim fMultipleNullsFound As Boolean

  For intI = LBound(varListOfControlsAndLabels) _
    To UBound(varListOfControlsAndLabels) Step 2

    Set ctl = frm(varListOfControlsAndLabels(intI))

    If ctl.ControlType = acTextBox Then
        fNullFound = IsNull(ctl)
    End If

    If fNullFound Then
      strMsg = strMsg & varListOfControlsAndLabels(intI + 1) & vbCrLf
      'Store the first null Control, to set focus to it later.
      If Len(strFirstNullControl) = 0 Then
        strFirstNullControl = varListOfControlsAndLabels(intI)
      Else
        'More than one null was found
        fMultipleNullsFound = True
      End If
    End If
  Next intI

  If Len(strMsg) > 0 Then 'at least one null was found
    If Not fMultipleNullsFound Then
      'There was only one null Control found.
      'Use single case in message. Trim trailing CrLf.
      strMsg = Left(strMsg, Len(strMsg) - 2)
      strMsg = """" & strMsg & """ is a required entry. " _
       & "Please fill in a value for this item."
    Else
      'There was more than one null Control found.
      ' Use plural case in message.
      strMsg = "The following required entries were left blank:" _
       & vbCrLf & vbCrLf & strMsg & vbCrLf _
       & "Please fill in values for these items."
    End If

    frm(strFirstNullControl).SetFocus
  End If

  CheckForNulls = strMsg

End Function

Or for a simpler approach you could go with this sort of thing:

Private Sub cmdSave_Click()
    If IsNull(Me.[GAppropriation]) Or _
        Len(Me.([GAppropriation]) = 0 Then
        MsgBox "[GAppropriation]Must be Filled In Before Proceeding"
        [GAppropriation].SetFocus
        Exit Sub
    End if

    DoCmd.GoToRecord , , acNewRec

End Sub

HTH,

Bob

For the simplest approach, design your tables so that those fields are required.

mmcdonal

If you still want to avoid code you can open the table in design view. For each field you do not want blank, set the following properties:

Required: YES
Allow Zero Length: NO

Now when the user tries to save a record and move on, Access won't let them. The good news is that no coding is required. The bad news is that Access's built-in error messages are very cryptic. Your users may not understand exactly what the error message is telling them.

Try it.


Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division

Bob, Thank you so much for your reply. This seems like some great code and I am going to move forward with trying to implement it. Thanks for taking the time to help. Greg, I had already set the fields to required, but like you said the reason I need this code is because the error message is in fact very cryptic. I needed to tell the user exactly what they had done wrong. Also, do you all hard code your buttons or use button wizards. Just curious how the gurus do it. I guess I will pick up a VB book. Thank you all for your help and suggestions.

-Justin

I usually use the button wizard to create the button and insert the "seed code" then I alter the code to do exactly what I want. It saves a few keystrokes. For buttons that do things "out of the ordinary," I code it myself from scratch without the Wizard.

Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division

Hi Justin,

I don't use the Wizards, but then I have a lot of boiler plate code I've accumulated in code libraries over time that I just reference or cut and paste a lot. The CheckForNulls routine, for example, is a function that I keep in a library .mda add-in and can reference from any application I'm working on.

Since you're book shopping, however, I wanted to make a recommendation. Check out http://www.developershandbook.com/. Best Access VBA book around with tons of sample code. You'll love it if the coding bug has bit, as it appears it has.

Best,

Bob