Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2002
    Posts
    20

    Question Unanswered: Looping thru Records and Incrementing

    I need to know how to do something like this:
    Imagine I have a table of pets and their respective names:

    Fields: [Pet_Type], [Pet_Name], [Pet_Owner]

    so we would have:
    Dog, Scooby, Shaggy
    Duck, Daffy, Elmer
    Bunny, Bugs, Warner
    Bunny, Easter, Demi
    Bunny, Easter, Bruce
    Bunny, Roger, Bill_Gates

    imagine i had to link all this data together in this way
    001, 01, Shaggy
    001, 01, Elmer
    001, 01, Warner
    002, 01, Demi
    002, 02, Bruce
    003, 01, Bill_Gates

    For every new animal type, it is 001. The second field is incremented for the same animal type, but different animal name (or different pet). Easter Bunny is owned by Bruce Willis and Demi Moore, listing him twice, so the second column is incremented. Kinda hard to explain but I guess you see the pattern.

    Now if access had a java integration I would be a happy camper. But I know very little VB. Any help would be greatly appreciated.

  2. #2
    Join Date
    Oct 2001
    Location
    Chicago
    Posts
    440
    Not sure what the output is for - report, form, export..?

    A report or an export could be re-formatted on the fly.

    For a form - I would probably use a copy of the original table and update the fields as shown below.

    This sub will loop through the recordset of your copied table and increment the values as you specified. (Should Bruce be 003 and Bill be 004 in your example?). I used temp variables to hold the names and increment counters.


    Private Sub cmdConvertTable_Click()
    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Dim strSQL As String
    Dim strPet_Type_Temp As String
    Dim strPet_Name_Temp As String
    Dim intTypeCounter As Integer
    Dim intNameCounter As Integer

    'this sql is used to reset my [Table] from a backup for testing purposes
    strSQL = "UPDATE [Table] INNER JOIN Tablebk ON Table.ID = " & _
    "Tablebk.ID SET [Table].Pet_Type = [Tablebk]!" & _
    "[Pet_Type], [Table].Pet_Name = [Tablebk]!" & _
    "[Pet_Name];"
    DoCmd.RunSQL strSQL

    Set dbs = CurrentDb
    strSQL = "SELECT * FROM [Table]"
    Set rst = dbs.OpenRecordset(strSQL)
    strPet_Type_Temp = rst.Fields("Pet_Type")
    strPet_Name_Temp = rst.Fields("Pet_Owner")
    Do While Not rst.EOF
    rst.Edit
    If rst.Fields("Pet_Type") = strPet_Type_Temp Then
    intTypeCounter = intTypeCounter + 1
    rst.Fields("Pet_Type") = "00" & CStr(intTypeCounter)
    Else
    intTypeCounter = 1
    strPet_Type_Temp = rst.Fields("Pet_Type")
    rst.Fields("Pet_Type") = "00" & CStr(intTypeCounter)
    End If
    If rst.Fields("Pet_Name") = strPet_Name_Temp Then
    intNameCounter = intNameCounter + 1
    rst.Fields("Pet_Name") = "0" & CStr(intNameCounter)
    Else
    intNameCounter = 1
    strPet_Name_Temp = rst.Fields("Pet_Name")
    rst.Fields("Pet_Name") = "0" & CStr(intNameCounter)
    End If
    rst.Update
    Debug.Print rst.Fields("Pet_Type") & vbTab & rst.Fields("Pet_Name") & _
    vbTab & rst.Fields("Pet_Owner")
    rst.MoveNext
    Loop

    rst.Close
    Set rst = Nothing
    dbs.Close
    Set dbs = Nothing

    End Sub

    Output:
    001 01 Shaggy
    001 01 Elmer
    001 01 Warner
    002 01 Demi
    003 02 Bruce
    004 01 Bill_Gates
    Last edited by Rockey; 10-24-02 at 22:22.

  3. #3
    Join Date
    Jul 2002
    Posts
    20
    Thanx, I will try it out tomorrow.

    oops I see you editted it to show the output. I will recheck to see if my output description is wrong and yours is right. Thanx for the help again

Posting Permissions

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