VBA help

[astarbyfar]

Just wondering if anyone can help! I have the following code;

Private Sub Tonnage6_AfterUpdate()

If Tonnage6.Value > Press6Max.Value Then

Dim bytResponse As Byte

bytResponse = MsgBox("The tonnage exceeds the maximum allowed. Do you wish to continue", vbYesNo, "Exceeded tonnage")

If bytResponse = vbNo Then ' user selected No
' do your No code here
Me.Tonnage6.SetFocus
Else ' otherwise, user has selected cancel
'do your Yes code
Me.Press5.SetFocus
End If

End Sub

What I need it to do is bring a message box up if the value in Tonnage 6 is > the value in Press6Max. If the user then clicks on Yes then he/she goes to the next field in the tab sequence. Else if they click no the focus is set to and the value highlighted in the field Tonnage 6. Im really poor at VBA and this may be very simple but im really struggling. Thanks for any help

 

[Mile-O]

    If Me.Tonnage6 > Me.Press6Max Then
        If MsgBox("The tonnage exceeds the maximum allowed. Do you wish to continue", vbYesNo, "Exceeded tonnage") = vbYes Then
            Me.Press5.SetFocus
        Else
            Me.Tonnage6.SetFocus
        End If
    End If

Also, please don't PM me with questions. There's a forum for that sort of thing.

 

[astarbyfar]

Still no luck!

Apoligies for PM! There still seems to be a problem with the above code. Im still receiving the message with a vbOK format instead of vbYesNo. Also when I click on OK there seems to be no focus structure. If its any use Im using Access XP! Any help is much appreciated

 

[astarbyfar]

A little bit better

I have sorted out the message box and when I click yes to continue it does go to the right field. However when I click No to continue it should go back to the text box where I have previously visited. Which it doesnt! Should I be typing refresh or anything similar?

 

[Rich]

Your code is in the wrong event, use the BeforeUpdate event

 

[astarbyfar]

Error

Ive to0ok the advice above and put the following code in

Private Sub Tonnage6_BeforeUpdate(Cancel As Integer)

If Me.Tonnage6 > Me.Press6Max Then
If MsgBox("The tonnage exceeds the maximum allowed. Do you wish to continue", vbYesNo, "Exceeded tonnage") = vbNo Then Tonnage6.SetFocus

ElseIf vbYes Then Press5.SetFocus
End If

End Sub

However I am now getting the following error

Run-time error '2108':

You must save the field before you execute the GoToControl action, the GoToControl Method, or the SetFocus Method.

Any help will be much appreciated Im nearly finished in this rather annoying form!

 

[Rich]

Private Sub Tonnage6_BeforeUpdate(Cancel As Integer)

If Me.Tonnage6 > Me.Press6Max Then
If MsgBox("The tonnage exceeds the maximum allowed. Do you wish to continue", vbYesNo, "Exceeded tonnage") = vbNo Then Cancel = True


Else Press5.SetFocus
End If

End Sub

 

[astarbyfar]

Nearly there

Rich that seems to work howvere there is a new problem! When Im entering values that are within the boundaries i.e. Tonnage 6 < Press6Max I am getting the error that i previously mentioned in thread five. Thanks for all your help by the way!

 

[astarbyfar]

Finito!!!

Ive eventually managed to finish it. Ive removed the Else statement used above and just allowed the predefined tab control to take effect. Thanks for all your help!