View Single Post
  #3 (permalink)  
Old January 12th, 2005, 08:42 PM
Bob Bedell Bob Bedell is offline
Friend of Wrox
Join Date: Jun 2003
Location: , , USA.
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts

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)
        '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."
      '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

  End If

  CheckForNulls = strMsg

End Function