weird glitch on a simple text box command

[grgimpy]

My form has two main text boxes that are not working correctly with the code I wrote. Upon opening the form, the operator must select a "Part Number" from a list box. Once a "Part Number" is selected, some code fills in some parameters in other text boxes and brings up a subform based on a query. This all works fine. The operator must then enter in eight "Thickness" values. Once the last value is entered, three text boxes should calculate the "Average", "Range", and "Moving Range".

The glitch is this: when an operator selects a "Part Number" and enters the last "Thickness" value, then the "Average" and "Range" are calculated fine and show up. The "Moving Range" text box contains no value. If I select the same "Part Number" again, then re-enter the last "Thickness" value, all three (Average, Range, Moving Range) values are calculated.

Basically, the code does not work initially. But if re-select the part number, and re-enter the data, it works fine.

I guess I'm hoping someone out there has ran into a similar problem and can point me in the right direction towards fixing it. I'll include some of the code I use to perform these operations.

This what I came up with when the form is opened:

Private Sub Form_Open(Cancel As Integer)
On Error GoTo Form_Open_Err

Me.Refresh
DoCmd.Maximize
DoCmd.GoToRecord acForm, "Ni-Au - Main Page", acNewRec
Me.cboPartNumber.SetFocus

Me.Previous_Record_Button.Enabled = True
Me.Next_Record.Enabled = False
Me.AddRecord.Enabled = True

Me.Ni_Au_CooperSubformQuery_subform.Visible = False
Me.Ni_Au_XeroxSubformQuery_subform.Visible = False
Me.Ni_Au_OSRAMSubFormQuery_subform.Visible = False
Me.Ni_Au_CyndiSubformQuery_subform.Visible = False
Me.Ni_Au_Valeo75SubformQuery_subform.Visible = False
Me.Ni_Au_Valeo79SubformQuery_subform.Visible = False

Me.NiUCL.Caption = ""
Me.NiLCL.Caption = ""
Me.AuLCL.Caption = ""
Me.AuUCL.Caption = ""

Form_Open_Exit:
Exit Sub
Form_Open_Err:
MsgBox Error$
Resume Form_Open_Exit

End Sub


Here's the code for the "Part Number" routine:
Private Sub cboPartNumber_AfterUpdate()

Dim NiAve, NiSigma, AuAve, AuSigma
If Me.[cboPartNumber] = "156882-001" Then

NiAve = DLookup("[Ni-Au-CooperNiAve]", "Ni-Au-Production Log", "[ID]=" & DMax("[ID]", "Ni-Au-Production Log"))
NiSigma = DLookup("[Ni-Au-CooperNiSigma]", "Ni-Au-Production Log", "[ID]=" & DMax("[ID]", "Ni-Au-Production Log"))
Me.NiLCL.Caption = NiAve - 3 * NiSigma
Me.NiUCL.Caption = NiAve + 3 * NiSigma
AuAve = DLookup("[Ni-Au-CooperAuAve]", "Ni-Au-Production Log", "[ID]=" & DMax("[ID]", "Ni-Au-Production Log"))
AuSigma = DLookup("[Ni-Au-CooperAuSigma]", "Ni-Au-Production Log", "[ID]=" & DMax("[ID]", "Ni-Au-Production Log"))
Me.AuLCL.Caption = AuAve - 3 * AuSigma
Me.AuUCL.Caption = AuAve + 3 * AuSigma

Me.Ni_Au_CooperSubformQuery_subform.Form.Recordset.MoveLast
Me.Ni_Au_CooperSubformQuery_subform.Visible = True
Me.Ni_Au_XeroxSubformQuery_subform.Visible = False
Me.Ni_Au_OSRAMSubFormQuery_subform.Visible = False
Me.Ni_Au_CyndiSubformQuery_subform.Visible = False
Me.Ni_Au_Valeo75SubformQuery_subform.Visible = False
Me.Ni_Au_Valeo79SubformQuery_subform.Visible = False

Me.Part_Name = DLookup("[PartTbl-Part Name]", "Ni-Au-Production Table", "[PartTbl-Part Name] = 'COOPER'")
Me.txtGoldType = DLookup("[PartTbl-Ni-Au-Gold Type]", "Ni-Au-Production Table", "[PartTbl-Part Name] = 'COOPER'")
Me.Ni_Au_Nickel_Amps = DLookup("[PartTbl-Ni-Au-Nickel Amps]", "Ni-Au-Production Table", "[PartTbl-Part Name]='COOPER'")
Me.Ni_Au_Strike_Gold_Amps = DLookup("[PartTbl-Ni-Au-Strike Gold Amps]", "Ni-Au-Production Table", "[PartTbl-Part Name]='COOPER'")
Me.Ni_Au_Soft_Gold_Amps = DLookup("[PartTbl-Ni-Au-Soft Gold Amps]", "Ni-Au-Production Table", "[PartTbl-Part Name]='COOPER'")
End If

