After Update/ On Lost Focus - change record & save

  • Thread starter Thread starter A Hopper
  • Start date Start date
A

A Hopper

I have a bound form and I am using a combo box to select
the record that will populate the form.

In the After Update I have the following:
' Find the record that matches the control.
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[PackageRecordID] = " & Str(Nz(Me!
[SearchPackageSurRecord], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
DoCmd.Hourglass True
DoCmd.Echo False
With Me
..Total6PVSurCarton = Val(Nz(.ULA)) + Val(Nz(.URA)) + Val(Nz
(.CLA)) + Val(Nz(.CRA)) + Val(Nz(.LLA)) + Val(Nz(.LRA))
..Total6PVSurCarton.Requery
tex1 = Nz(.UL6PVSurPack)
tex2 = Nz(.UR6PVSurPack)
tex3 = Nz(.CL6PVSurPack)
tex4 = Nz(.CR6PVSurPack)
tex5 = Nz(.LL6PVSurPack)
tex6 = Nz(.LR6PVSurPack)
End With

Dim ctl As Control

Dim dblLowest As Double
Dim dblHighest As Double
Dim dblValue As Double

Dim intControl As Integer
dblLowest = Nz(Me.tex1)
dblHighest = Nz(Me.tex6)

For intControl = 1 To 6
'loop through controls that contain values
'that you want to find highest & lowest from
'This code assumes they are named txtValue1,
txtValue2, txtValue3 etc.

dblValue = Nz(Me.Controls("tex" &
intControl).Value)

If dblValue < dblLowest Then dblLowest = dblValue
If dblValue > dblHighest Then dblHighest = dblValue

Next

'show values on form
Me.MinSurAv = dblLowest
Me.MaxSurAv = dblHighest

Me.MinSurAv.Requery
Me.MaxSurAv.Requery
DoCmd.Hourglass False
DoCmd.Echo True

The above seems to work well.


In the On Lost Focus I have:

Dim PackType As Integer
Dim MasterQty As Single
PackType = Nz([Forms]![SixDifinPackageForm]!
[PackagingTypeID])
MasterQty = Nz([Forms]![SixDifinPackageForm]!
[MasterCartonQty])
If Nz(Me.PlateBlockPack) < Nz([Forms]!
[SixDifinPackageForm]![PlateBlockPack]) Or Nz
(Me.PlateBlockPack) > Nz([Forms]![SixDifinPackageForm]!
[PlateBlockPack]) Then
MsgBox "WARNING! Surplus PLATE BLOCK is different!",
vbOKOnly
End If
If Nz(Me.PackagingTypeID) < Nz([Forms]!
[SixDifinPackageForm]![PackagingTypeID]) Or Nz
(Me.PackagingTypeID) > Nz([Forms]![SixDifinPackageForm]!
[PackagingTypeID]) Then
Answer = MsgBox("WARNING!" & vbCrLf & "SURPLUS
PACKAGING TYPE is different from the PRESENT CHOICE." &
vbCrLf & " Do you want to change the present Pack
Type?", vbYesNo + vbQuestion)
If Answer = vbYes Then
Me.PackagingTypeID = PackType
End If
If Answer = vbNo Then
End If
End If
If Nz(Me.MasterCartonQty) < Nz([Forms]!
[SixDifinPackageForm]![MasterCartonQty]) Or Nz
(Me.MasterCartonQty) > Nz([Forms]![SixDifinPackageForm]!
[MasterCartonQty]) Then
Answer = MsgBox("WARNING!" & vbCrLf & "Surplus MASTER
CARTON QUANTITY is different than the PRESENT CHOICE of ""
& MasterQty & ""!" & vbCrLf & " Do you want to change the
MASTER CARTON QTY?", vbYesNo + vbQuestion)
If Answer = vbYes Then
Me.MasterCartonQty = MasterQty
End If
If Answer = vbNo Then
End If
End If


When I say Yes to the changes I get the following error:

Run time error '-2147352567 (80020009)':

Update or CancelUpdate without AddNew or Edit.

When I debug Me.PackagingTypeID = PackType is highlighted.
This is the update I answered Yes to.


When I made the following change
If Answer = vbYes Then
Me.AllowEdits
Me.PackagingTypeID = PackType
End If
the populating of the record on the form was cancelled.

I want to update the information and then save the record
so I can use the record with the new information.

Thank you for your help.

Allan
 
Back
Top