Results 1 to 13 of 13
  1. #1
    sam10 is offline Novice
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    6

    Loop through records


    I am trying to create a loop that will isolate all the records with same ID (ie, 1), perform a task and then move on to the next set of records that have same ID (ie, 2). I would appreciate if anybody could tell me how to do it.
    Thank you.
    Sam

  2. #2
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows XP Access 2003
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    Hi

    I would create a query with criteria that returns all the required records. Create a recordset based on this query and loop thru the recordset until EOF is true. Carry out your "tasks" in each loop before moving on to the next record.

    What are the "tasks" that need to be carried out? Mayby this could all be done with an "Update Query" ?
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  3. #3
    sam10 is offline Novice
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    6
    Bob,

    Thanks for the quick response. I have attached a dummy database to explain what I am trying to do.

    1. Grab all the records with same id (say ID=1)
    2. sort "FromLeft" in ascending order,
    3. compare "FromLeft" value with "ToLeft" value of previous record
    4. if "FromLeft" value <= "ToLeft" value of previous record, export both records to a new table
    5. Once this is done for all the records with ID=1, move on to next set of records with same id, (say ID=3)

    Its even better if this can be done using query.

    Thank you.

    Sam

  4. #4
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows XP Access 2003
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    Hi
    3. compare "FromLeft" value with "ToLeft" value of previous record
    What will the "FromLeft" value of the first record be compared with
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  5. #5
    sam10 is offline Novice
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    6
    Is it possible to ignore or skip the first record of each set?

  6. #6
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows XP Access 2003
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    Hi

    I think that skipping the first record should be possible, as is the rest of your task, but it will involve some careful thought and code.
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  7. #7
    sam10 is offline Novice
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    6
    So, I'm guessing creating a query to do this is out of the question. I'm new to programming. Would you have time to help me figure this out?

    Thank you.

  8. #8
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows XP Access 2003
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    Hi sam10
    So, I'm guessing creating a query to do this is out of the question
    .
    IMHO that is true.

    Would you have time to help me figure this out?
    I'll have a try and see how I get on.
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  9. #9
    sam10 is offline Novice
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    6
    Bob,

    Have you had time to look into it? I didn't mean to bug you but I need to find the solution soon.

    Thank you.

    Samir

  10. #10
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows XP Access 2003
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    Hi

    I've made some good progress but not finished yet. I'm sorry to say that I've had other personal and work related issues that needed all my time since my last post. Hope to have a solution for you in the next 24hrs.
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  11. #11
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows XP Access 2003
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    Hi
    You may like to try this. Hopefully it will give the required results.
    The function listed below should be copied to a general module so that it can be called from anywhere in your db.
    Code:
     
    Public Function fnBobsSolution()
    On Error GoTo Err_fnBobsSolution_Error
    Dim Db As Database
    Dim recMultiIDs As DAO.Recordset
    Dim sqlRecMultiIDs As String
    Dim SqlAppend As String
    Dim lngRC As Long
    Dim lngID As Long
    Dim lngOBID As Long
    Dim recIDs As DAO.Recordset
    Dim sqlRecIDs As String
    Dim varFromLeft As Variant
        'Returns a list of IDs that are in the table more than once
        sqlRecMultiIDs = "SELECT Table1.ID, Count(Table1.ID) AS CountOfID " & _
            "FROM Table1 " & _
            "GROUP BY Table1.ID " & _
            "HAVING (((Count(Table1.ID)) > 1)) " & _
            "ORDER BY Table1.ID;"
        Set Db = CurrentDb
        Set recMultiIDs = Db.OpenRecordset(sqlRecMultiIDs)
        With recMultiIDs
            'Loop thru the multiple IDs
            Do Until .EOF
                lngID = .Fields("ID")
                'Returns all records from Table1 with the current ID
                sqlRecIDs = "SELECT Table1.OBID, Table1.ID, Table1.FromLeft, Table1.ToLeft " & _
                    "FROM Table1 " & _
                    "WHERE (((Table1.ID) = " & lngID & ")) " & _
                    "ORDER BY Table1.OBID;"
                Set recIDs = Db.OpenRecordset(sqlRecIDs)
                With recIDs
                .MoveLast
                lngRC = .RecordCount
                'Go to the last record. Set the value of varFromLeft to that of "FromLeft". Move to the previous rec.
                'Compare the value of varFromLeft to that of "ToLeft".
                For lngRC = lngRC To 1 Step -1
                    If Not IsEmpty(varFromLeft) And varFromLeft <= .Fields("ToLeft") Then
                        SqlAppend = "INSERT INTO Table1CopiedRecords ( OBID, ID, FromLeft, ToLeft, FromRight, ToRight, Flag ) " & _
                            "SELECT Table1.OBID, Table1.ID, Table1.FromLeft, Table1.ToLeft, Table1.FromRight, Table1.ToRight, Table1.Flag " & _
                            "FROM Table1 " & _
                            "WHERE (((Table1.OBID)=" & .Fields("OBID") & "));"
                        Db.Execute SqlAppend
     
                        SqlAppend = "INSERT INTO Table1CopiedRecords ( OBID, ID, FromLeft, ToLeft, FromRight, ToRight, Flag ) " & _
                            "SELECT Table1.OBID, Table1.ID, Table1.FromLeft, Table1.ToLeft, Table1.FromRight, Table1.ToRight, Table1.Flag " & _
                            "FROM Table1 " & _
                            "WHERE (((Table1.OBID)=" & lngOBID & "));"
                        Db.Execute SqlAppend
     
                    End If
                    varFromLeft = .Fields("FromLeft")
                    lngOBID = .Fields("OBID")
                    .MovePrevious
                Next
                End With
                varFromLeft = Empty
                lngOBID = 0
                .MoveNext 'Go to the next ID in list
            Loop
        End With
    Exit_ErrorHandler:
        'Clean Up
        Set Db = Nothing
        Set recMultiIDs = Nothing
        Set recIDs = Nothing
        Exit Function
    Err_fnBobsSolution_Error:
        MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure fnBobsSolution of Module Module1 at Line " & Erl
        Resume Exit_ErrorHandler
    End Function
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  12. #12
    sam10 is offline Novice
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    6
    Bob,

    It did what I exactly wanted it to do. So, I guess I would just have to copy and paste the codes to do the same to "FromRight" and "ToRight" fields and also create an empty table with same schema.

    Thank you very much.

    Sam

  13. #13
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows XP Access 2003
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    Hi Sam

    Glad it worked for you and sorry I had to keep you waiting so long.
    If you copy/paste the function to do the same for "FromRight" and "ToRight" fields, then you will need to go thru the code and replace "FromLeft" with "FromRight" and replace "ToLeft" with "ToRight". Don't forget to change the name of the second function.

    If you have any probs, post back.
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Error: Loop without Do
    By eric.opperman1@gmail.com in forum Programming
    Replies: 4
    Last Post: 01-25-2011, 02:37 PM
  2. Loop through subform records
    By asmith in forum Forms
    Replies: 6
    Last Post: 10-06-2010, 10:31 AM
  3. Do While loop
    By jgelpi16 in forum Programming
    Replies: 7
    Last Post: 07-23-2010, 08:21 AM
  4. VBA Loop
    By DreamOn in forum Programming
    Replies: 4
    Last Post: 06-25-2010, 03:35 AM
  5. Loop through Records and Make ID
    By rob4465 in forum Programming
    Replies: 3
    Last Post: 01-14-2010, 10:46 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums