VBA on Update is not working

[khodem]

Hi guys,



I have a forum attached is the screenshot:

- I have if statement in check box when someone click on that check box it will fill the value automaticllay it's static value for each
- once the user click on check box on the (Total Quantity) I have expression that says (=value+value2)
- and on this (Total Quantity) I have VBA on (AfterUpdate) and the condition is if >40 set it back to 40 and give me msgbox warning me
as well as it will calculate this value here is the code:

Private Sub s1totalqtty_AfterUpdate()
If Me.s1totalqtty > 40 Then
MsgBox "Your Main stock is only 40"
Me.s1totalqtty = 40
End If


If Me.s1totalqtty > 10 Then
Me.s1totalfees = (Me.Count_Of_Days * 150) + 1465
End Sub

but the problem is now it's not working... can you suggest any changes?

thanks

[ItsMe]

You have two if's and this means you need two end if's

If Me.s1totalqtty > 10 Then
Me.s1totalfees = (Me.Count_Of_Days * 150) + 1465
End if

Maybe there is a way you can check for conditions between 10 and 40 then use else or else if for values greater than 40

[khodem]

Hi,

thanks for replay... even when I put one if it's not working... I don't know if (=value+value2) in a field treated as AfterUpdate EVENT in access or not... I even tried AfterChange EVENT without any success, but what I notice is when I remove (=value+value2) from TotalQuanity and manually insert the value it works but not with (=value+value2) im not sure if access treat this as update or what?

this field is a table field so value will be saved in table.

and another strange thing I notice is when I check box all those then the value of TotalFee is zero and once I go and try to add value in it, it will not allow me but instead it calculate what I told him to calculate in IF statement IDK... my if run AfterUpdate of TotalQuanity as you can see not Totalfeee

regards

[ItsMe]

Well I am not trying to judge the logic behind the math. I was merely pointing out the syntax error.

It does not make sense to evaluate a field and then update that field. It kinda defeats the purpose of evaluation. Why not go straight to updating it?

[khodem]

Well i'm not sure but my goals here is :

1- if Value of TotalQaunitity went >40 I want to warn the user that it's going over stock(value of TotalQuanity comes from those check boxes each one of them have static value and this controled by IF statement i.e if I check the box 32X40 it will automatically set the value to 40) there is a lot of these check boxes
2- if the value of TotalQuantity is set >10 I will do some math calculation and set the value of Fees to the value of calculation which is shown in above

so if you have any good way to do this other than IF please share as i'm stuck here my mind is not working .

regards

[ItsMe]

Still don't quite understand. s1totalqtty should not be adjusted if this is the field you are trying to evaluate. If you want to adjust fees based on an evaluation of s1totalqtty then the following should work. Alternatively, you can create a select case statement.

If Me.s1totalqtty >= 10 and <=40 Then
MsgBox "Your Main stock is between 10 and 40"
else if Me.s1totalqtty > 40 Then
MsgBox "Your Main stock is GREATER than 40"
' Me.s1totalqtty = 40
else
MsgBox "Your Main stock is less than 10"

End If

[khodem]

Hi thanks... so what event you think this should be placed? onupdate, after updte? ?

[ItsMe]

I really do not know. It seems as though the code block is evaluating a calculated field. So after the calculation is done would probably be appropriate. However, a before update event could do some calculations and make sure it is OK to update a field or cancel. When I look at what you have posted, it is hard for me to discern what is being updated, what is getting calculated, and what is being evaluated. Perhaps you should consider these points and determine the appropriate place to do these three actions.

[ssanfu]

It is still not clear what the problem is, but in addition to what ItsMe has said, I am confused on the following:

(Total Quantity) I have expression that says (=value+value2)

You keep saying "value + value2". Are you using that to get the total?
Did you really name the controls "value" and "value2"?????
"Value" is a reserved word in Access and shouldn't be used for object names... http://allenbrowne.com/AppIssueBadWord.html

If the two controls shown in the picture are named "txt32_32" and "txt32_40", then the expression in the totals field should be "= Me.txt32_32 + Me.txt32_40"