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]

Not sure if your textboxes are bound or not, but if they are not bound, by default, the values of the textboxes are String Data Type.

Think of the following situation for string data types:

FullString = &quot;String1&quot; + &quot;String2&quot;

FullString then has the value of &quot;String1String2&quot;

I suspect you have the same type issue going on above, so to get around that issue, use one of the conversion functions to convert the Data Type from String to a Numeric one such as CLng to convert it to a Long Data Type.

ElseIf CLng([A9]) + CLng([A10]) = 77050 Then

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

 

[janetlyn]

Thank you so much. I have three other threads talking about this issue with some others, and although they gave me some wonderful advice as to how I could improve my database (and I am not being sarcastic), they never really gave me an answer to my problem (that worked anyway). I really am not dissing anyone, just glad to get a straight forward answer I actually understand. I will go change the formula and let you know what happens. Thanks again. JL

 

[janetlyn]

Ronald, thought I had this fixed. Guess not. It still is making all visible even though the amounts all equal. Can you see anything wrong? Thanks for the help, JL


ElseIf (((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
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

 

[rdodge]

At this point of time, probably the easiest way to find the issue is to use the debugging tools in VBE. Do you know how to use the break points along with the Watch Window?

Put a break point on the ElseIf line and check the various values, each part of the expressions and anything else that may need to be checked.

Between the break points, stepping through, and the watch window, those are the main debugging tools with VBE that I use for finding issues, went looking at the code doesn't seem to be apparent.

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

 

[janetlyn]

This is some debugging code someone gave me for another problem. I am not very good at debugging. I debugged this equation because it was small. The screen shows both values are the same, but they are doing the visible code. Here is what I typed. It is not giving me the message box, nor are there any values in the CTRL-G box.

Debug.Print &quot;[A5]=&quot; & [A5]
Debug.Print &quot;[A20]=&quot; & [A20]
Debug.Print &quot;Is This Statement False ([A5]=[A20]): &quot; & [A5] = [A20]
MsgBox &quot;Debug - Evaluate Expression (Equals): &quot; & [A5] = [A20]
If (CLng([A5]) = CLng([A20])) Then
A5.Visible = False
A20.Visible = False
CumExpNoMark.Visible = False
Line219.Visible = False
BoxA5A20.Visible = False

Can you tell me what is wrong? Thanks, JL

 

[rdodge]

Here's the steps that I take for depbugging this sort of thing as you don't have to do it via code, you can also do it visually.

Bring up the Watch Window from the View Menu, if it's not already up.

On the line that you have:

If (CLng([A5]) = CLng([A20])) Then

Just to the left of it on the grey bar, left click to have that line of code highlighted (in redish-brown color) and a filled in circle (some may call it a dot) on the grey bar next to the line of code.

Highlight the &quot;CLng([A5])&quot; code, but only it doesn't have the quotes.

Right Click it

Select Add Watch...

Click on &quot;OK&quot; on the dialog box. (this adds the variable to the watch window)

Do the same with &quot;CLng([A20])&quot;, but only it doesn't have the quotes.

After you added both of the variables individually, now you can add the whole expression to the watch window the same way.

Run the code like you normally will. However, this will cause the code not execute the IF line and take you into VBE with the If line highlighted in Yellow. At this point, you can check the values in your Watch Window. Often times, this is the method I have found to be most useful in debugging, but there are situations where you won't really be able to use this method too easily. However, this is a good starting point for learning how to debug. Here's a few additional links for learning how to use the debugging tools in VBA:

The first 2 gives basic information while the last one is a technical paper on the technicalities of how to debug and other things you can do with debugging in VBA.

BASIC INFO

http://www.cpearson.com/excel/Debug.htm

http://www.j-walk.com/ss/excel/tips/tip11.htm

ADVANCED INFO

http://www.fmsinc.com/tpapers/vbacode/Debug.asp

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

 

[janetlyn]

Ronald, I did what you said and now there is a window at the bottom of my code screen with three watches in it. However, when I rerun the code, it stops at the formula, but stays on the form and does not go to the code. Also, there are no values, it says <Out of context>. The type says &quot;Empty&quot;. Any clues what I did wrong? Thanks, JL

 

[janetlyn]

Bob, it worked. I had the form open when I did the code. I needed to close the form to refresh it and run through all the code. I had other debug code in there I needed to make REM. Then it worked fine.

Also, I made all my boxes visible, and all my formulas to be = instead of <>. It seems to be working better. Someone else suggested this in another thread and I wish I could remember who now so I could go tell them they were right. I think it was MichaelRed. Anyhow, thanks so much. Especially for those excellent guides to debugging. You are fantastic. Have a wonderful day. Janet Lyn

 

[CajunCenturion]

I would suggest that you break this down into some smaller parts get a better understanding of where is failing, for example:
instead of:

ElseIf ((([A9] + [A10] + [A11] + [A12] + [A13] + [A15] + [A16] + [A24]) <> [A8]) Or ([A8] <> [E49]) Or ([A8] <> [E12]) Or ([A8] <> [A23])) Then

You might try

Dim SubTotal1 as Long
SubTotal1 = Val([A9]) + Val([A10]) + Val([A11]) + Val([A12]) + Val([A13]) + Val([A15]) + Val([A16]) + Val([A24])
If SubTotal1 <> Val([A8]) Then
   MsgBox &quot;Sub1 <> A8&quot;
Else
   If Val([A8]) <> Val([E49]) Then
      MsgBox &quot;A8 <> E49&quot;
   Else
      If Val([A8]) <> Val([E12]) Then
         MsgBox &quot;A8 <> E12&quot;
      Else
         If Val([A8]) <> Val([A23]) Then
            MsgBox &quot;A8 <> A23&quot;
         End If
      End If
   End If
End If

This should at least help determine where the inequity is.

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

 

[janetlyn]

Bob, back to my previous post, the values did not come up, and it stayed on the form. Different formula same problem. I thought maybe it was not going through the whole code and stopping before it got to the formula, so I put a message box at the end telling me it got there and it did. Why I am smart enough to figure out that use of message box and not smart enough to use it above like Cajun showed is anyone's guess.

While waiting to find out how I messed up the debugging code, I will go to the code and put in Cajun's. Thanks, and I will let you know what happened. Janet Lyn

 

[janetlyn]

Okay, I put the following code in (first 5 lines) and showed the If statement I am testing:

Dim SubTotal1 As Long
SubTotal1 = Val([E38]) + Val([E58])
If SubTotal1 <> Val([A21]) Then
MsgBox &quot;A21 <> E38+E58&quot;
End If
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

I did not get to the MsgBox A21<>E38+E38, however, I did get the MsgBox that it went through all the code, so I know it worked. However, it also did not go false. So although it is true, A21=E38+E58, it is not doing the after of Then (six lines). Now what do I do? Thanks so much for all the help, JL

 

[CajunCenturion]

It's awfully hard to piece things together when only seeing snippets of the code.

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

 

[rdodge]

Janet,

Did you look in the watch window for what the values were returned when you have:

Val([E38])

along with:

CLng([E38])

There is one difference between Val function and CLng function. Val function return any digits within the string while the CLng function converts the string to a long integer value provided the string can be converted to a numeric value.

Cajun,

With regards to your little quote, there's one other thing that I have came to realization dealing with 2-D shapes.

The smallest parameter around a fixed amount of an area is the circumference of a circle for that area size.

If all we have to work with are straight lines, then a perfect circle is consisted of infinite number of these straight lines

Example of the second:

Triangle has a large parameter for an area of 16sqft

A square has a smaller parameter of an area of 16sqft which is 16ft

an Octogon gets closer to a perfect circle with it's 8 sides for the same area and smaller parameter.

Of course, as the number of sides increases, the length of those sides decreases.

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

 

[CajunCenturion]

That's all fine and good rdodge, but what I think Mr. Einstein meant was that as you learn more and more (the circle of light increases), you realize how little you know, and how much more there is to know (the circumference of darkness).

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

 

[janetlyn]

You two are wayyyy tooooo smart for me. Cajun, here is the code. It is on the kindergarten level, but I will improve as I go along:

Option Compare Database
Private Sub Form_Close()
'When the form closes, all textboxes, lines, and boxes, are visible.
A1.Visible = True
A2.Visible = True
A3.Visible = True
A4.Visible = True
A5.Visible = True
A6.Visible = True
A7.Visible = True
A8.Visible = True
A9.Visible = True
A10.Visible = True
A11.Visible = True
A12.Visible = True
A13.Visible = True
A14.Visible = True
A15.Visible = True
A16.Visible = True
A17.Visible = True
A18.Visible = True
A19.Visible = True
A20.Visible = True
A21.Visible = True
A22.Visible = True
A23.Visible = True
A24.Visible = True
A25.Visible = True
A26.Visible = True
A27.Visible = True
A28.Visible = True
A29.Visible = True
A30.Visible = True
A31.Visible = True
A32.Visible = True
A33.Visible = True
E1.Visible = True
E2.Visible = True
E3.Visible = True
E4.Visible = True
E5.Visible = True
E6.Visible = True
E7.Visible = True
E8.Visible = True
E9.Visible = True
E10.Visible = True
E11.Visible = True
E12.Visible = True
E13.Visible = True
E14.Visible = True
E15.Visible = True
E16.Visible = True
E17.Visible = True
E18.Visible = True
E19.Visible = True
E20.Visible = True
E21.Visible = True
E22.Visible = True
E23.Visible = True
E24.Visible = True
E25.Visible = True
E26.Visible = True
E27.Visible = True
E28.Visible = True
E29.Visible = True
E30.Visible = True
E31.Visible = True
E32.Visible = True
E33.Visible = True
E34.Visible = True
E35.Visible = True
E36.Visible = True
E37.Visible = True
E38.Visible = True
E39.Visible = True
E40.Visible = True
E41.Visible = True
E42.Visible = True
E43.Visible = True
E44.Visible = True
E45.Visible = True
E46.Visible = True
E47.Visible = True
E48.Visible = True
E49.Visible = True
E50.Visible = True
E51.Visible = True
E52.Visible = True
E53.Visible = True
E54.Visible = True
E55.Visible = True
E56.Visible = True
E57.Visible = True
E58.Visible = True
E59.Visible = True
Q1.Visible = True
Q2.Visible = True
Q3.Visible = True
Q4.Visible = True
Q5.Visible = True
Q6.Visible = True
Q7.Visible = True
BoxA1E50Q3.Visible = True
BoxA2E11E48Q6Q4.Visible = True
BoxA21E38E58.Visible = True
BoxA26.Visible = True
BoxA5A20.Visible = True
BoxA7Q5.Visible = True
BoxA9.Visible = True
BoxE13.Visible = True
BoxE14E37A19.Visible = True
BoxE25E1E25A6E52.Visible = True
BoxE26.Visible = True
BoxE51E39A17.Visible = True
BoxE9E10E34E35A4A14A18.Visible = True
BoxQ2A3A25.Visible = True
BoxQ7Q1A22.Visible = True
Line176.Visible = True
Line183.Visible = True
Line186.Visible = True
Line189.Visible = True
Line193.Visible = True
Line204.Visible = True
Line207.Visible = True
Line209.Visible = True
Line211.Visible = True
Line213.Visible = True
Line215.Visible = True
Line217.Visible = True
Line219.Visible = True
Line221.Visible = True
Line223.Visible = True
AnnBill.Visible = True
AnnCatBill.Visible = True
AnnEmpHrs.Visible = True
AnnIndEmpHrs.Visible = True
CumBill.Visible = True
CumBillSubDisc.Visible = True
CumEmpHrs.Visible = True
CumExp.Visible = True
CumExpNoMark.Visible = True
CumInd.Visible = True
CumNetPL.Visible = True
CumRev.Visible = True
MoBill.Visible = True
MoEmpHrs.Visible = True
MoIndEmpHrs.Visible = True
End Sub

Private Sub Label99_Click()
'If changes were made, this button does all the queries again.
Call Form_Open(True)
DoCmd.Requery
End Sub

Private Sub Form_Open(Cancel As Integer)
'Because the report is pulling from reports that have parameters these allow me to just type in the dates once instead of answering each parameter separately.
DoCmd.Maximize
MsgBox &quot;Please answer date questions and wait for form to load. Thanks!&quot;
Dim CheckY
varYN = 7
CheckY = Yes
Do While varYN = 7
xYear = InputBox(&quot;Please enter a year (yyyy).&quot;, &quot;Enter Date&quot;, Date)
varYN = MsgBox(&quot;You have entered &quot; & xYear & &quot;,&quot; & vbCrLf & &quot;Is this correct?&quot;, vbYesNo, &quot;Correct Date?&quot
If varYN = 7 Then
CheckY = No
End If
Loop
Dim CheckM
varYN = 7
CheckM = Yes
Do While varYN = 7
xMonth = InputBox(&quot;Please enter a Month-Year (mm/dd/yyyy).&quot;, &quot;Enter Date&quot;, Date)
varYN = MsgBox(&quot;You have entered &quot; & xMonth & &quot;,&quot; & vbCrLf & &quot;Is this correct?&quot;, vbYesNo, &quot;Correct Date?&quot
If varYN = 7 Then
CheckM = No
End If
Loop
'Open each report, pull a variable, close report.
DoCmd.OpenReport &quot;RPT-ARClientBillHistAnnual&quot;, acViewPreview 'Var1
Me.A1 = Var1
DoCmd.Close acReport, &quot;RPT-ARClientBillHistAnnual&quot;
DoCmd.OpenReport &quot;RPT-BillAvg&quot;, acViewPreview 'Var2
Me.A2 = Var2
DoCmd.Close acReport, &quot;RPT-BillAvg&quot;
DoCmd.OpenReport &quot;RPT-MoInvTotalbyMo&quot;, acViewPreview 'Var3
Me.A3 = Var3
DoCmd.Close acReport, &quot;RPT-MoInvTotalbyMo&quot;
DoCmd.OpenReport &quot;RPT-ProfitLoss&quot;, acViewPreview 'Var4
Me.A4 = Var4
DoCmd.Close acReport, &quot;RPT-ProfitLoss&quot;
DoCmd.OpenReport &quot;RPT-ProfitLossbyClass&quot;, acViewPreview 'Var5
Me.A5 = Var5
DoCmd.Close acReport, &quot;RPT-ProfitLossbyClass&quot;
DoCmd.OpenReport &quot;RPT-EmpHrsOneMonth&quot;, acViewPreview 'Var6
Me.A6 = Var6
DoCmd.Close acReport, &quot;RPT-EmpHrsOneMonth&quot;
DoCmd.OpenReport &quot;RPT-Cat&quot;, acViewPreview 'Var7
Me.A7 = Var7
DoCmd.Close acReport, &quot;RPT-Cat&quot;
DoCmd.OpenReport &quot;RPT-EmpHrsCum&quot;, acViewPreview 'Var8
Me.A8 = Var8
DoCmd.Close acReport, &quot;RPT-EmpHrsCum&quot;
DoCmd.OpenReport &quot;RPT-EmpHrsCumJPC&quot;, acViewPreview 'Var9
Me.A9 = Var9
DoCmd.Close acReport, &quot;RPT-EmpHrsCumJPC&quot;
DoCmd.OpenReport &quot;RPT-EmpHrsCumRDH&quot;, acViewPreview 'Var10
Me.A10 = Var10
DoCmd.Close acReport, &quot;RPT-EmpHrsCumRDH&quot;
DoCmd.OpenReport &quot;RPT-EmpHrsCumWKB&quot;, acViewPreview 'Var11
Me.A11 = Var11
DoCmd.Close acReport, &quot;RPT-EmpHrsCumWKB&quot;
DoCmd.OpenReport &quot;RPT-EmpHrsCumJAB&quot;, acViewPreview 'Var12
Me.A12 = Var12
DoCmd.Close acReport, &quot;RPT-EmpHrsCumJAB&quot;
DoCmd.OpenReport &quot;RPT-EmpHrsCumCMT&quot;, acViewPreview 'Var13
Me.A13 = Var13
DoCmd.Close acReport, &quot;RPT-EmpHrsCumCMT&quot;
DoCmd.OpenReport &quot;RPT-ProfitLossbyClass&quot;, acViewPreview 'Var14
Me.A14 = Var14
DoCmd.Close acReport, &quot;RPT-ProfitLossbyClass&quot;
DoCmd.OpenReport &quot;RPT-EmpHrsCum&quot;, acViewPreview 'Var15
Me.A15 = Var15
DoCmd.Close acReport, &quot;RPT-EmpHrsCum&quot;
DoCmd.OpenReport &quot;RPT-EmpHrsCumJLD&quot;, acViewPreview 'Var16
Me.A16 = Var16
DoCmd.Close acReport, &quot;RPT-EmpHrsCumJLD&quot;
DoCmd.OpenReport &quot;RPT-EmpHrsAnnual&quot;, acViewPreview 'Var17
Me.A17 = Var17
DoCmd.Close acReport, &quot;RPT-EmpHrsAnnual&quot;
DoCmd.OpenReport &quot;RPT-ProfitLossbyBillType&quot;, acViewPreview 'Var18
Me.A18 = Var18
DoCmd.Close acReport, &quot;RPT-ProfitLossbyBillType&quot;
DoCmd.OpenReport &quot;RPT-ProfitLoss&quot;, acViewPreview 'Var19
Me.A19 = Var19
DoCmd.Close acReport, &quot;RPT-ProfitLoss&quot;
DoCmd.OpenReport &quot;RPT-ProfitLossbyBillType&quot;, acViewPreview 'Var20
Me.A20 = Var20
DoCmd.Close acReport, &quot;RPT-ProfitLossbyBillType&quot;
DoCmd.OpenReport &quot;RPT-ProfitLoss&quot;, acViewPreview 'Var21
Me.A21 = Var21
DoCmd.Close acReport, &quot;RPT-ProfitLoss&quot;
DoCmd.OpenReport &quot;QRY-MoInvbyDate&quot;, acViewPreview 'Var22
Me.A22 = Var22
DoCmd.Close acReport, &quot;QRY-MoInvbyDate&quot;
DoCmd.OpenReport &quot;RPT-ProjectHrs&quot;, acViewPreview 'Var23
Me.A23 = Var23
DoCmd.Close acReport, &quot;RPT-ProjectHrs&quot;
DoCmd.OpenReport &quot;RPT-EmpHrsCumOLD&quot;, acViewPreview 'Var24
Me.A24 = Var24
DoCmd.Close acReport, &quot;RPT-EmpHrsCumOLD&quot;
DoCmd.OpenReport &quot;RPT-ARClientsBillHistCum&quot;, acViewPreview 'Var25
Me.A25 = Var25
DoCmd.Close acReport, &quot;RPT-ARClientsBillHistCum&quot;
DoCmd.OpenReport &quot;RPT-EmpHrsAnnualJPC&quot;, acViewPreview 'Var26
Me.A26 = Var26
DoCmd.Close acReport, &quot;RPT-EmpHrsAnnualJPC&quot;
DoCmd.OpenReport &quot;RPT-EmpHrsAnnualRDH&quot;, acViewPreview 'Var27
Me.A27 = Var27
DoCmd.Close acReport, &quot;RPT-EmpHrsAnnualRDH&quot;
DoCmd.OpenReport &quot;RPT-EmpHrsAnnualWKB&quot;, acViewPreview 'Var28
Me.A28 = Var28
DoCmd.Close acReport, &quot;RPT-EmpHrsAnnualWKB&quot;
DoCmd.OpenReport &quot;RPT-EmpHrsAnnualJAB&quot;, acViewPreview 'Var29
Me.A29 = Var29
DoCmd.Close acReport, &quot;RPT-EmpHrsAnnualJAB&quot;
DoCmd.OpenReport &quot;RPT-EmpHrsAnnualCMT&quot;, acViewPreview 'Var30
Me.A30 = Var30
DoCmd.Close acReport, &quot;RPT-EmpHrsAnnualCMT&quot;
DoCmd.OpenReport &quot;RPT-EmpHrsAnnual&quot;, acViewPreview 'Var31
Me.A31 = Var31
DoCmd.Close acReport, &quot;RPT-EmpHrsAnnual&quot;
DoCmd.OpenReport &quot;RPT-EmpHrsAnnualJLD&quot;, acViewPreview 'Var32
Me.A32 = Var32
DoCmd.Close acReport, &quot;RPT-EmpHrsAnnualJLD&quot;
DoCmd.OpenReport &quot;RPT-EmpHrsAnnualOLD&quot;, acViewPreview 'Var33
Me.A33 = Var33
DoCmd.Close acReport, &quot;RPT-EmpHrsAnnualOLD&quot;
'Go through each group of amounts and confirm each group has the same value.
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
A1.Visible = False
E50.Visible = False
Q3.Visible = False
AnnBill.Visible = False
Line176.Visible = False
BoxA1E50Q3.Visible = False
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
E51.Visible = False
E39.Visible = False
A17.Visible = False
AnnEmpHrs.Visible = False
Line186.Visible = False
BoxE51E39A17.Visible = False
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
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([A18]))) 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([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
'ElseIf ([A9] + [A10] + [A11] + [A12] + [A13] + [A15] + [A16] + [A24]) = 78175.5 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;
'Debug.Print &quot;[A8]=&quot; & [A8]
'Debug.Print &quot;[A9]=&quot; & [A9]
'Debug.Print &quot;[A10]=&quot; & [A10]
'Debug.Print &quot;[A11]=&quot; & [A11]
'Debug.Print &quot;[A12]=&quot; & [A12]
'Debug.Print &quot;[A13]=&quot; & [A13]
'Debug.Print &quot;[A15]=&quot; & [A15]
'Debug.Print &quot;[A16]=&quot; & [A16]
'Debug.Print &quot;[A24]=&quot; & [A24]
'Debug.Print &quot;[E38]=&quot; & [E38]
'Debug.Print &quot;[E58]=&quot; & [E58]
'Debug.Print &quot;Is This Statement False ([A9]+[A10]+[A11]+[A12]+[A13]+[A15]+[A16]+[A24]=[E38]+[E58]): &quot; & [A21] = [E38] + [E58]
'MsgBox &quot;Debug - Evaluate Expression (Equals): &quot; & [A21] = ([E38] + [E58])
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

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

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([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
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([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

MsgBox &quot;I went to the end&quot;
End Sub

'Rest of code, works fine.

Private Sub EOMMM_Click()
Me!EOMMM.SpecialEffect = 2
Me!EOMMM.SpecialEffect = 1
DoCmd.Close acForm, &quot;FRM-EOM-Reconciliations&quot;
End Sub
Private Sub EOMMM_MouseDown(Button As Integer, Shift As Integer, X As Single, Y As Single)
Me!EOMMM.SpecialEffect = 2
End Sub
Private Sub EOMMM_MouseUp(Button As Integer, Shift As Integer, X As Single, Y As Single)
Me!EOMMM.SpecialEffect = 1
End Sub

Private Sub EH_Click()
Me!EH.SpecialEffect = 2
Me!EH.SpecialEffect = 1
DoCmd.Close acForm, &quot;FRM-EOM-Reconciliations&quot;
End Sub
Private Sub EH_MouseDown(Button As Integer, Shift As Integer, X As Single, Y As Single)
Me!EH.SpecialEffect = 2
End Sub
Private Sub EH_MouseUp(Button As Integer, Shift As Integer, X As Single, Y As Single)
Me!EH.SpecialEffect = 1
End Sub

Private Sub EXACC_Click()
Me!EXACC.SpecialEffect = 2
Me!EXACC.SpecialEffect = 1
DoCmd.Quit
End Sub
Private Sub EXACC_MouseDown(Button As Integer, Shift As Integer, X As Single, Y As Single)
Me!EXACC.SpecialEffect = 2
End Sub
Private Sub EXACC_MouseUp(Button As Integer, Shift As Integer, X As Single, Y As Single)
Me!EXACC.SpecialEffect = 1
End Sub

Private Sub HLP_Click()
Me!HLP.SpecialEffect = 2
Me!HLP.SpecialEffect = 1
DoCmd.Close acForm, &quot;FRM-EOM-Reconciliations&quot;
End Sub
Private Sub HLP_MouseDown(Button As Integer, Shift As Integer, X As Single, Y As Single)
Me!HLP.SpecialEffect = 2
End Sub
Private Sub HLP_MouseUp(Button As Integer, Shift As Integer, X As Single, Y As Single)
Me!HLP.SpecialEffect = 1
End Sub

Private Sub INS_Click()
Me!INS.SpecialEffect = 2
Me!INS.SpecialEffect = 1
DoCmd.Close acForm, &quot;FRM-EOM-Reconciliations&quot;
End Sub
Private Sub INS_MouseDown(Button As Integer, Shift As Integer, X As Single, Y As Single)
Me!INS.SpecialEffect = 2
End Sub
Private Sub INS_MouseUp(Button As Integer, Shift As Integer, X As Single, Y As Single)
Me!INS.SpecialEffect = 1
End Sub

Private Sub LST_Click()
Me!LST.SpecialEffect = 2
Me!LST.SpecialEffect = 1
DoCmd.Close acForm, &quot;FRM-EOM-Reconciliations&quot;
End Sub
Private Sub LST_MouseDown(Button As Integer, Shift As Integer, X As Single, Y As Single)
Me!LST.SpecialEffect = 2
End Sub
Private Sub LST_MouseUp(Button As Integer, Shift As Integer, X As Single, Y As Single)
Me!LST.SpecialEffect = 1
End Sub

Private Sub MI_Click()
Me!MI.SpecialEffect = 2
Me!MI.SpecialEffect = 1
DoCmd.Close acForm, &quot;FRM-EOM-Reconciliations&quot;
End Sub
Private Sub MI_MouseDown(Button As Integer, Shift As Integer, X As Single, Y As Single)
Me!MI.SpecialEffect = 2
End Sub
Private Sub MI_MouseUp(Button As Integer, Shift As Integer, X As Single, Y As Single)
Me!MI.SpecialEffect = 1
End Sub

Private Sub MNT_Click()
Me!MNT.SpecialEffect = 2
Me!MNT.SpecialEffect = 1
DoCmd.Close acForm, &quot;FRM-EOM-Reconciliations&quot;
End Sub
Private Sub MNT_MouseDown(Button As Integer, Shift As Integer, X As Single, Y As Single)
Me!MNT.SpecialEffect = 2
End Sub
Private Sub MNT_MouseUp(Button As Integer, Shift As Integer, X As Single, Y As Single)
Me!MNT.SpecialEffect = 1
End Sub

Private Sub PI_Click()
Me!PI.SpecialEffect = 2
Me!PI.SpecialEffect = 1
DoCmd.Close acForm, &quot;FRM-EOM-Reconciliations&quot;
End Sub
Private Sub PI_MouseDown(Button As Integer, Shift As Integer, X As Single, Y As Single)
Me!PI.SpecialEffect = 2
End Sub
Private Sub PI_MouseUp(Button As Integer, Shift As Integer, X As Single, Y As Single)
Me!PI.SpecialEffect = 1
End Sub

Private Sub RPT_Click()
Me!RPT.SpecialEffect = 2
Me!RPT.SpecialEffect = 1
DoCmd.Close acForm, &quot;FRM-EOM-Reconciliations&quot;
End Sub
Private Sub RPT_MouseDown(Button As Integer, Shift As Integer, X As Single, Y As Single)
Me!RPT.SpecialEffect = 2
End Sub
Private Sub RPT_MouseUp(Button As Integer, Shift As Integer, X As Single, Y As Single)
Me!RPT.SpecialEffect = 1
End Sub

Private Sub RTMM_Click()
DoCmd.Close
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = &quot;FRM-Mainmenu&quot;
DoCmd.OpenForm stDocName, , , stLinkCriteria
DoCmd.Close acForm, &quot;FRM-EOM-Reconciliations&quot;
End Sub
Private Sub RTMM_MouseDown(Button As Integer, Shift As Integer, X As Single, Y As Single)
Me!RTMM.SpecialEffect = 2
End Sub
Private Sub RTMM_MouseUp(Button As Integer, Shift As Integer, X As Single, Y As Single)
Me!RTMM.SpecialEffect = 1
End Sub


As you can see, there is some code REM'd out as I was trying different things to check the formulas.

Cajun, the formula I am working on at this time is:

If ((CLng([E25]) = CLng([E1])) Or (CLng([E25]) = CLng([A6])) Or (CLng([E25]) = CLng([E52]))) Then

All four textboxes stay visible with the number 1611 in each box. So obviously they all match and should be invisible. I tried the debugging method Ronald gave me and I just cannot get it to work as stated previously.

Remember, you asked for all this code. Smile. Thank you so much for your help. I am so confused what the problem could be.

Janet Lyn

 

[rdodge]

One thing that would normally help with coding for the debugging process, use indentions in an effective manner. Your use of indentions does get started in the right direction, but for nesting statements like 4 levels of If, Then, Else's, look at the following as a general example of what I mean:

Sub Form_Open()
If <Criteria> Then
<Statements>
If <Criteria> Then
<Statements>
If <Criteria> Then
<Statements>
If <Criteria> Then
<Statements>
End If
End If
End If
End If
End Sub

Also note, once one of the If statements becomes false, it will go to the else part, if there is an else part, otherwise, it will go to &quot;End If&quot;. For this, you need to think carefully with regards to how you want your code to work with If, Then, [ElseIf, Then, (could be multiples of these),] [Else,] End If branchings and nestings.

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

 

[janetlyn]

Well that makes sense. I will fix that. Do you have any idea why I cannot get the debugging to work as you explained how to do up above? JL

 

[rdodge]

Given it said &quot;<Out of context>&quot; error message, you can edit the watch directly by remove the CLng() portion of it and then click out of it. By dong that, you can then see if the object itself returns it's value and what type of value it is. If that still shows &quot;<Out of Context>&quot;, you may then need to prequalify your object witht he form object such as using the keyword &quot;Me&quot; withou the quotes like:

Me.A21

or

Me.A21.Value

Normally speaking, when you get an &quot;<Out of context>&quot; error message in the Watch Window, it's cause:

the Object/Variable hasn't been properly prequalified (this can happen and VBA think the object/variable belongs to a parent object, which doesn't have the object/variable)

the Object/Variable hasn't been initiated (This is normally an occurance when either a variable hasn't been properly declared, or the object isn't currently established)

the Object/Variable is part of a different method (procedure or function) than the method that the break is currently in. With this particular reason, it doesn't mean there is anything wrong with the code itself, but it's something to keep in the back of your mind, and how to setup watches.

These are just a few of the types of reasons for such an error message, but are the more common reasons why for it.

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

 

[janetlyn]

Okay, I get it. As soon as I put in the Watch, the value of the textbox should &quot;automatically&quot; appear. Unfortunately, that is just not gonna happen in this case. I believe it is because all the E32 type textboxes are tied to an Excel spreadsheet; and all the A2 type textboxes are tied to a Public Module. So, to find out the value of a textbox is it possible to put code at the end of all the EndIf's and have it tell me what the values are. No laughing, but will this work?

Msgbox &quot;The value of E32 is&quot; & [E32]

Or maybe put that line directly after the If statement it is in?

Thanks for the help, JL