Results 1 to 2 of 2
  1. #1
    Join Date
    Apr 2012
    Posts
    22

    Unanswered: Incremental Number VBA Question

    I currently have the following code and using Access 2007:

    Dim Number1 As Integer
    Dim Number2 As Integer
    Dim IncNum, i As Integer
    Dim db As DAO.Database
    Dim rs As DAO.Recordset

    Number1 = 10
    Number2 = Forms!ImportDToBForm![Fox Line Num] + 10

    For i = Number1 To Number2

    Set db = CurrentDb
    Set rs = db.OpenRecordset("ImportDealToBidTable")

    rs.MoveFirst
    Do Until rs.EOF
    If rs![Prod ID Num] <> "" Then
    rs.Edit
    rs("Fox Line Num") = IncNum + Number2
    rs.Update
    End If
    rs.MoveNext
    Loop

    Next

    DoCmd****nCommand acCmdRecordsGoToLast

    (For some reason the . Run keeps going to **** on this forum dunno why)

    But this code isn't doing what I want it to do all this does is updates the last number that I was working on for example 2000 and updates it in the 'ImportDealToBidTable' table for each record (Fox Line Num) as 2000 instead of incrementally by 10 each time. So what it's suppose to do is place in the (Fox Line Num) row 2000 for the top record then for each additional record add 10. So the next record would be 2010, 2020, 2030 etc until it reaches the end of the records. Any ideas on what I am doing wrong? And an example of my table is below:

    What my code is doing:
    ImportDealToBidTable
    Prod ID Num / Fox Line Num
    25 - 2000
    50 - 2000
    19 - 2000
    88 - 2000

    What my code is supposed to do:
    ImportDealToBidTable
    Prod ID Num / Fox Line Num
    25 - 2000
    50 - 2010
    19 - 2020
    88 - 2030

  2. #2
    Join Date
    Apr 2012
    Posts
    22
    I ended up finding my own solution after another hour or more of trying finally! lol
    Dim x As Integer
    Dim db As DAO.Database
    Dim rs As DAO.Recordset

    x = Forms!ImportDToBForm![Fox Line Num]

    Set db = CurrentDb
    Set rs = db.OpenRecordset("ImportDealToBidTable")
    'Goes through the ImportDealToBidTable and with the highest bid number add 10 until
    'all the records have been gone through
    rs.MoveFirst
    Do Until rs.EOF
    rs.Edit
    x = x + 10
    rs("Fox Line Num") = x
    rs.Update
    rs.MoveNext
    Loop

    Set rs = Nothing

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •