If Statement calling Msg Box

[Gilrucht]

I have a series of IF statements that check my fields and if null call a message telling the user a value must be filled in before the record cab be saved. The code works fine. Here is the code for two fields to show the syntax:

If Len(Me.FName.Value & "") = 0 Then
MsgBox "You must enter a value into FName."
Me.FName.SetFocus

ElseIf Len(Me.LName.Value & "") = 0 Then
MsgBox "You must enter a value into LName."
Me.LName.SetFocus

This same code repeats for each field.

Here is my problem. I have Home, Work and Cell phone fields. The client may not have these numbers. I want to add code that add code for these fields that calls a message box with 2 command buttons(yes & no) with the Message" Does the client have a Home Phone?"

The user would choose the yes or no command button. If the user chooses yes then the same code as above would be called. if he chooses no the ElseIf statement would be called. Can this be done? I don;t know how to wriye the code for the command buttons. can anyone help?

 

[WayneRyan]

G,

The "If - ElseIf" construct won't accomplish what you need here.

Even this does not guarantee that they will actually enter something
in the appropriate gields.

If Me.Home = "" And Me.Work = "" And Me.Work = "" Then
   If MsgBox("Do you have a Home Phone?", vbYesNo) = vbYes Then
      Me.Home.SetFocus
      Exit Sub
   End If
   If MsgBox("Do you have a Work Phone?", vbYesNo) = vbYes Then
      Me.Work.SetFocus
      Exit Sub
   End If
   If MsgBox("Do you have a Cell Phone?", vbYesNo) = vbYes Then
      Me.Cell.SetFocus
      Exit Sub
   End If
End If

Wayne

 

[ssteinke]

ElseIf Len(Me.HPhone.Value & "") = 0 Then
If MsgBox("Does the client have a Home Phone?", vbYesNo + vbExclamation, "Validation") = vbYes Then
MsgBox "You must enter a value into HPhone."
Me.HPhone.SetFocus
End If
elseif
'keep going

 

[Gilrucht]

Wayne,
I'm not sure why you say the IF-ElseIF won't work here. Its been working on all the othe fields with my test data. I get the message for any fields I leave empty and can't update the record until I fill in all the fields. I tested it with SSteinkes code and it worked perfectly as well. When I chose yes it would not let me save the record until I filled in the phone number. When I choose no it branches back to my original code, completes the loop and then saves the record as it is supposed to. You code worked too but the reason I like his is because it follows the syntax of the rest of my original code. Is there some way I am not aware of the user can save the record without filling in the fields if I use the IF-ElseIF statement?

 

[Gilrucht]

SST,
Thanks. That was exactly what I was looking for. Its working perfectly

 

[ghudson]

This should work in the forms BeforeUpdate event...

If Home = "" Or IsNull(Home) Then
   If MsgBox("Do you have a Home Phone?", vbQuestion + vbYesNo) = vbYes Then
      Me.Home.SetFocus
      MsgBox "You must enter a value into the Home field."
      DoCmd.CancelEvent
      Exit Sub
   End If
End If
   
If Work = "" Or IsNull(Work) Then
    If MsgBox("Do you have a Work Phone?", vbQuestion + vbYesNo) = vbYes Then
      Me.Work.SetFocus
      MsgBox "You must enter a value into the Work field."
      DoCmd.CancelEvent
      Exit Sub
    End If
End If
   
If Cell = "" Or IsNull(Cell) Then
   If MsgBox("Do you have a Cell Phone?", vbQuestion + vbYesNo) = vbYes Then
      Me.Cell.SetFocus
      MsgBox "You must enter a value into the Cell field."
      DoCmd.CancelEvent
      Exit Sub
   End If
End If

 

[WayneRyan]

Gilrucht,

I think that with the If - Elseif method, you could have the following scenario:

A - Filled (Desired)
B - Blank (Desired)
C - Blank (Inadvertant)

On each pass, it will only ask for the first Blank (in this case B). I don't ever
see it asking for C.

Ghudson's method is what works.

