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(Cance l As Integer)
On Error GoTo Form_Open_Err
Me.Refresh
DoCmd.Maximize
DoCmd.GoToRecor d acForm, "Ni-Au - Main Page", acNewRec
Me.cboPartNumbe r.SetFocus
Me.Previous_Rec ord_Button.Enab led = True
Me.Next_Record. Enabled = False
Me.AddRecord.En abled = True
Me.Ni_Au_Cooper SubformQuery_su bform.Visible = False
Me.Ni_Au_XeroxS ubformQuery_sub form.Visible = False
Me.Ni_Au_OSRAMS ubFormQuery_sub form.Visible = False
Me.Ni_Au_CyndiS ubformQuery_sub form.Visible = False
Me.Ni_Au_Valeo7 5SubformQuery_s ubform.Visible = False
Me.Ni_Au_Valeo7 9SubformQuery_s ubform.Visible = False
Me.NiUCL.Captio n = ""
Me.NiLCL.Captio n = ""
Me.AuLCL.Captio n = ""
Me.AuUCL.Captio n = ""
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_A fterUpdate()
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.Captio n = NiAve - 3 * NiSigma
Me.NiUCL.Captio n = 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.Captio n = AuAve - 3 * AuSigma
Me.AuUCL.Captio n = AuAve + 3 * AuSigma
Me.Ni_Au_Cooper SubformQuery_su bform.Form.Reco rdset.MoveLast
Me.Ni_Au_Cooper SubformQuery_su bform.Visible = True
Me.Ni_Au_XeroxS ubformQuery_sub form.Visible = False
Me.Ni_Au_OSRAMS ubFormQuery_sub form.Visible = False
Me.Ni_Au_CyndiS ubformQuery_sub form.Visible = False
Me.Ni_Au_Valeo7 5SubformQuery_s ubform.Visible = False
Me.Ni_Au_Valeo7 9SubformQuery_s ubform.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_G old_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_Th ickness_8_After Update()
Me.[Ni_Au_Nickel_Th ickness_Avg] = ([Ni_Au_Nickel_Th ickness_1] + [Ni_Au_Nickel_Th ickness_2] + [Ni_Au_Nickel_Th ickness_3] + [Ni_Au_Nickel_Th ickness_4] + [Ni_Au_Nickel_Th ickness_5] + [Ni_Au_Nickel_Th ickness_6] + [Ni_Au_Nickel_Th ickness_7] + [Ni_Au_Nickel_Th ickness_8]) / 8
Dim Max, Min As Double
Max = Ni_Au_Nickel_Th ickness_1
If Ni_Au_Nickel_Th ickness_2 > Max Then Max = Ni_Au_Nickel_Th ickness_2
If Ni_Au_Nickel_Th ickness_3 > Max Then Max = Ni_Au_Nickel_Th ickness_3
If Ni_Au_Nickel_Th ickness_4 > Max Then Max = Ni_Au_Nickel_Th ickness_4
If Ni_Au_Nickel_Th ickness_5 > Max Then Max = Ni_Au_Nickel_Th ickness_5
If Ni_Au_Nickel_Th ickness_6 > Max Then Max = Ni_Au_Nickel_Th ickness_6
If Ni_Au_Nickel_Th ickness_7 > Max Then Max = Ni_Au_Nickel_Th ickness_7
If Ni_Au_Nickel_Th ickness_8 > Max Then Max = Ni_Au_Nickel_Th ickness_8
If Ni_Au_Nickel_Th ickness_1 > 0 Then Min = Ni_Au_Nickel_Th ickness_1
If Ni_Au_Nickel_Th ickness_2 < Min Then Min = Ni_Au_Nickel_Th ickness_2
If Ni_Au_Nickel_Th ickness_3 < Min Then Min = Ni_Au_Nickel_Th ickness_3
If Ni_Au_Nickel_Th ickness_4 < Min Then Min = Ni_Au_Nickel_Th ickness_4
If Ni_Au_Nickel_Th ickness_5 < Min Then Min = Ni_Au_Nickel_Th ickness_5
If Ni_Au_Nickel_Th ickness_6 < Min Then Min = Ni_Au_Nickel_Th ickness_6
If Ni_Au_Nickel_Th ickness_7 < Min Then Min = Ni_Au_Nickel_Th ickness_7
If Ni_Au_Nickel_Th ickness_8 < Min Then Min = Ni_Au_Nickel_Th ickness_8
Me.[Ni_Au_Nickel_Th ickness_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-CooperSubformQu ery")
Lookup2 = DMax("[ID]", "Ni-Au-CooperSubformQu ery") - 1
a = DLookup("[Ni-Au-Nickel Thickness Avg]", "Ni-Au-CooperSubformQu ery", "[ID]=" & Lookup1)
b = DLookup("[Ni-Au-Nickel Thickness Avg]", "Ni-Au-CooperSubformQu ery", "[ID]=" & Lookup2)
For i = 2 To 50
If IsNull(b) Then
Lookup2 = DMax("[ID]", "Ni-Au-CooperSubformQu ery") - i
b = DLookup("[Ni-Au-Nickel Thickness Avg]", "Ni-Au-CooperSubformQu ery", "[ID]=" & Lookup2)
Else
Me.[Ni_Au_Nickel_Th ickness_Moving_ Range] = Abs(a - b)
End If
Next i
End If
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(Cance l As Integer)
On Error GoTo Form_Open_Err
Me.Refresh
DoCmd.Maximize
DoCmd.GoToRecor d acForm, "Ni-Au - Main Page", acNewRec
Me.cboPartNumbe r.SetFocus
Me.Previous_Rec ord_Button.Enab led = True
Me.Next_Record. Enabled = False
Me.AddRecord.En abled = True
Me.Ni_Au_Cooper SubformQuery_su bform.Visible = False
Me.Ni_Au_XeroxS ubformQuery_sub form.Visible = False
Me.Ni_Au_OSRAMS ubFormQuery_sub form.Visible = False
Me.Ni_Au_CyndiS ubformQuery_sub form.Visible = False
Me.Ni_Au_Valeo7 5SubformQuery_s ubform.Visible = False
Me.Ni_Au_Valeo7 9SubformQuery_s ubform.Visible = False
Me.NiUCL.Captio n = ""
Me.NiLCL.Captio n = ""
Me.AuLCL.Captio n = ""
Me.AuUCL.Captio n = ""
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_A fterUpdate()
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.Captio n = NiAve - 3 * NiSigma
Me.NiUCL.Captio n = 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.Captio n = AuAve - 3 * AuSigma
Me.AuUCL.Captio n = AuAve + 3 * AuSigma
Me.Ni_Au_Cooper SubformQuery_su bform.Form.Reco rdset.MoveLast
Me.Ni_Au_Cooper SubformQuery_su bform.Visible = True
Me.Ni_Au_XeroxS ubformQuery_sub form.Visible = False
Me.Ni_Au_OSRAMS ubFormQuery_sub form.Visible = False
Me.Ni_Au_CyndiS ubformQuery_sub form.Visible = False
Me.Ni_Au_Valeo7 5SubformQuery_s ubform.Visible = False
Me.Ni_Au_Valeo7 9SubformQuery_s ubform.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_G old_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_Th ickness_8_After Update()
Me.[Ni_Au_Nickel_Th ickness_Avg] = ([Ni_Au_Nickel_Th ickness_1] + [Ni_Au_Nickel_Th ickness_2] + [Ni_Au_Nickel_Th ickness_3] + [Ni_Au_Nickel_Th ickness_4] + [Ni_Au_Nickel_Th ickness_5] + [Ni_Au_Nickel_Th ickness_6] + [Ni_Au_Nickel_Th ickness_7] + [Ni_Au_Nickel_Th ickness_8]) / 8
Dim Max, Min As Double
Max = Ni_Au_Nickel_Th ickness_1
If Ni_Au_Nickel_Th ickness_2 > Max Then Max = Ni_Au_Nickel_Th ickness_2
If Ni_Au_Nickel_Th ickness_3 > Max Then Max = Ni_Au_Nickel_Th ickness_3
If Ni_Au_Nickel_Th ickness_4 > Max Then Max = Ni_Au_Nickel_Th ickness_4
If Ni_Au_Nickel_Th ickness_5 > Max Then Max = Ni_Au_Nickel_Th ickness_5
If Ni_Au_Nickel_Th ickness_6 > Max Then Max = Ni_Au_Nickel_Th ickness_6
If Ni_Au_Nickel_Th ickness_7 > Max Then Max = Ni_Au_Nickel_Th ickness_7
If Ni_Au_Nickel_Th ickness_8 > Max Then Max = Ni_Au_Nickel_Th ickness_8
If Ni_Au_Nickel_Th ickness_1 > 0 Then Min = Ni_Au_Nickel_Th ickness_1
If Ni_Au_Nickel_Th ickness_2 < Min Then Min = Ni_Au_Nickel_Th ickness_2
If Ni_Au_Nickel_Th ickness_3 < Min Then Min = Ni_Au_Nickel_Th ickness_3
If Ni_Au_Nickel_Th ickness_4 < Min Then Min = Ni_Au_Nickel_Th ickness_4
If Ni_Au_Nickel_Th ickness_5 < Min Then Min = Ni_Au_Nickel_Th ickness_5
If Ni_Au_Nickel_Th ickness_6 < Min Then Min = Ni_Au_Nickel_Th ickness_6
If Ni_Au_Nickel_Th ickness_7 < Min Then Min = Ni_Au_Nickel_Th ickness_7
If Ni_Au_Nickel_Th ickness_8 < Min Then Min = Ni_Au_Nickel_Th ickness_8
Me.[Ni_Au_Nickel_Th ickness_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-CooperSubformQu ery")
Lookup2 = DMax("[ID]", "Ni-Au-CooperSubformQu ery") - 1
a = DLookup("[Ni-Au-Nickel Thickness Avg]", "Ni-Au-CooperSubformQu ery", "[ID]=" & Lookup1)
b = DLookup("[Ni-Au-Nickel Thickness Avg]", "Ni-Au-CooperSubformQu ery", "[ID]=" & Lookup2)
For i = 2 To 50
If IsNull(b) Then
Lookup2 = DMax("[ID]", "Ni-Au-CooperSubformQu ery") - i
b = DLookup("[Ni-Au-Nickel Thickness Avg]", "Ni-Au-CooperSubformQu ery", "[ID]=" & Lookup2)
Else
Me.[Ni_Au_Nickel_Th ickness_Moving_ Range] = Abs(a - b)
End If
Next i
End If