Looping through table records in Access

Posted on 2012-08-29
Last Modified: 2012-09-03
Can vba be used to loop through records in a table in Access in the same way it can in Excel?
Question by:JSSenior
    LVL 30

    Assisted Solution

    You can use VBA to create a recordset (rs)  from the table.

    Loop through recordset While Not (rs.EOF)

    Private Sub Command7_Click()
        Dim rs As Recordset
        Set rs = CurrentDb.OpenRecordset("a")
        Do While Not rs.EOF
        'insert code here
    End Sub

    Open in new window

    LVL 74

    Assisted Solution

    by:Jeffrey Coachman
    1. Sure, but for what reason?
    A lot of what people want to do with a recordset, can be done faster with SQL...

    2. Slight tweaks on hnasr's code:
        set rs=Nothing the end of the code

    LVL 48

    Assisted Solution

    by:Gustav Brock
    And skip the rs.MoveFirst. Not needed.


    Author Comment

    I could write the 200 queries but was thinking of just having one query with the variables in a table.
    LVL 30

    Expert Comment

    Thanks boag2000. Tend to overlook obvious things.

    "I could write the 200 queries but was thinking of just having one query with the variables in a table."
    Good Idea!
    If you you need help in that, upload a sample database with 2 or 3 similar queries showing the variations in each, if it is a field name or table name, see how the record set will help.
    LVL 31

    Accepted Solution

    You might be able to write code using two loops -- the outer one looping through a table of table names, query names, or whatever is relevant, and the inner one processing each table (or query, or whatever) in turn.
    LVL 74

    Expert Comment

    by:Jeffrey Coachman

    Are you still out there?

    As you can see, we need to know more of the details of what you are trying to do...
    LVL 74

    Expert Comment

    by:Jeffrey Coachman
    Still, you posted exactly what the asker wanted.
    So the logic behind recordset looping may be similar to what they were doing in Excel.
    So using a recordset may be the way to go...

    My suggestion of using SQL was just exploratory...
    ...and might confuse the issue... I'll step aside here.

    I am sure between you, gustav and Helen, you can get this going.



    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Top 6 Sources for Identifying Threat Actor TTPs

    Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

    Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
    In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
    In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now