Message box error

[astarbyfar]

In a form Ive created I have data entry field that prompts the user to enter a figure which we will call tonnage. I then using the below code check to see if this number is valid i.e. whether it is above the recommended tonnage

Private Sub Tonnage2_BeforeUpdate(Cancel As Integer)
Recommended2 = (Press2Max * 0.8)

If Me.Tonnage2 > Recommended2 Then
If MsgBox("The tonnage exceeds the recommended tonnage. Do you wish to continue?", vbYesNo, "Exceeded tonnage") = vbNo Then Cancel = True
End If

End Sub

There is no problem with this. It works! However, in another case I need to do a sum of four user inputs which is calculated correctly by the form and then stores the result in a text box called Innertotal. The result in inner total must then be checked through the same procedure as above. Ive tried in all events using similar code to the above but still no luck. Cheers Mike

 

[Rich]

You shouldn't store the calculated total, use a query or calculated control, you can use the BeforeUpdate event of the form to verify the calculation

 

[astarbyfar]

Im not storing the calculated total but it still must be displayed on the form! The text box innertotal displays the total of four user inputs! Then this total must be checked to see if tonnage exceeds the recommended tonnage.

 

[modest]

If the problem is with Innertotal's procedure, why didn't you just post that instead!???

Make sure the textbox isn't being evaluated as text, make sure you're using the correct datatypes

Try:
If Cdbl(Me.Tonnage2) > dbl(Recommended2) Then

I have no idea what Press2Max is or what Recommended2 is.
What I mean by having no idea is.. you cannot see what datatype it is/what value is coming in

It is good programming practice to declare your variables within your functions so that cases like these dont come up.

 

[astarbyfar]

Modest!

Thanks for the suggestions! The datatype of Press2Max is a number. This field stores the maximum value allowed for each Press. Recommended is then used to calculate the recommended tonnage which is 80% of the maximum tonnage hence the calculation

recommended = (PressInnerMax * 0.8)

However the textbox Innertotal has no data type as it is a calculated field which as Rich pointed out must not be stored in the database. Its control source is

=[Inner front left]+[Inner front right]+[Inner rear left]+[Inner rear right]

Now the total of the above calculation is displayed correctly in the text box Innertotal but the seems to be no check on this calculated field to see whether it goes over the recommended tonnage. The code I have upto now is

Private Sub Innertotal_BeforeUpdate()
Recommended1 = (PressInnerMax * 0.8)

If Innertotal > Recommended1 Then
If MsgBox("The tonnage exceeds the recommended tonnage. Do you wish to continue?", vbYesNo, "Exceeded tonnage") = vbNo Then Cancel = True
End If

End Sub

This obviously doesnt work. Any new suggestions. Im aware how sketchy this description is! Mike

 

[modest]

You need to define your variables. I'm sorry but I cant spend all day seeing what is a textbox/object and what is just a numbered variable.

I have repeatedly preached on why the Dim statement is used and why we should use good programming practice. One of the reasons mentioned is so that others can easily follow your code. Make the necessary updates and post back.

 

[Rich]

Use the BeforeUpdate event of the form, not the control

 

[astarbyfar]

Rich Im now getting the message box to appear when Im about to exit the form. Essentially I need it to appear as soon as the total from the four text boxes goes over the recommended tonnage.

Modest Im awful on VBA so I dont really know how to declare a number variable using Dim. Is the following correct?

Dim PressInnerMax AS Number

As I stated in the above all my fields are data type number so there really isnt much point but if you let me know how to decalare them I'll make the neccessary adjustments. Mike

 

[modest]

Sort of, but "Number" would be the type of number you want:

Types:

