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

Increment a column when column value changes

P: 1
I am new to vba access and I am trying to create a field that will increment when the another field value changes. Any assistance will be welcomed. This is what I have so far.
Expand|Select|Wrap|Line Numbers
  1. Dim i As Integer
  2. Dim n As Integer
  3. Dim db As Database
  4. Dim rs As Recordset
  5. Dim rsOne As String, rsTwo As String
  6.  
  7.  
  8. Set db = CurrentDb
  9. Set rs = db.OpenRecordset("qrySortData", dbOpenDynaset)
  10.  
  11.  
  12. rs.MoveFirst
  13.  
  14. n = 1
  15. Do While (Not rs.EOF)
  16.  
  17.     rsOne = rs.Fields("Folder")
  18.     rs.MoveNext
  19.     rsTwo = rs.Fields("Folder")
  20.     If rsOne = rsTwo Then
  21.  
  22.  
  23.         rs.Edit
  24.         rs.Fields!Counter = n
  25.         rs.Update
  26.         rs.MoveNext
  27.         rs.Edit
  28.         rs.Fields!Counter = n
  29.         rs.Edit
  30.  
  31.         rs.Update
  32.         rs.MoveNext
  33.     Else
  34.  
  35.         i = n + 1
  36.         rs.Edit
  37.         rs.Fields!Counter = i
  38.         rs.Update
  39.         rs.MoveNext
  40.         rs.Edit
  41.         rs.Fields!Counter = i
  42.         rs.Update
  43.         rs.MoveNext
  44.  
  45.  
  46.  
  47.     End If
  48. Loop
  49.  
  50.  
  51.  
  52. rs.Close
  53. Set rs = Nothing
  54. db.Close
  55. End Sub
Apr 6 '17 #1
Share this question for a faster answer!
Share on Google+

Post your reply

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