Results 1 to 10 of 10
  1. #1
    Join Date
    Jan 2013
    Posts
    53

    Unanswered: Find blank fields in Form

    Hello All, in need of your expertise

    Have the following code designed to flag required fields on a Form left blank. Am unsure where exactly to place this code, either in a separate Module or in the Before Update for each of the required fields? Any assistance is greatly appreciated!

    Code:
    Private Sub TXTLENGTH_R_BeforeUpdate(Cancel As Integer)
    
    Dim ctl As Control
    Dim strMsg As String
     
     ' loop through every control on the form
    For Each ctl In Me.Controls
         ' look for a particular tag
        If ctl.Tag = "Check" Then
             ' create a list of the empty questions
            If IsNull(ctl) Then
                strMsg = strMsg & "-  " & ctl.Name & vbCrLf
            End If
        End If
    Next ctl
     ' are there any blank fields?
    If strMsg <> "" Then
        If vbNo = MsgBox("The following field has been left blank" & vbCrLf & vbCrLf & _
        strMsg & vbCrLf & vbCrLf & "Please enter a value", _
        vbQuestion + vbYesNo + vbDefaultButton2, "Blank Fields") Then
            Cancel = True
        End If
    End If
    
    End Sub

  2. #2
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    994
    Provided Answers: 2
    That code will check all of the controls with the specified tag. Therefore, you only want to call it once per user attempt to move away from the record. I would advise putting it on one of the form events, or if you're using a command button to save records or navigate, put it there.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  3. #3
    Join Date
    Jan 2013
    Posts
    53
    Thanks for your response weejas. Here is the code I have thus far attached to the Add command button. Each of the controls listed below have the ''Check" tag but when I test and leave blank fields, it automatically proceeds and adds the records to the Parts Updated tbl. What am I missing here?

    Code:
    Private Sub cmdADD_Click()
    
    Dim ctrk As Control
    Dim strMsg As String
     
     ' loop through every control on the form
    For Each ctl In Me.Controls
         ' look for a particular tag
        If ctl.Tag = "Check" Then
             ' create a list of the empty questions
            If IsNull(ctl) Then
                strMsg = strMsg & "-  " & ctl.Name & vbCrLf
            End If
        End If
    Next ctl
     ' are there any blank fields?
    If strMsg <> "" Then
        If vbNo = MsgBox("The following field has been left blank" & vbCrLf & vbCrLf & _
        strMsg & vbCrLf & vbCrLf & "Please enter a value", _
        vbQuestion + vbYesNo + vbDefaultButton2, "Blank Fields") Then
            Cancel = True
        End If
    End If
    
        CurrentDb.Execute "INSERT INTO [Parts Updated]([Part Number],Description,Length,Width,Height,Weight,[Package Type],[Measured Pack QTY],[Arrow Orientation],[Max Bank],CCODE,[Bin Profile])" & _
        " VALUES('" & Me.TXTPART_R & "','" & Me.TXTDESC_R & "','" & Me.TXTLENGTH_R & "','" & _
            Me.TXTWIDTH_R & "','" & Me.TXTHEIGHT_R & "','" & Me.TXTWEIGHT_R & " ','" & Me.TXTPackageType_R & "','" & Me.TXTMeasuredPackQTY_R & "','" & Me.TXTArrowOrientation_R & "','" & Me.TXTMAXBANK_R & "','" & _
            Me.TXTCCODE_R & "','" & Me.TXTBIN_R & "')"
            Me.TXTLENGTH_R = ""
            Me.TXTWIDTH_R = ""
            Me.TXTHEIGHT_R = ""
            Me.TXTWEIGHT_R = ""
            Me.TXTPackageType_R = ""
            Me.TXTMeasuredPackQTY_R = ""
            Me.TXTArrowOrientation_R = ""
            Me.TXTMAXBANK_R = ""
            Me.TXTCCODE_R = ""
            Me.TXTBIN_R = ""
            DoCmd.Requery
        Me.TXTPART_R.SetFocus
        MsgBox "Part Updated Successful"
        
    End Sub

  4. #4
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    994
    Provided Answers: 2
    The Click subroutine has no Cancel argument. Replace "Cancel = True" with "Exit Sub".
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  5. #5
    Join Date
    Jan 2013
    Posts
    53
    Still a no go...records not left blank are still added to the Parts Updated tbl.

  6. #6
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    994
    Provided Answers: 2
    Well, you have *told* it to do that. You're asking a question with a yes/no answer. If they answer no, my suggested amendment above will make the subroutine stop. If they answer yes, the rest of the subroutine will run.

    You need to rethink the process. What do you want to happen in response to each possible answer? When you have worked that out, write the appropriate code.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  7. #7
    Join Date
    Jan 2013
    Posts
    53
    For some reason the msg box does not even prompt after clicking cmdAdd for the user to select yes or no...

  8. #8
    Join Date
    Jan 2013
    Posts
    53
    ...after some further testing this code works but only after the an entry is deleted. Seems the controls need to be activated or something...

  9. #9
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    994
    Provided Answers: 2
    As your code stands, the message box will only be invoked if one or more "Check"-tagged controls are left blank. If no such controls are unpopulated, then the code will cause the record to be saved.

    If the subroutine is not behaving as you intended, put a break on the very first line and step through it. Look at the value of each control and variable as you get to it, and see what's causing the problem.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  10. #10
    Join Date
    Jan 2005
    Posts
    146
    Before Update events can be canceled and so your code should be placed in the forms Before Update event.
    Last edited by billmeye; 05-16-13 at 19:55.

Posting Permissions

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