Byte 					1 byte 		0 to 255 
Boolean 				2 bytes 	True or False 
Integer 				2 bytes 	-32,768 to 32,767 
Long(long integer) 			4 bytes 	-2,147,483,648 to 2,147,483,647 
Single(single-precision floating-point) 4 bytes 	-3.402823E38 to -1.401298E-45 for negative values; 1.401298E-45 to 3.402823E38 for positive values 
Double(double-precision floating-point) 8 bytes 	-1.79769313486231E308 to -4.94065645841247E-324 for negative values; 4.94065645841247E-324 to 1.79769313486232E308 for positive values 
Currency(scaled integer) 		8 bytes 	-922,337,203,685,477.5808 to 922,337,203,685,477.5807 
Decimal 				14 bytes 	+/-79,228,162,514,264,337,593,543,950,335 with no decimal point; 
			+/-7.9228162514264337593543950335 with 28 places to the right of the decimal; smallest non-zero number is 
			+/-0.0000000000000000000000000001

 

[Rich]

Rich Im now getting the message box to appear when Im about to exit the form. Essentially I need it to appear as soon as the total from the four text boxes goes over the recommended tonnage.

Then you'll have to put the code in the Form_Current event and call it in the after update event of your data entry textboxes . there's no way that I know of that can calculate the total before you attempt to leave a control

 

[modest]

there's no way that I know of that can calculate the total before you attempt to leave a control

Evaluate at KeyPress

 

[Rich]

Evaluate at KeyPress

Post the code

 

[astarbyfar]

Private Sub Innertotal_BeforeUpdate()

Dim PressInnerMax As Integer
Dim Recommended1 As Integer
Dim Innertotal As Integer ' Not sure about this because this is a calculated field

Recommended1 = (PressInnerMax * 0.8)

If Innertotal > Recommended1 Then
If MsgBox("The tonnage exceeds the recommended tonnage. Do you wish to continue?", vbYesNo, "Exceeded tonnage") = vbNo Then Cancel = True
End If

End Sub

Ive left this in the BeforeUpdate field because i think this is where the problem lies. Im repeating myself now because Im certain that Im not making myself clear!

The Innertotal is a text box made up of the values enetered by the user via four text boxes named;

[Innerrearright]+[Innerrearleft]+[Innerfrontleft]+[Innerfrontright]

The Innertotal must not exceed the 80% of the PressInnerMax. An example scenario is when the PressInnerMax is 20 and the user enters ;

10 in the Innerrearright
8 in the Innerrearleft and
4 in the Innerfrontleft

As soon as the user attempts to leave the text box named Innerfrontleft then he/she will get the error message.

 

[asingh]

Would it be possible to use the "keyup" event of the 4 data entry boxes...to run a sequence that will add up all four data inputs..display the total in a fifth txt box , and use a condition to check if it is greater than a value and display the msg box

 

[modest]

Private Sub Innertotal_BeforeUpdate()

Dim PressInnerMax As Integer
Dim Recommended1 As Integer
Dim Innertotal As Integer ' Not sure about this because this is a calculated field

Recommended1 = (PressInnerMax * 0.8)

I know this is a simple problem, but I'm struggling to understand how PressInnerMax just gets a value. First you declare it as an integer (which it's a null). Then you multiply it by .8 and see if Recommended is equal to it?

How does it get a value??

 

[astarbyfar]

Modest

This is what I meant when saying that I didnt need to declare the variables. PressInnerMax is a value entered into the database via the user. This PressInnerMax is just a variable that stores the maximum allowed tonnage. However the company in question recommends that a tonnage of 80% of the maximum allowed is only ever used but in some cases +80% is used. I am very close to it at the moment. Ive simply produced the following procedure for each of the four text boxes

Private Sub Innerrearleft_BeforeUpdate(Cancel As Integer)
recommended = ((PressInnerMax / 100) * 80)
total = Innerrearleft + Innereearright + Innerfrontright + Innerfrontleft

If total1 > recommended1 Then
If MsgBox("The tonnage exceeds the recommended tonnage. Do you wish to continue?", vbYesNo, "Exceeded tonnage") = vbNo Then Cancel = True
End If
End Sub

However one will notice that recommended and total will be declared four times so am I right by typing the following code in the General Declarations method/procedure

Dim total As Integer

total = [Innerrearleft]+[Innerrearright]+[Innerfrontleft]+[Innerfrontright]

Dim recommended As Integer

recommended = (PressInnerMax * 0.8)

Thanks for any advise. Much appreciated