Results 1 to 8 of 8
  1. #1
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Silicon Valley, California, USA
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Loop through fields (Access 2000)

    Hi Everybody/Anybody,

    I need to write some code that will loop through every record value in a table for a certain field. I am very familiar with the Excel object model, but not with the Access object model. Can you give me some starting points? Thanks, in advance!

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Loop through fields (Access 2000)

    Here a simple code to print the field txtContactNaam form table tblContact for all the records to the immediate window.
    <pre>Function LoopRecords()
    Dim db As DAO.Database
    Dim rst As DAO.Recordset
    Set db = CurrentDb
    Set rst = db.OpenRecordset("tblcontact", dbOpenDynaset)
    rst.MoveFirst
    Do While Not rst.EOF
    Debug.Print rst!txtContactNaam
    rst.MoveNext
    Loop
    Set rst = Nothing
    Set db = Nothing
    End Function</pre>

    If you want to modify the data use :
    <pre>Function LoopRecords()
    Dim db As DAO.Database
    Dim rst As DAO.Recordset
    Set db = CurrentDb
    Set rst = db.OpenRecordset("tblcontact", dbOpenDynaset)
    rst.MoveFirst
    Do While Not rst.EOF
    rst.Edit
    'here some code to modify the data
    rst.Update
    rst.MoveNext
    Loop
    Set rst = Nothing
    Set db = Nothing
    End Function</pre>

    Francois

  3. #3
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Loop through fields (Access 2000)

    If you try Francois's code, be sure you set a reference to the DAO 3.6 object library, which is not the default in Access 2000. Otherwise, your code won't compile.
    Charlotte

  4. #4
    Gold Lounger Rebel's Avatar
    Join Date
    Jul 2001
    Location
    Canada
    Posts
    3,024
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Loop through fields (Access 2000)

    Charlotte,
    Is there a way to code this function (Francois' LoopRecords) in Access 2000 without using DAO?

    John
    John
    A Child's Mind, Once Stretched by Imagination...
    Never Regains Its Original Dimensions

  5. #5
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Loop through fields (Access 2000)

    Actually there are all kinds of ways to do it with ADO, ranging from the simple to the complex. How you do it depends on what you want to do. ADO doesn't have a single "right" way to do it. The most basic (but not necessarily the best) way to do it is like this:

    <pre>Public Function LoopRecordsADO()
    Dim cnn As ADODB.Connection
    Dim rst As ADODB.Recordset

    Set cnn = CurrentProject.Connection
    Set rst = New ADODB.Recordset

    rst.Open "tblContact", cnn, adOpenDynamic, adLockOptimistic
    Do Until rst.EOF
    Debug.Print rst!ContactName
    rst.MoveNext
    Loop

    rst.Close
    Set rst = Nothing
    cnn.Close
    End Function</pre>

    Charlotte

  6. #6
    Gold Lounger Rebel's Avatar
    Join Date
    Jul 2001
    Location
    Canada
    Posts
    3,024
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Loop through fields (Access 2000)

    Tnaks Charlotte.

    John
    John
    A Child's Mind, Once Stretched by Imagination...
    Never Regains Its Original Dimensions

  7. #7
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Loop through fields (Access 2000)

    Hi Francois

    What code would you need to add to sort by contact name (alpha) and print total records read/printed?

    Thanks, John

  8. #8
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Loop through fields (Access 2000)

    It depend on what you want to do
    <pre>Function TotalRecords()
    Dim db As DAO.Database
    Dim rst As DAO.Recordset
    Dim lngTotal as Long
    Set db = CurrentDb
    Set rst = db.OpenRecordset("Select * From tblContact Order By ContactName", dbOpenDynaset)
    rst.MoveFirst
    Do While Not rst.EOF
    lngTotal = lngTotal + rst!MyFieldToTotal
    rst.MoveNext
    Loop
    Set rst = Nothing
    Set db = Nothing
    TotalRecords = lngTotal
    End Function</pre>

    But I wonder why you want to sort it for totaling.
    To total just a field of a table you could also use :
    DSum("MyFieldToTotal","tblContact")
    Francois

Posting Permissions

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