I am fully convinced that my code would have looked like that if I hadn't stopped
by the bar to shoot pool after work.

Wayne

 

[photoguy53]

In searching for an answer to my just posted thread titled: "Urgent Required Field Advice Needed ", I was reading this thread. In reading the solution here, I *think* it contains the solution to my problem.

Would the authors of this thread and replies take a look at my question and guide me in using the code written to solve this topic.

It looks like by modifiying this solution, I might have a solution to my problem.

Thanks
Photoguy53

 

[Gilrucht]

GHudson,
Your code worked until I added my required fiekds. Then I got an error message when compiling the code-"BlockIF without Endif. Here is the full code:

Private Sub Form_BeforeUpdate(Cancel As Integer)

If FName = "" Or IsNull(FName) Then
MsgBox "You must enter a value into FName."
Me.FName.SetFocus
MsgBox "You must enter a value into the First Name field."
DoCmd.CancelEvent
Exit Sub
End If


If LName = "" Or IsNull(LName) Then
MsgBox "You must enter a value into the Last Name field."
Me.LName.SetFocus
DoCmd.CancelEvent
Exit Sub
End If

If SocialSecNo = "" Or IsNull(SocialSecNo) Then
MsgBox "You must enter a value into Social Security"
Me.SocialSecNo.SetFocus
DoCmd.CancelEvent
Exit Sub
End If

If Address = "" Or IsNull(Address) Then
MsgBox "You must enter a value into Address"
Me.Address.SetFocus
DoCmd.CancelEvent
Exit Sub
End If


If HPhone = "" Or IsNull(HPhone) Then
If MsgBox("Do you have a Home Phone?", vbQuestion + vbYesNo) = vbYes Then
Me.HPhone.SetFocus
MsgBox "You must enter a value into the Home Phone field."
DoCmd.CancelEvent
Exit Sub
End If

If WPhone = "" Or IsNull(WPhone) Then
If MsgBox("Do you have a Work Phone?", vbQuestion + vbYesNo) = vbYes Then
Me.WPhone.SetFocus
MsgBox "You must enter a value into the Work Phone field."
DoCmd.CancelEvent
Exit Sub
End If

If CellPhone = "" Or IsNull(CellPhone) Then
If MsgBox("Do you have a Cell Phone?", vbQuestion + vbYesNo) = vbYes Then
Me.CellPhone.SetFocus
MsgBox "You must enter a value into the CellPhone field."
DoCmd.CancelEvent
Exit Sub
End If
End If
End Sub

Can you se where my error is? Thanks.

 

[ansentry]

Gilrucht,

I would remove the End If that I have in bold.

If CellPhone = "" Or IsNull(CellPhone) Then
If MsgBox("Do you have a Cell Phone?", vbQuestion + vbYesNo) = vbYes Then
Me.CellPhone.SetFocus
MsgBox "You must enter a value into the CellPhone field."
DoCmd.CancelEvent
Exit Sub
End If
End If
End Sub

To save yourself writing so much code, why not use the tag property to check all these controls before the form is updated.

If you use the following example you would have to enter required in the tag property of each of your control that you want "checked".

Private Sub Form_BeforeUpdate(Cancel As Integer)

 Dim ctl As Control
 
    For Each ctl In Me
    
     
        If ctl.Tag = "Required" Then
            If IsNull(ctl) Or ctl = "" Then
                MsgBox "You must complete the required field '" & " " & ctl.Name & " " & "' before you can continue.", vbCritical, "Required Field"
                ctl.SetFocus
                Cancel = True 
                Exit Sub
            End If
        End If
    Next
    Set ctl = Nothing
End Sub

 

[Gilrucht]

John, I originally had just had code for the required fields. That worked fine. Yours will too and I like it muck better. The problem occurs when I try to add the msgbox for the 3 phone fields that aren't required. I'm trying to remind users to fill them in if clients have any of those 3 numbers. I like your ideas of tags but as I read your code for the tags it will only work for the required fields. Is there a way to modify to ask " If there is a phone number? If yes please enter." That was my original question in this post.

 

