locked
How to stack IF statements so form fields won't allow blanks RRS feed

  • Question

  • I have a form that a user enters their initials, a received date, and an entered date prior to pressing a button that runs a bunch of queries to import and apply data. When the form opens it says, "You must enter your initials." As you tab through, if you leave any of the fields blank they will show a msgbox that says, you must enter a received date or an entered date, like the initials box does. The problem is that after I leave the field I can go back into it and delete what is entered and click the button. It errors because it needs the information from those fields to continue but doesn't stop me from going forward. I need it to focus back to the empty box so the user can fill it in.

    I tried to use stacked If statements but even that errored.

    This is what I have so far...

    Private Sub DateReceived_Exit(Cancel As Integer)
      If IsNull(Me![DateReceived]) Then
        MsgBox "You must enter the date the serials were received."
        Me![DateReceived].SetFocus
      End If
    End Sub

    Private Sub Initials_AfterUpdate()
        Me![Initials] = UCase(Me![Initials])
        If Len(Initials) >= 6 Then
        MsgBox "No more than 5 characters allowed."
        Else
        Me![Initials].SetFocus
        End If
    End Sub

    Private Sub Initials_GotFocus()
      If IsNull(Me![Initials]) Then
        MsgBox "You must enter your initials."
        Me![Initials].SetFocus
      End If
    End Sub

    Private Sub DateReceived_GotFocus()
      If IsNull(Me![DateReceived]) Then
        MsgBox "You must enter the date the serials were received."
        Me![DateReceived].SetFocus
      End If
    End Sub

    Thanks,

    Jessica

    Wednesday, April 17, 2019 1:22 PM