Here's the code that calculates "Average", "Range", and "Moving Range":

Private Sub Ni_Au_Nickel_Thickness_8_AfterUpdate()
Me.[Ni_Au_Nickel_Thickness_Avg] = ([Ni_Au_Nickel_Thickness_1] + [Ni_Au_Nickel_Thickness_2] + [Ni_Au_Nickel_Thickness_3] + [Ni_Au_Nickel_Thickness_4] + [Ni_Au_Nickel_Thickness_5] + [Ni_Au_Nickel_Thickness_6] + [Ni_Au_Nickel_Thickness_7] + [Ni_Au_Nickel_Thickness_8]) / 8

Dim Max, Min As Double
Max = Ni_Au_Nickel_Thickness_1
If Ni_Au_Nickel_Thickness_2 > Max Then Max = Ni_Au_Nickel_Thickness_2
If Ni_Au_Nickel_Thickness_3 > Max Then Max = Ni_Au_Nickel_Thickness_3
If Ni_Au_Nickel_Thickness_4 > Max Then Max = Ni_Au_Nickel_Thickness_4
If Ni_Au_Nickel_Thickness_5 > Max Then Max = Ni_Au_Nickel_Thickness_5
If Ni_Au_Nickel_Thickness_6 > Max Then Max = Ni_Au_Nickel_Thickness_6
If Ni_Au_Nickel_Thickness_7 > Max Then Max = Ni_Au_Nickel_Thickness_7
If Ni_Au_Nickel_Thickness_8 > Max Then Max = Ni_Au_Nickel_Thickness_8
If Ni_Au_Nickel_Thickness_1 > 0 Then Min = Ni_Au_Nickel_Thickness_1
If Ni_Au_Nickel_Thickness_2 < Min Then Min = Ni_Au_Nickel_Thickness_2
If Ni_Au_Nickel_Thickness_3 < Min Then Min = Ni_Au_Nickel_Thickness_3
If Ni_Au_Nickel_Thickness_4 < Min Then Min = Ni_Au_Nickel_Thickness_4
If Ni_Au_Nickel_Thickness_5 < Min Then Min = Ni_Au_Nickel_Thickness_5
If Ni_Au_Nickel_Thickness_6 < Min Then Min = Ni_Au_Nickel_Thickness_6
If Ni_Au_Nickel_Thickness_7 < Min Then Min = Ni_Au_Nickel_Thickness_7
If Ni_Au_Nickel_Thickness_8 < Min Then Min = Ni_Au_Nickel_Thickness_8
Me.[Ni_Au_Nickel_Thickness_Range] = Max - Min

Dim Lookup1, Lookup2, a, b As Variant
Dim i As Integer
'''NICKEL MOVING RANGE
'''COOPER
If Me.[cboPartNumber] = "156882-001" Then
Lookup1 = DMax("[ID]", "Ni-Au-CooperSubformQuery")
Lookup2 = DMax("[ID]", "Ni-Au-CooperSubformQuery") - 1
a = DLookup("[Ni-Au-Nickel Thickness Avg]", "Ni-Au-CooperSubformQuery", "[ID]=" & Lookup1)
b = DLookup("[Ni-Au-Nickel Thickness Avg]", "Ni-Au-CooperSubformQuery", "[ID]=" & Lookup2)
For i = 2 To 50
If IsNull(b) Then
Lookup2 = DMax("[ID]", "Ni-Au-CooperSubformQuery") - i
b = DLookup("[Ni-Au-Nickel Thickness Avg]", "Ni-Au-CooperSubformQuery", "[ID]=" & Lookup2)
Else
Me.[Ni_Au_Nickel_Thickness_Moving_Range] = Abs(a - b)
End If
Next i
End If

 

[lameid]

Just glancing at this, afterupdate fires after the data is written to the controls but before the record is saved.

Trying putting in the following line of code before you use any of the domain functions (i.e. Dmax)...

docmd.runcommand acCmdSaveRecord

 

[grgimpy]

the command you gave me worked perfectly. thanks for the help. i figured it would be a little more complicated than this.