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

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

Answer 1

> 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

Answer 2

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. 

Answer 3

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

Answer 4

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

Answer 5

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

Answer 6

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

Answer 7

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.

Answer 8

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

Answer 9

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

Answer 10

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.

Answer 11

I'm so confused.

Answer 12

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

Answer 13

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 :(

Answer 14

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

Answer 15

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

Answer 16

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

Answer 17

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.

Answer 18

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

Answer 19

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.

Answer 20

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

Answer 21

> 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

Answer 22

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 :)