Hi Guys
I have been battling with a type 13 (mismatch error) and I can't seem to resolve this,
the code that is making the error is highlighted below and runs on a textboxes after update event
Code:
Dim PartNumberString As String
PartNumberString = Nz(DLookup("ITEMNUMBER", "UNEXData", "WO= " & Forms!SOIAddDetails!WorkOrderTXTBox), "Work Order Number Not Found")
Dim PartNumberDescriptionString As String
PartNumberDescriptionString = Nz(DLookup("Description", "UNEXData", "WO=" & Forms!SOIAddDetails!WorkOrderTXTBox), "Work Order Number Not Found")
Dim QTYString As Single
QTYString = Nz(DLookup("QTYOUTST", "UNEXData", "WO=" & Forms!SOIAddDetails!WorkOrderTXTBox), 0)
Me.PARTNUMBERTXTBOX = PartNumberString
Me.PartNumberDescriptionTXTBox = PartNumberDescriptionString
Me.QtyTXTBox = QTYString
'then we work out if the part number has a "*" in it
Dim LPosition As Integer
LPosition = InStr(Forms!SOIAddDetails.Form.PartNumberDescriptionTXTBox, "*")
If LPosition > 0 Then
Dim NumberPosition As Integer
NumberPosition = Nz(LPosition - 1, 0)
'this code looks at the charecter to the left of the * and this number is used for more calculations later
Dim YourCharacter As String
YourCharacter = Mid(Me.PartNumberDescriptionTXTBox, NumberPosition, 1)
Else
MsgBox "THIS IS A UNI JOB"
Me.Uni = True
Me.Ends7 = False
Me.Ends19 = False
Me.Ends37 = False
Exit Sub
End If
'this code counts the soiid in the soidetails table that match the workorder on the form
Dim CountID As Variant
CountID = DCount("SOIID", "SOIDetails", "WorkOrderID='" & Forms!SOIAddDetails!WorkOrderTXTBox & "'")
'This Code Works Out If It Has 7 Ends
If CountID = 0 And LPosition > 0 Then
Me.Uni = False
Me.Ends7 = True
Me.Ends19 = False
Me.Ends37 = False
MsgBox "CountID Is " & CountID & " And Posituion Is " & LPosition
MsgBox "THIS HAS 7 ENDS"
Me.NUMBEROFENDSTXTBOX = 7
Me.WeightPerReelTXT = Nz(DLast("WeightPerReel", "SOIDetails", "Partnumber='" & Forms!SOIAddDetails!PARTNUMBERTXTBOX & "'" And Ends7 = True), 0)
Exit Sub
End If
'This Code Works Out If It Has 19 Ends
If CountID = 1 And LPosition > 0 Then
MsgBox "CountID Is " & CountID & " And Posituion Is " & LPosition
MsgBox "THIS HAS 19 ENDS"
Me.Ends19 = True
Me.NUMBEROFENDSTXTBOX = 19
Me.KingWireTension.Visible = True
DoCmd.GoToControl "KingWireTension"
Me.SINGLEENDDIAMETERTXTBOX = 0
Me.ActualDieSizeTXTBox = 0
Me.ACTUALLAYLENGHTTXTBOX = 0
Me.SPECIFIEDLAYLENGHTTXTBOX = 0
Me.ActualGramsPerMeterTXTBox = 0
Me.RequiredLenghtPerReelTXTBox = 0
Me.Ends7 = False
Me.Uni = False
Exit Sub
End If
'This Code Works Out If It Has 37 Ends
If CountID = 2 And LPosition > 0 Then
MsgBox "CountID Is " & CountID & " And Posituion Is " & LPosition
MsgBox "THIS HAS 37 ENDS"
Me.Ends37 = True
Me.NUMBEROFENDSTXTBOX = 37
Me.KingWireTension.Visible = True
DoCmd.GoToControl "KingWireTension"
Me.SINGLEENDDIAMETERTXTBOX = 0
Me.ActualDieSizeTXTBox = 0
Me.ACTUALLAYLENGHTTXTBOX = 0
Me.SPECIFIEDLAYLENGHTTXTBOX = 0
Me.ActualGramsPerMeterTXTBox = 0
Me.RequiredLenghtPerReelTXTBox = 0
Me.Uni = False
Me.Ends7 = False
Me.Ends19 = False
Exit Sub
End If
Ends7 is a tick box when I remove this part of the code the after update event runs correctly, but when I add it I get the type 13 error
what I an trying to do is get the last value of the WeightPerReel when ends7 tick box was ticked, based on the part number entered in the PARTNUMBERTXTBOX if its never been entered the weight should be zero
I cant see what I have done wrong
any help would be fantastic
I'm also sure that all this would be better suited in a select statement but that's another problem for me all together
kind regards
steve