[ansentry]

Gilrucht,

Is there a way to modify to ask " If there is a phone number? If yes please enter." That was my original question in this post.

Good question, I will have a play with that one. Just of the top of my head what about you have 2 types of Tags e.g. MustHave and IfPossible and then have the code check the MustHave first and then the IfPossible.

I am of to bed now 1:41am

So I will have a look when I have some time and get back to you. If you or someone else solves it in the mean time I will view the post with interest.

 

[mollycase]

i realize this thread a few years old but...

i am so excited that code i modified actually worked! the first time!

i was trying to prevent blank record creation and this worked, with a little mod that someone else might useful one day. i'm sure this is old news (or not the "correct" way to you access masters) but it works for me

first: i have a tblBook that contains all the FK for all the tables with book info. i have a tblBookTitles that has, of course, book titles. i have a frmBookInfo that allows for all info to be entered, thus creating a book record with a unique ID (and allowing for books with the same title - you'd be surprised how much that happens, stuff like "Selected Poems" is a popular one.)
on the frmBookInfo i have a cbo for BookTitle. it has a notinlist event that will add it without a popup. however, this was creating a new record in frmBookInfo - creating a unique book ID when there was no other info (author etc.)

so i modified the code in this thread to make the cboAuthor required before saving the record. if you try to go to a new record without an author, it asks if you forgot. yes takes you back to cboAuthor. no provides a message box that says "if no author, select No Author Listed from the list." and then also takes you back to the cboAuthor.

here it is!
______________________________________________________________--
Private Sub Form_BeforeUpdate(Cancel As Integer)
If cboAuthor = "" Or IsNull(cboAuthor) Then
If MsgBox("Oops! Did you forget to add an author for this book!", _ vbQuestion + vbYesNo) = vbYes Then
Me.cboAuthor.SetFocus
DoCmd.CancelEvent
Else
MsgBox "If no author, please selected No Author Listed from the list."
Me.cboAuthor.SetFocus
DoCmd.CancelEvent
Exit Sub
End If
End If

End Sub

 

[boblarson]

Just a little info for you -

The typical way to cancel the Before Update event is to use

Cancel = True

instead of

DoCmd.CancelEvent

 

[mollycase]

cool, thanks!

 

[Thinh]

Simple solution

dim status as integer
if len(me.txtcontrol1 & "") = 0 then status = status +1
if len(me.txtcontrol2 & "") = 0 then status = status +2
if len(me.txtcontrol3 & "") = 0 then status = status +4

select case status
case 1
msgbox "Required control1"
case 2
msgbox "Required control2"
case 3
msgbox "Required control1 and control2"
case 4
msgbox "Required control3"
case 5
msgbox "Required control1 and control3"
case 6
msgbox "Required control2 and control3"
case 7
msgbox "Required control1 and control2 and control3"
end select

 

[Ellen07886]

Hi Thinh

I'm relatively inexperienced with access but I'm developing a database at the moment where I need to have required fields in forms, you're code above works very well with the exception that if I enter data in one of the required fields but not another the message box works great but it deletes the data from the 1st required field and doesn't prompt me to add data to the 3rd required field I've added my code below, any assistance you can provide would be appreciated.

Code has been entered in the before update of the form

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim status As Integer
If Len(Me.Inspecting_Officer & "") = 0 Then status = status + 1
If Len(Me.Activity_Date & "") = 0 Then status = status + 2
If Len(Me.Time_Taken & "") = 0 Then status = status + 4
Select Case status
Case 1
MsgBox "Enter your Name"
Case 2
MsgBox "Enter the date of activity"
Case 3
MsgBox "Complete Name and Activity Date Fields"
Case 4
MsgBox "Complete the Time Taken Field"
Case 5
MsgBox "Required Name and Time Taken"
Case 6
MsgBox "Required Activity Date and Time taken"
Case 7
MsgBox "Name1 and Activity Date and Time taken"
End Select
End Sub

Thanks in advance

Ellen