If formula error 3

[janetlyn]

I have 12 textboxes in a form. Box 1-8 = 77050; Box 9=77050; Box 10=77050; Box 11=77050; Box 12=77050. This is my code for these textboxes:

ElseIf ((([A9] + [A10] + [A11] + [A12] + [A13] + [A15] + [A16] + [A24]) <> [A8]) Or ([A8] <> [E49]) Or ([A8] <> [E12]) Or ([A8] <> [A23])) Then
A9.Visible = True
A10.Visible = True
A11.Visible = True
A12.Visible = True
A13.Visible = True
A15.Visible = True
A16.Visible = True
A23.Visible = True
A24.Visible = True
A8.Visible = True
E12.Visible = True
E49.Visible = True
CumEmpHrs.Visible = True
Line207.Visible = True
BoxA9.Visible = True

All boxes were true, meaning the computer thinks the numbers do not equal. So I put this code in before the above code:

'ElseIf ([A9] + [A10] + [A11] + [A12] + [A13] + [A15] + [A16] + [A24]) = 77050 Then
' MsgBox &quot;It adds up&quot;
'ElseIf [A8] = 77050 Then
' MsgBox &quot;A8 adds up&quot;
'ElseIf [A23] = 77050 Then
' MsgBox &quot;A23 adds up&quot;
'ElseIf [E12] = 77050 Then
' MsgBox &quot;E12 adds up&quot;
ElseIf [E49] = 77050 Then
MsgBox &quot;E49 adds up&quot;

Then I tested each one. All came up with the MsgBox that it adds up. So why does the ElseIf formula not work; in other words, why does it all become visible instead of going onto the next ElseIf. The above code is in the middle of a bunch of other ElseIf statements which are coded the same way, but are working.

Appreciate any help. Janet Lyn

 

[rdodge]

Just looking at it from the Excel/Access stand point of view, the first thing that comes to mind is linked tables. Have you worked with linked tablesin Access and create a link to an Excel spreadsheet?

As far as the Msgbox box question is concerned, you could put it on a line:

before the If statement if you want it to pop up every time for the time being to debug

on the first line directly under the If statement, if you want it to pop up when the If condition is true

on the first line directly under the ElseIf statement, if you want it to pop up when the ElseIf condition is true

on the first line directly under the Else statement, if you want it to pop up when no conditions has been met

If the information is already setup via a Linked Table and the textboxes are bound to the linked table, then it should work about the same as Access tables except you can't modify the properties of the table, and the refreshing of the table may be done differently.

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000
When the going gets tough, the tough gets going.

 

[CajunCenturion]

After removing all of the redundant statements (redundant because of the nesting - see last comment for more), and providing some indentation, this complex If statement looks like the following:

