Solved

IF then statement with a message box

Posted on 2007-03-21
13
344 Views
Last Modified: 2013-11-27
I am trying to write some code that says - if a text box (which sums the number of yes/no boxes) is greater than 3, bring up a message box.

Problem:  it's not warning me if I go over 3

If (Me.Text49 > 3) Then
    MsgBox "SELECT CLIENT FIRST"
    Me.HCFASubmit.SetFocus
    Exit Sub
    Cancell = True
End If
0
Comment
Question by:dewster1971
  • 5
  • 4
  • 3
  • +1
13 Comments
 
LVL 26

Expert Comment

by:jerryb30
ID: 18766523
Add msgbox  Me.Text49  before IF statement, and see what values shows up.
0
 

Author Comment

by:dewster1971
ID: 18766589
Jerry -
I don't understand what you're saying here.  Can you rewrite the code so I can understand?
0
 
LVL 26

Expert Comment

by:jerryb30
ID: 18766702
msgbox  Me.Text49  <add this line, wherever your code is at.
Want to make sure you are getting value you think you should.
If (Me.Text49 > 3) Then
    MsgBox "SELECT CLIENT FIRST"
    Me.HCFASubmit.SetFocus
    Exit Sub
    Cancell = True
End If

If you are summing the values of the yes/no boxes, you might be getting a negative number.
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 13

Expert Comment

by:wiswalld
ID: 18773522
If Me.Text49 > 3 Then
    MsgBox "SELECT CLIENT FIRST"
    Me.HCFASubmit.SetFocus
    Exit Sub
    Cancell = True
End If

0
 
LVL 26

Expert Comment

by:jerryb30
ID: 18773567
Can you post the full sub?
adding:
msgbox  Me.Text49  
to the beginning of your sub will allow you to see what value is actually being evaluated in your
 if statement.
0
 
LVL 13

Expert Comment

by:adraughn
ID: 18774900
you had two ll's in cancel.
Msgbox me.Text49 ' Take this out after you get it working
If (Me.Text49 > 3) Then
    MsgBox "SELECT CLIENT FIRST"
    Me.HCFASubmit.SetFocus
    Exit Sub
    Cancel = True
End If
0
 

Author Comment

by:dewster1971
ID: 18800615
Sorry for not getting back sooner, but..

Here's what looks like is happening.  My complete code is:

Private Sub HCFASubmit_Click()


If Me.Text49 > 2 Then
    MsgBox "SELECT CLIENT FIRST"
    Me.HCFASubmit.SetFocus
    Exit Sub
    Cancel = True
End If
End Sub

When I clidk the checkbox, the information on the table is updated, and the calculation to sum the check boxes updates (=Sum([HCFASubmit])*-1).  Putting the code

Msgbox me.Text49

returns 1 less than the actual number of checked boxes.  Say two check boxes are checked, I click a 3rd, the Msgbox brings back 2, then I click "OK", the message box goes away and then the Text49 calculation sums to 3.  It's being "delayed" by 1.

Any other suggestions?
0
 
LVL 13

Expert Comment

by:adraughn
ID: 18800667
What is HCFASubmit? Is that a command button? So how I am pocturing this is that you have a form with multiple check boxes, a textbox that adds the value of the checkboxes and a command button that will send the message box. Is that right?

What is the code for the textbox that is calculating the check box totals?

-adria
0
 

Author Comment

by:dewster1971
ID: 18800869
adraughn -
HCFASubmit is a checkbox, the form is tabular and the Text49 text box sums the checkboxes on that form.

The code to sum the checkboxes is: =Sum([HCFASubmit])*-1

I'm not using a command button to initiate the event.  I'm using the "On Click" event for the checkbox to run the event.
0
 
LVL 13

Expert Comment

by:adraughn
ID: 18800886
what are the names of the other check boxes? how many checkboxes fo you have?
0
 
LVL 13

Expert Comment

by:adraughn
ID: 18800966
Private Sub HCFASubmit_Click()
Dim cnt as Integer
cnt = 0
If ckMyCheckBox = True Then cnt = cnt + 1
If ckMyCheckBox2 = True Then cnt = cnt + 1 'etc
   
MsgBox "SELECT CLIENT FIRST"
    Me.HCFASubmit.SetFocus
    Exit Sub
    Cancel = True
End If
End Sub
0
 
LVL 13

Accepted Solution

by:
adraughn earned 50 total points
ID: 18801000
Sorry, I was typing the code into this browser and it submited it for me to early. This is what I was trying to send you:

Private Sub HCFASubmit_Click()
Dim cnt As Integer

cnt = 0
    If Me.ckMyCheckBox = True Then cnt = cnt + 1
    If Me.ckMyCheckBox2 = True Then cnt = cnt + 1 'etc
   
    If cnt > 3 Then
        MsgBox "SELECT CLIENT FIRST"
        Me.HCFASubmit.SetFocus
    Exit Sub
    Cancel = True
End If
End Sub
0
 

Author Comment

by:dewster1971
ID: 18811273
There is only one checkbox and it's called HCFASubmit, but its on a tabular (continuous form) form so it will show up several times.

I finally just put an iif statement on a different command button that runs after a requery.  This seems to do the trick.

Thanks for your help.
0

Featured Post

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

803 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question