By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,453 Members | 3,194 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,453 IT Pros & Developers. It's quick & easy.

Select records in sequential chronologial order

maxamis4
Expert 100+
P: 295
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:
--------------------------------------------------------------------------------
Expand|Select|Wrap|Line Numbers
  1. Private Sub btn_LumpGrap_Click()
  2. '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
  3. mysql = "SELECT tbl_Phone_Arch.DID, tbl_Phone_Arch.Status, tbl_Phone_Arch.ServiceValue" _
  4. & " FROM tbl_Phone_Arch" _
  5. & " WHERE (((tbl_Phone_Arch.Status)='In Stock'))" _
  6. & " ORDER BY tbl_Phone_Arch.DID"
  7. Set db = CurrentDb()
  8. Set rs = db.OpenRecordset(mysql, dbOpenDynaset)
  9.  
  10.  
  11. If (Opt_Non) = -1 Then
  12.         If IsNull(txt_LumpSelect) Then
  13.             Exit Sub
  14.         Else
  15.         i = 0
  16.             Do While i < Val(txt_LumpSelect)
  17.                 rs.Edit
  18.                     rs!Status = "In Service"
  19.                     rs!ServiceValue = -1
  20.  
  21.                 rs.Update
  22.             rs.MoveNext
  23.             i = i + 1
  24.             Loop
  25.  
  26.         End If
  27. Else
  28.     If (Opt_Seq) = -1 Then
  29.  
  30.         Do While i < Val(txt_LumpSelect)
  31.  
  32.  
  33.  
  34.  
  35.             If rs.EOF Then
  36.                 MsgBox "Not enough numbers in sequence!"
  37.                 Exit Sub
  38.             Else
  39.                 rs.MoveNext
  40.             End If
  41.         Loop
  42.     Else
  43.      MsgBox "Please select an assignement Method"
  44.      Exit Sub
  45.     End If
  46.  
  47. End If
  48. Form.Refresh
  49.  
  50. End Sub
Apr 6 '07 #1
Share this Question
Share on Google+
1 Reply


MMcCarthy
Expert Mod 10K+
P: 14,534
Try this ...
Expand|Select|Wrap|Line Numbers
  1. Private Sub btn_LumpGrap_Click()
  2. '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
  3. mysql = "SELECT tbl_Phone_Arch.DID, tbl_Phone_Arch.Status, tbl_Phone_Arch.ServiceValue" _
  4. & " FROM tbl_Phone_Arch" _
  5. & " WHERE (((tbl_Phone_Arch.Status)='In Stock'))" _
  6. & " ORDER BY tbl_Phone_Arch.DID"
  7. Set db = CurrentDb()
  8. Set rs = db.OpenRecordset(mysql, dbOpenDynaset)
  9.  
  10. If (Opt_Non) = -1 Then
  11.         If IsNull(txt_LumpSelect) Then
  12.             Exit Sub
  13.         Else
  14.         i = 0
  15.             Do While i < Val(txt_LumpSelect)
  16.                 rs.Edit
  17.                     rs!Status = "In Service"
  18.                     rs!ServiceValue = -1
  19.  
  20.                 rs.Update
  21.             rs.MoveNext
  22.             i = i + 1
  23.             Loop
  24.  
  25.         End If
  26. ElseIf (Opt_Seq) = -1 Then
  27.  
  28. Dim tmpDID As Long
  29.  
  30. rs.MoveFirst
  31.         Do While i < Val(txt_LumpSelect)
  32.                 tmpDID = rs!DID
  33.     rs.MoveNext
  34.     If i < Val(txt_LumpSelect) Then
  35.        If rs!DID <> (tmpDID + 1)  Then
  36.           Msgbox "No sequence", vbOkOnly
  37.           Exit Sub
  38.        End If
  39.    End If
  40. Loop
  41.  
  42. rs.MoveFirst
  43. Do While i < Val(txt_LumpSelect)
  44.                 rs.Edit
  45.                     rs!Status = "In Service"
  46.                     rs!ServiceValue = -1
  47.  
  48.                 rs.Update
  49.             rs.MoveNext
  50.             i = i + 1
  51.             Loop
  52.  
  53. End If
  54. Form.Refresh
  55.  
  56. End Sub
Mary
Apr 11 '07 #2

Post your reply

Sign in to post your reply or Sign up for a free account.