Results 1 to 11 of 11
  1. #1
    Join Date
    Oct 2006
    Posts
    110

    Question Unanswered: Using Mousemove() to check for nulls in fields

    Hello all.. I am wanting to check for nulls or blanks before sending my email from the command button. I have about 12 fields i would like to check, is there a easier way to do this? I would probably be better off using the On Click function, if I do, how would I end the code if a null is found, and not continue sending the email? Thanks


    Youll have to excuse me, I know this is long:

    Private Sub MAIL_MouseMove(Button As Integer, Shift As Integer, X As Single, Y As Single)
    'Check for Nulls Before Sending Email
    If IsNull(Me.Combo14) Then
    MsgBox "Please Complete Record Before Sending."
    cancel = True
    End If
    If IsNull(Me.Combo18) Then
    MsgBox "Please Complete Record Before Sending."
    cancel = True
    End If
    If IsNull(Me.NUM_REP) Then
    MsgBox "Please Complete Record Before Sending."
    cancel = True
    End If
    If IsNull(Me.Combo77) Then
    MsgBox "Please Complete Record Before Sending."
    cancel = True
    End If
    If IsNull(Me.DATE_MAILED) Then
    MsgBox "Please Complete Record Before Sending."
    cancel = True
    End If
    If IsNull(Me.DATE_RECD) Then
    MsgBox "Please Complete Record Before Sending."
    cancel = True
    End If
    If IsNull(Me.TRACKING1) Then
    MsgBox "Please Complete Record Before Sending."
    cancel = True
    End If
    If IsNull(Me.TRACKING2) Then
    MsgBox "Please Complete Record Before Sending."
    cancel = True
    End If
    If IsNull(Me.TRACKING3) Then
    MsgBox "Please Complete Record Before Sending."
    cancel = True
    End If
    If IsNull(Me.TOTAL1) Then
    MsgBox "Please Complete Record Before Sending."
    cancel = True
    End If
    If IsNull(Me.TOTAL2) Then
    MsgBox "Please Complete Record Before Sending."
    cancel = True
    End If
    If IsNull(Me.TOTAL3) Then
    MsgBox "Please Complete Record Before Sending."
    cancel = True
    End If

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Oh, you want that in the click event. Just use:

    Exit Sub

    if a Null is found to exit the sub before sending the mail.
    Paul

  3. #3
    Join Date
    Oct 2006
    Posts
    110
    Can you please elaborate?

  4. #4
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    I'm assuming since these fields are going to be part of an email they're all text controls. You can use this to loop thru them without actually naming them, and they'll tell you which ones are empty:

    Code:
    Private Sub Mail_Click()
    Dim ctrl As Control
    
      For Each ctrl In Me.Controls
        If TypeOf ctrl Is TextBox Then
            If IsNull(ctrl.Value) Then
              MsgBox (ctrl.Name & " is Blank!")
            End If
        End If
     Next
    End Sub
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  5. #5
    Join Date
    Oct 2006
    Posts
    110
    Missinglinq, Yes they are text and date fields

    That is great.. so much easier..

    I also have fields that are not required, so I dont want to bother to check them.. How would I exclude those?

    Would I also use exit sub at the end, to stop before sending the email.
    Last edited by cmays637; 01-02-07 at 23:44.

  6. #6
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    There's a little known property of controls called Tags. You can hide all kinds of stuff in there, then check the Tag property and use what 's there to decide whether to do something or not, like check the control or not!

    Goto the Property Box of any control you DON’T want to check, goto the Other Tab, and under the Tag property enter the word

    skip

    just like that, no quotation marks there!

    Now copy and paste this new version of the code over the old:

    Code:
    Private Sub Mail_Click()
    
    Dim ctrl As Control
    
    For Each ctrl In Me.Controls
        If TypeOf ctrl Is TextBox Then
            If IsNull(ctrl.Value) Then
              If ctrl.Tag <> "skip" Then
               MsgBox (ctrl.Name & " is Blank!")
              End If
            End If
        End If
     Next
    End Sub
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  7. #7
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Sorry, about the Exit Sub to avoid sending the email that would be yes I think!
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  8. #8
    Join Date
    Oct 2006
    Posts
    110
    Thanks.. I learn something new everyday.. I added the exit sub to stop the code if it is missing required fields. I also have comboboxes, can I include those in this?

    How can I set these to be check in my tab order? It looks random to me..



    Dim ctrl As Control

    For Each ctrl In Me.Controls
    If TypeOf ctrl Is TextBox Then
    If IsNull(ctrl.Value) Then
    If ctrl.Tag <> "skip" Then
    MsgBox (ctrl.Name & " is Blank!")
    Exit Sub
    Exit Sub
    Exit Sub
    End If
    End If
    End If
    Next
    Last edited by cmays637; 01-03-07 at 00:18.

  9. #9
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    You need to place your Exit Sub after the Next statement or you'll exit the sub after only checking one control!

    I'm just guessing here, never really checked a combobox but try replacing

    If TypeOf ctrl Is TextBox Then

    with

    If (TypeOf ctrl Is TextBox) or (TypeOf ctrl Is ComboBox) Then
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  10. #10
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    After I shut down last nite and visions of code were dancing in my head I got to wondering why you wanted to Exit Sub and not finish running code to send email. Realized you had to correct deficits so I changed the message boxes to Input boxes. Try this:

    Code:
    Private Sub Mail_Click()
    
    Dim ctrl As Control
    Dim Message, Title, Default, MyValue
    
    For Each ctrl In Me.Controls
        If (TypeOf ctrl Is TextBox) Or (TypeOf ctrl Is ComboBox) Then
            If IsNull(ctrl.Value) Then
              If ctrl.Tag <> "skip" Then
               'MsgBox (ctrl.Name & " is Blank!")
               Message = "Enter Value for " & ctrl.Name
              ctrl.Value = InputBox(Message, Title, Default)
              End If
            End If
        End If
     Next
    'Your code for sending email here
    End Sub
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  11. #11
    Join Date
    Oct 2006
    Posts
    110
    This is good.. Thanks Missinglinq for the help!

    Chris

Posting Permissions

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