Asked by:
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 SubPrivate 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 SubThanks,
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 SubI 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 SubBoth 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 SubPrivate 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 SubI'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 ExplicitPrivate 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 SubHi 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 DatabaseThat is a HUGE mistake that is often made, so don't feel bad. It should be:
Option Compare Database
Option ExplicitThe 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
- Edited by Tom van Stiphout (MVP)MVP Wednesday, April 17, 2019 11:21 PM
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