Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

IF then statement with a message box

Posted on 2007-03-21
13
Medium Priority
?
359 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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 150 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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

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

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
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.

721 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