Here is the code that is set up for the form i am working with and it will stop the number series at the end of the series (0429999) but it will not stop the series in the middle (0421000) even if you tell it to on the table the code is pulling from. I would just assume rewrite the code to make it simpler (if possible) unless there is a quick fix here. Thanks for the help
Private Sub Button28_Click()
Dim Q As String
If (IsNull(Forms![7digitform]![series]) Or IsEmpty(Forms![7digitform]![series])) Then
MsgBox "You Must Enter A Series Value First", 48, "Error"
Else
Q = Forms![7digitform]![series]
Forms![7digitform]![ITEM] = NextNum7(Q)
DoCmd.GoToControl "[DESCRIPTION]"
End If
End Sub
Private Sub Form_BeforeUpdate(Cancel As Integer)
S = Forms![7digitform].[series]
I = Forms![7digitform].[ITEM]
If (IsNull(S) Or IsNull(I)) Then
MsgBox "Must Enter Series Number and/or Item Number", 48, "Error!"
Cancel = True
End If
LS = Len(S)
ItemS = Left(I, LS)
ValidNum = (Abs(StrComp(S, ItemS)) - 1)
If Not (ValidNum) Then
MsgBox "Item Number and Series Do not Match!", 48, "Error!"
Cancel = True
Exit Sub
End If
'Check to see that Item Number is not Less than Minimum Value for the Series
'If so, warn them to check the value is empty in MAPICS
CRITERIA = "[7-digit].SERIES = """ & S & """"
Smallest = DMin("[Item]", "[7-digit]", CRITERIA)
If (StrComp(I, Smallest) < 0) Then
MsgBox "You Have Entered an Item Number Smaller than the Beginning Record In this Database", 48, "Warning!"
msg = "Have You Checked to See that this Number is not Already Used in MAPICS"
Response = MsgBox(msg, 36, "Warning!")
End If
If Response = 7 Then ' The No box is checked
Cancel = True
End If
'Check to See that the Item Number is not In the BadNumbers Table
CRITERIA = "Item = """ & I & """"
BADYN = DLookup("Series", "BadNumbers", CRITERIA)
If Not (IsNull(BADYN)) Then
MsgBox "You Have Entered an Item Number which is already in MAPICS. Try using the next largest number.", 48, "Warning!"
Cancel = True
End If
End Sub
Private Function NextNum7(SERIESNUM As String)
'
Dim CRITERIA, biggest, almost As String
CRITERIA = "[7-digit].SERIES = """ & SERIESNUM & """"
biggest = DMax("[Item]", "7-digit", CRITERIA)
If (IsNull(biggest)) Then
CRITERIA = "[7-Digit Begin/End].[Series Number] = """ & SERIESNUM & """"
biggest = DLookup("Begin", "7-Digit Begin/End", CRITERIA)
'MsgBox biggest
End If
numchar = Len(biggest)
almost = Format$(Val(biggest) + 1)
dropped = numchar - Len(almost)
Select Case dropped
Case 0
NumValue = almost
Case 1
NumValue = "0" & almost
Case 2
NumValue = "00" & almost
Case Else
MsgBox "More than 2 leading zeros??"
NumValue = "9999999"
End Select
LS = Len(SERIESNUM)
ItemS = Left(NumValue, LS)
ValidNum = (Abs(StrComp(SERIESNUM, ItemS)) - 1)
If Not (ValidNum) Then
MsgBox "Series Has Reached Its Limit And May Be Full", 48, "Error!"
MsgBox "Unable to Automatically Assign a Number", , "Series May Be Full"
NextNum7 = "ERROR"
Else
NextNum7 = NumValue
End If
End Function
Private Sub Next_Click()
On Error GoTo Err_Next_Click
Screen.PreviousControl.SetFocus
DoCmd.FindNext
Exit_Next_Click:
Exit Sub
Err_Next_Click:
MsgBox Err.Description
Resume Exit_Next_Click
End Sub
