help

[Cori]

I have two tables, one table has the general criteria (series number, description of the series, the begining number for the series and the end nubmer for the series) The second table stores the numbers once they have been pulled from the series.

The code is supposed to work where it pulls a number from a series consecutivly starting with the number that is specified in the beginning series # (1st table) and stop pulling from that series when it reaches the number in the ending series number (1st table)

My delima is the pulling of the numbers from the series does not stop when it hits that ending number (it's supposed to pop up an error message saying that series is full), it just keeps pulling past the specified end number.

Can anyone help me???
here is the code that is currently in place:

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

 

[Cori]

Pat
I wish this helped me, but i am not sure what it means. I am new to this code thing and I didn't write this specific code. Every time i try to change small things in it, it doesn't work any more. thanks for trying, but i am not sure what to do with the info you gave me.

 

[Cori]

No, that doesn't work. I wish i knew more about this stuff becuase i would just rewrite this code to make it work better. thanks for the help and if you think of something else feel free to let me know