Loop function where to begin

[oxicottin]

Hello, I have tried everything on getting this result in a mesage box so I figure mabe looping might give me the result needed but I dont understand how it works and after reading through the web I cant grasp the consept for some reason without an example that pertains to my situation.

Here is the issue im having, I have a main form [frm_Hold] with two subforms [frmsub_ProductHoldData], [fsub_RejectedData] and each sub form is a continious form BUT the main form isnt. Now there is a textbox on each subform..

1) [frmsub_ProductHoldData] with record source [tbl_frmsub_ProductHoldData] and has a textbox txtCartonsHeld with its control source [CartonsHeld]

2) [fsub_RejectedData] with record source [tblsub_RejectedHoldData] and has a textbox txtCartonRejected with its control source [CartonRejected]

Now, I have in the subforms footer a textbox txtSumOfCartonRejected that sums =Sum([CartonRejected]) and same with txtSumOfCartonsHeld =Sum([CartonsHeld]). What I need to do is when I enter an number in txtCartonsHeld it needs to check and see if new SUM would be less than txtSumOfCartonRejected and if so have a message and in the subforms textbox txtCartonRejected when I enter a number it will check to see if is not greater than the toal sum of [CartonsHeld]

Like this but it doesnt work becuse it doesnt update untill you move focus unto the next record and I need it to work on the before update..


Dim strSumOfRejectRework As String
Dim strSumOfHoldCts As String
Dim cancel As Integer

strSumOfHoldCts = Me.txtSumOfCartonsHeld
strSumOfRejectRework = Nz([Forms]![frm_Hold]![fsub_RejectedData].[Form].[txtSumOfCartonRejected], 0) _
+ Nz([Forms]![frm_Hold]![fsub_ReworkedData].[Form].[txtSumOfCartonReworked], 0)

If strSumOfHoldCts > strSumOfRejectRework Then
MsgBox "Check your totals and try again.", vbInformation
txtCartonsHeld.Undo
cancel = True
End If

[jonh]

Sounds like you need to use oncurrent to store the actual and compare that to the changed value @ beforeupdate.


An example file might be useful here.

Not sure why you'd think a loop would help...?

[SamT]

Convert the String to numbers as soon as possible

Dim lngSumOfRejectRework As Long
Dim lngSumOfHoldCts As Long
Dim cancel As Integer

lngSumOfHoldCts = CLng(Me.txtSumOfCartonsHeld )
SumOfRejectRework = CLng(Nz([Forms]![frm_Hold]![fsub_RejectedData].[Form].[txtSumOfCartonRejected], 0) _
+ Nz([Forms]![frm_Hold]![fsub_ReworkedData].[Form].[txtSumOfCartonReworked], 0))

If lngSumOfHoldCts > lngSumOfRejectRework Then
MsgBox "Check your totals and try again.", vbInformation
txtCartonsHeld.Undo
cancel = True
End If

[oxicottin]

Ok I created a demo of the forms and some text on each form of what is needed.

@SamT, I tried your example VBA and it gives the exact same results as what im getting?

[SamT]

Unfortunately, I cannot open accdb files with Access XP.

Have you tried

If strSumOfHoldCts > strSumOfRejectRework Then
MsgBox "Check your totals and try again.", vbInformation
txtCartonsHeld.Undo
cancel = True
txtCartonsHeld .SetFocus
End If

[jonh]

Private Sub txtCartonsHeld_BeforeUpdate(cancel As Integer)
Dim diff As Double

diff = txtSumOfCartonsHeld + (txtCartonsHeld - txtCartonsHeld.OldValue)

cancel = diff > Nz(Parent![fsub_RejectedData].[Form]![txtSumOfCartonRejected], 0) _
+ Nz(Parent![fsub_ReworkedData].[Form]![txtSumOfCartonReworked], 0)

If cancel Then
MsgBox "Your total calculated cartons are higher than what you have " & _
"entered for your total reject/rework." & vbCrLf & vbCrLf & _
"Please check your numbers!", vbInformation
End If
End Sub

[oxicottin]

Private Sub txtCartonsHeld_BeforeUpdate(cancel As Integer)
Dim diff As Double

diff = txtSumOfCartonsHeld + (txtCartonsHeld - txtCartonsHeld.OldValue)

cancel = diff > Nz(Parent![fsub_RejectedData].[Form]![txtSumOfCartonRejected], 0) _
+ Nz(Parent![fsub_ReworkedData].[Form]![txtSumOfCartonReworked], 0)

If cancel Then
MsgBox "Your total calculated cartons are higher than what you have " & _
"entered for your total reject/rework." & vbCrLf & vbCrLf & _
"Please check your numbers!", vbInformation
End If
End Sub


@jonh your example is working but is backwards to what im needing, Oldvalue is what did it!!.... :thumb Im going to play around with it and see if I can understad it and get it to work in all 3 subforms and post back my results. Thank You!

[oxicottin]

Yes @jonh it worked with all 3 subforms thank you for your help and thank you everyone else also.