Beginning/End Series

[Cori]

I have a database that was put together long before I took it over. After up dating some of the data I have found that it is not working as you would think it should. It is set up to start a series of numbers and end when it gets to a certain number. If you put in 1001 in the beginning field it will start with 1001 but it won't end the series where you tell it to end unless it is 1999. Is there an If Then statement I can type into the code to make it End at 1015 if i want it to???


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