Select records in sequential chronologial order

[maxamis4]

Hello folks,

I have two forms a parent form and a subform. The parent form is an unbound form while the subform is a form that contains all a list of what I like to call 'in stock ' phone numbers. with in that subform the user has the ability to select between to radio buttons to do a bulk selection. The first option lets the user select the first X amount of numbers that he or she wants. The second option selects X amount of numbers but they all must be in chronological order. If the numbers even skip a number it should return with a message no numbers avaiable in that order. Example
the txt box where a user enters the number of phone numbers he or she wants to select is called txt_LumpSelect

So a user enters 5 in text_Lumpselect and clicks on the grab button what it should select in the subform is the first 5 sequential numbers like so:
1234567
1234568
1234569
1234570
1234571


if it can not find any numbers that fit the required 5 in sequential order, an error message should be prompted that says no numbers in sequence.

Please note I don't need it to filter just to check the box check box that goes with the number. Filtering would just be an added bonous if someone can help with this process


How do I accomplish this feat
below is the selection process for the non sequential numbers which I quess whas the easier part. Any ideas,

Thanks
Code:
--------------------------------------------------------------------------------

Private Sub btn_LumpGrap_Click()
'Sets the status of the TN to in service as well as sets the Service Value to -1 to make sure it is checked off
mysql = "SELECT tbl_Phone_Arch.DID, tbl_Phone_Arch.Status, tbl_Phone_Arch.ServiceValue" _
& " FROM tbl_Phone_Arch" _
& " WHERE (((tbl_Phone_Arch.Status)='In Stock'))" _
& " ORDER BY tbl_Phone_Arch.DID"
Set db = CurrentDb()
Set rs = db.OpenRecordset(mysql, dbOpenDynaset)
 
 
If (Opt_Non) = -1 Then
        If IsNull(txt_LumpSelect) Then
            Exit Sub
        Else
        i = 0
            Do While i < Val(txt_LumpSelect)
                rs.Edit
                    rs!Status = "In Service"
                    rs!ServiceValue = -1
 
                rs.Update
            rs.MoveNext
            i = i + 1
            Loop
 
        End If
Else
    If (Opt_Seq) = -1 Then
 
        Do While i < Val(txt_LumpSelect)
 
 
 
 
            If rs.EOF Then
                MsgBox "Not enough numbers in sequence!"
                Exit Sub
            Else
                rs.MoveNext
            End If
        Loop
    Else
     MsgBox "Please select an assignement Method"
     Exit Sub
    End If
 
End If
Form.Refresh
 
End Sub

[MMcCarthy]

Try this ...

Private Sub btn_LumpGrap_Click()
'Sets the status of the TN to in service as well as sets the Service Value to -1 to make sure it is checked off
mysql = "SELECT tbl_Phone_Arch.DID, tbl_Phone_Arch.Status, tbl_Phone_Arch.ServiceValue" _
& " FROM tbl_Phone_Arch" _
& " WHERE (((tbl_Phone_Arch.Status)='In Stock'))" _
& " ORDER BY tbl_Phone_Arch.DID"
Set db = CurrentDb()
Set rs = db.OpenRecordset(mysql, dbOpenDynaset)
 
If (Opt_Non) = -1 Then
        If IsNull(txt_LumpSelect) Then
            Exit Sub
        Else
        i = 0
            Do While i < Val(txt_LumpSelect)
                rs.Edit
                    rs!Status = "In Service"
                    rs!ServiceValue = -1
 
                rs.Update
            rs.MoveNext
            i = i + 1
            Loop
 
        End If
ElseIf (Opt_Seq) = -1 Then
 
Dim tmpDID As Long
 
rs.MoveFirst
        Do While i < Val(txt_LumpSelect)
                tmpDID = rs!DID
    rs.MoveNext
    If i < Val(txt_LumpSelect) Then
       If rs!DID <> (tmpDID + 1)  Then
          Msgbox "No sequence", vbOkOnly
          Exit Sub
       End If
   End If
Loop
 
rs.MoveFirst
Do While i < Val(txt_LumpSelect)
                rs.Edit
                    rs!Status = "In Service"
                    rs!ServiceValue = -1
 
                rs.Update
            rs.MoveNext
            i = i + 1
            Loop
 
End If
Form.Refresh
 
End Sub

Mary