If (CLng([A1]) = CLng([E50])) Then
A1.Visible = False
E50.Visible = False
Q3.Visible = False
AnnBill.Visible = False
Line176.Visible = False
BoxA1E50Q3.Visible = False
If (CLng([A1]) = CLng([Q3])) Then
If (CLng([Q5]) = CLng([A7])) Then
A7.Visible = False
Q5.Visible = False
Line183.Visible = False
AnnCatBill.Visible = False
BoxA7Q5.Visible = False
If ((CLng([E39]) = CLng([A17]))) Then
E51.Visible = False
E39.Visible = False
A17.Visible = False
AnnEmpHrs.Visible = False
Line186.Visible = False
BoxE51E39A17.Visible = False
If (CLng([E39]) = CLng([E51])) Then
If ((CLng([E9]) + CLng([E10])) = (CLng([E34]) + CLng([E35]))) Then
E9.Visible = False
E10.Visible = False
E34.Visible = False
E35.Visible = False
A4.Visible = False
A14.Visible = False
A18.Visible = False
CumRev.Visible = False
Line189.Visible = False
BoxE9E10E34E35A4A14A18.Visible = False
If ((CLng([E9]) + CLng([E10])) = CLng([A4])) Then
If (((CLng([E9]) + CLng([E10])) = CLng([A18]))) Then
If (((CLng([E26]) + CLng([E27]) + CLng([E28]) + CLng([E29]) + CLng([E30]) + CLng([E31]) + CLng([E32]) + CLng([E33])) = (CLng([E2]) + CLng([E3]) + CLng([E4]) + CLng([E5]) + CLng([E6]) + CLng([E7]) + CLng([E8]))) Or ((CLng([E26]) + CLng([E27]) + CLng([E28]) + CLng([E29]) + CLng([E30]) + CLng([E31]) + CLng([E32]) + CLng([E33])) = (CLng([E53]) + CLng([E54]) + CLng([E55]) + CLng([E56]) + CLng([E57]) + CLng([E59]) + CLng([E36])))) Then
E26.Visible = False
E27.Visible = False
E28.Visible = False
E29.Visible = False
E30.Visible = False
E31.Visible = False
E32.Visible = False
E33.Visible = False
E2.Visible = False
E3.Visible = False
E4.Visible = False
E5.Visible = False
E6.Visible = False
E7.Visible = False
E8.Visible = False
E53.Visible = False
E54.Visible = False
E55.Visible = False
E56.Visible = False
E57.Visible = False
E59.Visible = False
E36.Visible = False
Line217.Visible = False
MoIndEmpHrs.Visible = False
BoxE26.Visible = False
CumEmpHrs.Visible = False
If ((CLng([Q2]) = CLng([A3])) Or (CLng([Q2]) = CLng([A25]))) Then
Q2.Visible = False
A3.Visible = False
A25.Visible = False
CumBillSubDisc.Visible = False
Line204.Visible = False
BoxQ2A3A25.Visible = False
If ((CLng([A2]) = CLng([E11])) Or (CLng([A2]) = CLng([E48])) Or (CLng([A2]) = CLng([Q6])) Or (CLng([A2]) = (CLng([Q4]) - 2055))) Then
A2.Visible = False
E11.Visible = False
E48.Visible = False
Q6.Visible = False
Q4.Visible = False
CumBill.Visible = False
Line209.Visible = False
BoxA2E11E48Q6Q4.Visible = False
If (((CLng([A8]) = CLng([E49])) Or ((CLng([A8]) = CLng([E12]))) Or (CLng([A8]) = CLng([A23])))) Then
A9.Visible = False
A10.Visible = False
A11.Visible = False
A12.Visible = False
A13.Visible = False
A15.Visible = False
A16.Visible = False
A23.Visible = False
A24.Visible = False
A8.Visible = False
E12.Visible = False
E49.Visible = False
CumEmpHrs.Visible = False
Line207.Visible = False
BoxA9.Visible = False
If (((CLng([A9]) + CLng([A10]) + CLng([A11]) + CLng([A12]) + CLng([A13]) + CLng([A15]) + CLng([A16]) + CLng([A24])) = CLng([A8])) Or ((CLng([A8]) = CLng([E49])) Or ((CLng([A8]) = CLng([E12]))) Or (CLng([A8]) = CLng([A23])))) Then
If ((CLng([E14]) + CLng([E37])) = CLng([A19])) Then
E14.Visible = False
E37.Visible = False
A19.Visible = False
CumExp.Visible = False
Line211.Visible = False
BoxE14E37A19.Visible = False
If (CLng([A5]) = CLng([A20])) Then
A5.Visible = False
A20.Visible = False
CumExpNoMark.Visible = False
Line219.Visible = False
BoxA5A20.Visible = False
If ((CLng([E23]) - (CLng([E13]) + CLng([E24]))) < 50) Or ((CLng([E23]) - (CLng([E13]) + CLng([E24]))) > 50) Then
E23.Visible = False
E15.Visible = False
E16.Visible = False
E17.Visible = False
E18.Visible = False
E19.Visible = False
E20.Visible = False
E21.Visible = False
E22.Visible = False
E13.Visible = False
E24.Visible = False
CumInd.Visible = False
Line213.Visible = False
BoxE13.Visible = False
If ((CLng([E23]) = (CLng([E15]) + CLng([E16]) + CLng([E17]) + CLng([E18]) + CLng([E19]) + CLng([E20]) + CLng([E21]) + CLng([E22]) + CLng([E13])))) Then
If ((CLng([E38]) + CLng([E58])) = &quot;-1,518,636.51&quot Then
MsgBox &quot;Yes&quot;
If (CLng([A21]) = (CLng([E38]) + CLng([E58]))) Then
A21.Visible = False
E38.Visible = False
E58.Visible = False
CumNetPL.Visible = False
Line221.Visible = False
BoxA21E38E58.Visible = False
If ((CLng([Q7]) = CLng([Q1])) Or (CLng([Q7]) = CLng([A22]))) Then
Q7.Visible = False
Q1.Visible = False
A22.Visible = False
MoBill.Visible = False
Line223.Visible = False
BoxQ7Q1A22.Visible = False
If ((CLng([E25]) = CLng([E1])) Or (CLng([E25]) = CLng([A6])) Or (CLng([E25]) = CLng([E52]))) Then
E25.Visible = False
E1.Visible = False
A6.Visible = False
E25.Visible = False
MoEmpHrs.Visible = False
Line215.Visible = False
BoxE25E1E25A6E52.Visible = False
If ((CLng([A26]) + CLng([A27]) + CLng([A28]) + CLng([A29]) + CLng([A30]) + CLng([A31]) + CLng([A32]) + CLng([A33])) = (CLng([E40]) + CLng([E41]) + CLng([E42]) + CLng([E43]) + CLng([E44]) + CLng([E45]) + CLng([E46]) + CLng([E47]))) Then
A26.Visible = False
A27.Visible = False
A28.Visible = False
A29.Visible = False
A30.Visible = False
A31.Visible = False
A32.Visible = False
A33.Visible = False
E40.Visible = False
E41.Visible = False
E42.Visible = False
E43.Visible = False
E44.Visible = False
E45.Visible = False
E46.Visible = False
E47.Visible = False
AnnIndEmpHrs.Visible = False
Line193.Visible = False
BoxA26.Visible = False
Else
Box226.Visible = False
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If

You must understand that all of your If statements are nested, meaning that each If statement will only be executed if the preceeding If statement is true. As soon as any one of them are false, you'll drop out and no other controls will be set to not visible, with the exception of the most embedded If statement which if it fails, will set Box226 to not visible. For example, if A1 <> E50, then nothing will be set not visible because you never enter the then clause, which means you never execute any of the other if statements.

Is that what you want to do? Or do you want to check each If statement every time? If you do want to check each one, then you cannot nest the IF statements, each must stand on its own. In this case, you need to do the following, where I've just shown the first three, but you'll have to do the same with all of them. Also, the redundant statement will no longer be redundant, as each set are not required, since each if statement will be check independantly.

If (CLng([A1]) = CLng([E50])) Then
  A1.Visible = False
  E50.Visible = False
  Q3.Visible = False
  AnnBill.Visible = False
  Line176.Visible = False
  BoxA1E50Q3.Visible = False
End If

If (CLng([A1]) = CLng([Q3])) Then
  A1.Visible = False
  E50.Visible = False
  Q3.Visible = False
  AnnBill.Visible = False
  Line176.Visible = False
  BoxA1E50Q3.Visible = False
End If

If (CLng([Q5]) = CLng([A7])) Then
   A7.Visible = False
   Q5.Visible = False
   Line183.Visible = False
   AnnCatBill.Visible = False
   BoxA7Q5.Visible = False
End If

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[janetlyn]

Ronald, I went and played with the message and found where the code is stopping. I was rereading your other message and when I read the part of being False so going to the Then instead of reading the rest of the lines, it was like a light bulb came on.

Cajun, Until I was playing around with Ronald's suggestion, it never dawned on me that it was not reading all my code. I do not believe it will make any difference if the IF's are nested or not. They will have to stay on that form until the THEN happens. On the other hand, I have four groups that are on my screen right now and all the numbers match. I have probably spent three hours wondering why they were not invisible since they matched. Now I know why. So, maybe it is better to NOT nest so the user can only see those that are wrong. Great idea. I think that was what Ronald was telling me also.

I will carry on. Y'all are great. Janet Lyn

 

[luceze]

A little off topic Janet, but looking at your On Close Event you could make it much shorter. Something like this will make all objects visible in a form.

On Error GoTo ErrorX
Dim ctrl As Control
For Each ctrl In Me.Controls
ctrl.Visible = False
Next
Exit Sub
ErrorX:
MsgBox Err.Number & vbCrLf & _
Err.Description
Resume Next

Eric

 

[janetlyn]

Eric, not off the topic at all. I appreciate anything that will make my code easier. I put your code in and it worked...like a charm. Question about your code? Which lines of your code are to prevent errors (like hitting the cancel button throws you into the code window with an error)? Is it lines 1, 7, 8, 9. Can I use this code anywhere I have code that will make a control (label) do something? Thanks for your great help, JL

 

[luceze]

When an error occurs the code jumps to the line &quot;ErrorX:&quot;. It then continues through to the end. The &quot;Resume Next&quot; line then sends it back to the For statement.
Someone else can explain error handling and its benefits a lot better than me, but I'll try. First, it will prevent users from getting the Debug option when an error occurs, they get a message that you choose instead. Second, you can &quot;trap&quot; the error numbers and do alternative tasks when a certain error occurs.

Yes you can use it wherever you want to set ALL controls to visible or not visible. You can't call it from a button because you can't set a control's visible property to false if it has the focus.

HTH,
Eric

 

[CajunCenturion]

Another thing that you can do to simplify the code is to write a small subroutine to take a list controls and make them invisible. Copying the first three If statements from my previous post, that code can be re-written as follows (borrowing from MichaelRed's suggestion in another thread):

If (CLng([A1]) = CLng([E50])) Then
   MakeInvisible A1, E50, Q3, AnnBill, Line176, BoxA1E50Q3
End If

If (CLng([A1]) = CLng([Q3])) Then
   MakeInvisible A1, E50, Q3, AnnBill, Line176, BoxA1E50Q3
End If

If (CLng([Q5]) = CLng([A7])) Then
   MakeInvisible A7, Q5, Line183, AnnCatBill, BoxA7Q5
End If

with the MakeInvisible subroutine written as follows:

Private Sub MakeInvisible(ParamArray ControlList())

   Dim lCtl_LocControl     As Control
   Dim lInt_Idx            As Integer
   
   For lInt_Idx = LBound(ControlList) To UBound(ControlList)
      Set lCtl_LocControl = ControlList(lInt_Idx)
      lCtl_LocControl.Visible = False
   Next lInt_Idx
   
   Set lCtl_LocControl = Nothing
   
End Sub

And of course, from each conditional block, simply list the controls to be affected as the parameter list to the MakeInvisible routine.

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[janetlyn]

So if I have a message box (that opens and someone puts a value in there) with a cancel button on it, but that cancel button will go to a debug would the code be exactly as you wrote it or are some of those unique to my db?

 

[janetlyn]

Cajun, I'm not sure I understand &quot;from each conditional block, simply list the controls to be affected as the parameter list to the MakeInvisible routine&quot;. If I make all my If lines of code look like yours, and copy and paste the subroutine, it should work? I actually copied and pasted Michael's code because it was so much &quot;neater&quot; and easier to read, but I had so many problems with it I finally gave up and went back to what I knew was going to work and figured I would try his more complicated method later. What you showed seems pretty easy. Did I make the correct assumption about the code? Thanks for the help, JL

 

[rdodge]

Cajun brings up a very good point. Wherever you can, it's also best to modulate your code, which then you use parameters (arguments) to pass the necessary data/references to the modulated code. By doing this, it helps in many ways:

Only having code in one centralized place

Easy to Debug

Only have to change in one place rather than multiple places, should there be a need to change

Reduced file size

These are just a few of the advantages of having modulated code.

There may be a couple of drawbacks like when dealing with module level variables, you wouldn't necessarily want to run into conflicts, which then as you plan out your DB program, you need to determine the scope level (Project, module, or method level) of each variable that you use in the program, but generally, there would be very limited number of these types of situations as compared to how much you can modulate your code.

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000
When the going gets tough, the tough gets going.

 

[CajunCenturion]

If you look at the if statement from the original code, you are setting 5 controls to not visible

If (CLng([Q5]) = CLng([A7])) Then
   A7.Visible = False
   Q5.Visible = False
   Line183.Visible = False
   AnnCatBill.Visible = False
   BoxA7Q5.Visible = False
End If

Using the subroutine approach, those same five controls are listed as the arguement list to the subroutine

If (CLng([Q5]) = CLng([A7])) Then
   MakeInvisible A7, Q5, Line183, AnnCatBill, BoxA7Q5
End If

Each if statement would naturally, have its own different list of controls that would be passed to the subroutine.

The code that I posted does work, although for my tests, the names of the controls were different, but did achieve the desired effect.

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[janetlyn]

Great. I will get on it. I would give you another star but it won't let me. Have a most wonderful day. Janet Lyn