All replies

  • > The problem is that after I leave the field I can go back into it and delete what is entered and click the button

    You need to change your mindset. There is no point in forcing users in a straightjacket. Rather you wait until user hits the Continue button, and then you check all fields and complain if there are issues.


    -Tom. Microsoft Access MVP

    Wednesday, April 17, 2019 1:36 PM
  • Exactly and when I tried to put them all together in the After Click option they erred.

    What I posted above was how I started. 

    Wednesday, April 17, 2019 2:01 PM
  • I tried this...

    Private Sub Run_mRS_Click()
        If IsNull(Me![Initials]) Then
            MsgBox "You must enter your initials to continue."
            Me![Initials].SetFocus
        End If
        If IsNull(Me![DateReceived]) Then
            MsgBox "You must enter the date the serials were received to continue."
            Me![DateReceived].SetFocus
        End If
        If IsNull(Me![DateEntered]) Then
            MsgBox "You must enter the date you are importing the serials to continue."
            Me![DateEntered].SetFocus
        Else: DoCmd.RunMacro (mRS)
        End If
    End Sub

    I tried this...

    Private Sub Run_mRS_Click()
        If IsNull(Me![Initials]) Then
            MsgBox "You must enter your initials to continue."
            Me![Initials].SetFocus
        If IsNull(Me![DateReceived]) Then
            MsgBox "You must enter the date the serials were received to continue."
            Me![DateReceived].SetFocus
        If IsNull(Me![DateEntered]) Then
            MsgBox "You must enter the date you are importing the serials to continue."
            Me![DateEntered].SetFocus
        Else: DoCmd.RunMacro (mRS)
        End If
        End If
        End If
    End Sub

    Both give me the messages that the fields are blank but they don't focus back to those fields. It just cycles through the messages and then attempts to run the macro.

    What am I missing?

    Thanks,

    Jessica

    Wednesday, April 17, 2019 3:49 PM
  • Keep in mind that your code does not stop running when you hit the SetFocus.  The first example you gave will always RunMacro(mRS) unless DateEntered is null, regardless of how you've set the other two fields. You might want to try something similar to this:

    Private Sub Run_mRS_Click()
        If IsNull(Me![Initials]) Then
            MsgBox "You must enter your initials to continue."
            Me![Initials].SetFocus
            Exit Sub
        End If
        If IsNull(Me![DateReceived]) Then
            MsgBox "You must enter the date the serials were received to continue."
            Me![DateReceived].SetFocus
            Exit Sub
        End If
        If IsNull(Me![DateEntered]) Then
            MsgBox "You must enter the date you are importing the serials to continue."
            Me![DateEntered].SetFocus
            Exit Sub
        End If
       
        DoCmd.RunMacro (mRS)
        
    End Sub


    Wednesday, April 17, 2019 4:21 PM
  • Thank you, Bruce.

    I tried your suggestion and it gave me a compile error.

    Compile Error:

    Only comments may appear after End Sub, End Function, or End Property.

    Thanks,

    Jessica

    Wednesday, April 17, 2019 5:24 PM
  • The error message is correct, you should not have any code or other text besides comments after End Sub, End Function, or End Property unless you are starting another sub, function, or property.

    When you compile it should highlight whatever is appearing after your End Sub statement that is causing the problem (e.g. the "If True..." statement below should not be there and will cause the compile error you mentioned).

    Wednesday, April 17, 2019 5:42 PM
  • I tried your suggestion and it gave me a compile error.

    Hi Jessica,

    Bruce's code should work, at least, I cannot see why it would fail when you use it.

    An alternative could be:

    Private Sub Run_mRS_Click()
    
      If (IsNull(Me![Initials])) Then
        MsgBox "You must enter your initials to continue."
        Me![Initials].SetFocus
      ElseIf (IsNull(Me![DateReceived])) Then
        MsgBox "You must enter the date the serials were received to continue."
        Me![DateReceived].SetFocus
      ElseIf (IsNull(Me![DateEntered])) Then
        MsgBox "You must enter the date you are importing the serials to continue."
        Me![DateEntered].SetFocus
      Else
        DoCmd.RunMacro (mRS)
      End If
    
    End Sub
    
    

    Imb.

    Wednesday, April 17, 2019 5:50 PM
  • Hi Bruce,

    It highlights Me where I put it in Bold below. That's where it stops and throws the compile error. It does the same thing when I click Debug - Compile Database.

    Private Sub Run_mRS_Click()
        If IsNull(Me![Initials]) Then
            MsgBox "You must enter your initials to continue."
            Me![Initials].SetFocus
            Exit Sub
        End If
        If IsNull(Me![DateReceived]) Then
            MsgBox "You must enter the date the serials were received to continue."
            Me![DateReceived].SetFocus
            Exit Sub
        End If
        If IsNull(Me![DateEntered]) Then
            MsgBox "You must enter the date you are importing the serials to continue."
            Me![DateEntered].SetFocus
            Exit Sub
        End If
       
        DoCmd.RunMacro (mRS)
        
    End Sub

    Thanks,

    Jessica

    Wednesday, April 17, 2019 5:56 PM
  • This worked up until the Else DoCmd.RunMacro (mRS). It stopped there with Run-time error '2502': The action or method requires a Macro Name argument. 

    Thanks,

    Jessica

    Wednesday, April 17, 2019 6:12 PM
  • This worked up until the Else DoCmd.RunMacro (mRS). It stopped there with Run-time error '2502': The action or method requires a Macro Name argument. 

    Hi Jessica,

    … and you are the only one who knows that name.

    Imb.

    Wednesday, April 17, 2019 6:16 PM
  • I'm so confused.
    Wednesday, April 17, 2019 6:22 PM
  • Maybe you meant:

    DoCmd.RunMacro ("mRS")

    That would mean that mRS is the name of a macro, whereas the previous code means that you have a string variable mRS defined somewhere with the name of a macro.


    -Tom. Microsoft Access MVP

    Wednesday, April 17, 2019 6:26 PM
  • That was it but now I have another issue. 

    Grrrrrr

    It's not taking those entries for dates and initials into account when running the queries even though the query specifically says:

    ReceivedDate: [Forms]![frmRSWorkFile]![DateReceived]

    EnteredDate: [Forms]![frmRSWorkFile]![DateEntered]

    User: [Forms]![frmRSWorkFile]![Initials]

    Thee have all worked during testing up until now.

    Thanks,

    Jessica :(

    Wednesday, April 17, 2019 7:14 PM
  • My mistake. I forgot I was working in a copy of the original. I had to copy the code over to the original form. Duh!!

    Still working through a couple issues though. I'll get some notes and updates in a little bit.

    Thank you so much so far. 

    Jessica

    Wednesday, April 17, 2019 7:21 PM
  • Ok so I'm not sure what happened. After I fixed it and tried to run it, it was asking for information that should have already been there. For example Model# from the RSMassagedA table. When I opened the table it said Expr1: Model#. I don't know how they got like that so I recopied the SQL code back into the query and reran it and it corrected itself. So now it is working. Phew!! 

    I just have one more little piece that I need to reign in. The only thing not working is in BOLD. Here is the full code:

    Option Compare Database

    Private Sub Initials_AfterUpdate()
        Me![Initials] = UCase(Me![Initials])
        If Len(Initials) >= 6 Then
        MsgBox "No more than 5 characters allowed."
        Me![Initials].SetFocus
        End If
    End Sub

    Private Sub Run_mRS_Click()
     If (IsNull(Me![Initials])) Then
        MsgBox "You must enter your initials to continue."
        Me![Initials].SetFocus
      ElseIf (IsNull(Me![DateReceived])) Then
        MsgBox "You must enter the date the serials were received to continue."
        Me![DateReceived].SetFocus
      ElseIf (IsNull(Me![DateEntered])) Then
        MsgBox "You must enter the date you are importing the serials to continue."
        Me![DateEntered].SetFocus
      Else
        DoCmd.RunMacro ("mRS")
      End If

    End Sub

    I'm wondering If I could nest it into the rest of the code and it will work. I'm gonna try.

    Thanks,

    Jessica

    Wednesday, April 17, 2019 7:37 PM
  • That worked!!!

    Here is the final code...

    Private Sub Run_mRS_Click()
        Me![Initials] = UCase(Me![Initials])
      If (Len(Initials) >= 6) Then
        MsgBox "No more than 5 characters allowed."
        Me![Initials].SetFocus
      ElseIf (IsNull(Me![Initials])) Then
        MsgBox "You must enter your initials to continue."
        Me![Initials].SetFocus
      ElseIf (IsNull(Me![DateReceived])) Then
        MsgBox "You must enter the date the serials were received to continue."
        Me![DateReceived].SetFocus
      ElseIf (IsNull(Me![DateEntered])) Then
        MsgBox "You must enter the date you are importing the serials to continue."
        Me![DateEntered].SetFocus
      Else
        DoCmd.RunMacro ("mRS")
      End If

    End Sub

    Thank you so much for everyone's help!!!

    I couldn't have done it without yall!!

    Thank you again,

    Jessica

    Wednesday, April 17, 2019 8:02 PM
  • Option Compare Database
    Option Explicit

    Private Sub Initials_AfterUpdate()
        Me![Initials] = UCase(Me![Initials])
        If Len(Initials) >= 6 Then
        MsgBox "No more than 5 characters allowed."
        Me![Initials].SetFocus
        End If
    End Sub

    Hi Jessica,

    Please, add the line Option Explicit in every module that you use!!!

    Instead of the AfterUpdate event, you can better use the BeforeUpdate event. You can set the Cancel argument when the right condition is not yet met, and you will stay in the control:

    Private Sub Initials_BeforeUpdate(cancel As Integer)
    
         If (Len(Me!Initials) > 5) Then
           MsgBox "No more than 5 characters allowed."
           cancel = True
           Exit Sub
         End If
    
         Me!Initials = UCase(Me!Initials)
    
     End Sub

    Imb.

    Wednesday, April 17, 2019 8:04 PM
  • What does Option Explicit mean?

    It's no where in my code. It does have Option Compare Database at the very top. I added it just under like you have above and everything is still working.

    I'm still learning all the tricks to this. 

    Thanks,

    Jessica


    • Edited by Jessicasdd Wednesday, April 17, 2019 8:08 PM More detail
    Wednesday, April 17, 2019 8:07 PM
  • What does Option Explicit mean?

    Hi Jessica,

    You can read it in the Help.

    With Option Explicit the compiler demands that all variables are explicit declared.

    If not, any mis-typed variable will be used as a new variable of type Variant, with all kind of unexpected results.

    Imb.


    • Edited by Imb-hb Wednesday, April 17, 2019 8:22 PM
    Wednesday, April 17, 2019 8:20 PM
  • Oh then yes I want that in the code. 

    I've seen it in other databases but wasn't sure of it's meaning or why it was used. And because it wasn't in front of my face, of course, I never thought to look it up.

    Thank you again for all your help today. I really couldn't have finished this project without it.

    But this is why I LOVE this forum.

    Thanks,

    Jessica

    Wednesday, April 17, 2019 8:24 PM
  • > Here is the full code:
    > Option Compare Database

    That is a HUGE mistake that is often made, so don't feel bad. It should be:

    Option Compare Database
    Option Explicit

    The extra option ensures you cannot use undimensioned variables, which always leads to bad results. If I was Bill Gates, it would have been there by default.

    Please go back and add it for EVERY code module.
    Then using VBA Editor > Tools > Options check the box "Require variable declaration" so it will be inserted automatically for new modules.

    If you think about it: if this had been in place from the start, then the line:
    DoCmd.RunMacro (mRS)
    would have failed to compile, because it is considered an undeclared variable, and you would have realized that, and rewrote it as "mRS".


    -Tom. Microsoft Access MVP


    Wednesday, April 17, 2019 11:20 PM
  • Thank you for the further explanation, Tom.

    I did find that it was in my other code. I just didn't realize it. 

    I also did what you said and checked the box in VBA Options. 

    We have over 50 databases. I did not create any of them. I just add to and maintain them. Sometimes it's really hard to figure out what the last person did. So when I complete a project I have been documenting it completely. A list of all objects used and a copy of all the code that was used. That way if something happens, like yesterday when the query went to Expr1 instead of the actual table names, I can just copy the code back into the query to fix it.

    Also in my code I add notes so that if someone else has to look at it they understand what I've done. I so wish the last person did that. She tried to with some that she worked on but there were a lot that were already there before she started that were never documented. So it's like learning from the examples that we have on hand.

    And of course this forum because everyone here is GREAT!! And knows their stuff! 

    So thank you again, I truly appreciate all of your help.

    Jessica :)

    Thursday, April 18, 2019 6:02 PM