Results 1 to 7 of 7
  1. #1
    2 Star Lounger
    Join Date
    Feb 2003
    Location
    Melbourne, Victoria, Australia
    Posts
    139
    Thanks
    0
    Thanked 0 Times in 0 Posts

    looping through fields (Access 2000 SR-1)

    How can I, in VBA, loop through all fields/columns in a table or query
    and display the name of the field as well as the contents?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: looping through fields (Access 2000 SR-1)

    Here is some code that uses DAO; you need to set a reference to the Microsoft DAO 3.6 Object Library in Tools/References... (in the Visual Basic Editor) to use it.

    Sub ListFields()
    Dim dbs As DAO.Database
    Dim tdf As DAO.TableDef ' use this for a table
    Dim qdf As DAO.QueryDef 'use this for a query
    Dim rst As DAO.Recordset
    Dim fld As DAO.Field

    Set dbs = CurrentDb
    Set tdf = dbs.TableDefs("tblWhatever")

    ' List fields
    For Each fld In tdf.Fields
    Debug.Print fld.Name
    Next fld

    Set rst = tdf.OpenRecordset

    ' Loop through records
    Do Until rst.EOF
    For Each fld In rst.Fields
    Debug.Print fld.Name & "=" & fld.Value
    Next fld
    rst.MoveNext
    Loop

    rst.Close
    Set rst = Nothing
    Set fld = Nothing
    Set qdf = Nothing
    Set tdf = Nothing
    Set dbs = Nothing
    End Sub

  3. #3
    2 Star Lounger
    Join Date
    Feb 2003
    Location
    Melbourne, Victoria, Australia
    Posts
    139
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: looping through fields (Access 2000 SR-1)

    Thank you Hans,

    It does work fine.

    I have inherited a denomalised database where the column/fields are named after the twelve months etc. Jan, Feb, - Nov, Dec and other data is stored in column/fields like Account, Cost Centre etc.

    What I want to do is to leave the original table intact but via code to create another, normalised, table.

    My need for this is to loop through the field names and dependent on the name and contents to create a new
    record. Then store the denormalised table's field name as a string and the value for that record as a value.

    Eg.

    Denormalised contains
    CostCentre,Jan,Feb
    9999,10%,20%
    8888,25%,10%

    Normalised should be
    CostCentre, Month, Percent
    9999,Jan,10%
    9999,Feb,20%
    8888,Jan,25%
    8888,Feb,20%

    Have you got any suggestions?

    Regards
    Michael

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: looping through fields (Access 2000 SR-1)

    Here is code that takes the contents of a table named tblDenormalised with fields CostCentre, Ja, Feb, ... , Dec and fills a table named tblNormalised (which must exist already) with fields CostCentre, Month and Percent

    Sub ConvertToNormalised()
    Dim dbs As DAO.Database
    Dim rstIn As DAO.Recordset
    Dim rstOut As DAO.Recordset
    Dim i As Integer

    On Error GoTo ErrHandler

    ' Open recordsets
    Set dbs = CurrentDb
    Set rstIn = dbs.OpenRecordset("tblDenormalised")
    Set rstOut = dbs.OpenRecordset("tblNormalised")

    ' Loop through records of input table
    Do Until rstIn.EOF
    ' Loop through months - index is 0-based
    ' So CostCentre = field 0, Jan = field 1 etc.
    For i = 1 To 12
    ' Add a new record to output table
    rstOut.AddNew
    rstOut!CostCentre = rstIn!CostCentre
    rstOut!Month = rstIn.Fields(i).Name
    rstOut!percent = rstIn.Fields(i).Value
    rstOut.Update
    Next i
    rstIn.MoveNext
    Loop

    ExitHandler:
    ' Clean up
    If Not rstOut Is Nothing Then
    rstOut.Close
    Set rstOut = Nothing
    End If
    If Not rstIn Is Nothing Then
    rstIn.Close
    Set rstIn = Nothing
    End If
    Set dbs = Nothing
    Exit Sub

    ErrHandler:
    MsgBox Err.Description, vbExclamation
    Resume ExitHandler
    End Sub

  5. #5
    2 Star Lounger
    Join Date
    Feb 2003
    Location
    Melbourne, Victoria, Australia
    Posts
    139
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: looping through fields (Access 2000 SR-1)

    Thanks again Hans,

    Absolutely perfect. I'm also learning in the process.

    Regards
    Michael

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

    Re: looping through fields (Access 2000 SR-1)

    <P ID="edit" class=small>(Edited by WendellB on 27-Feb-03 09:21. Activate hyperlink)</P>Hi Michael

    In addition to Hans soultion there are sample Normalize.mdb and Denormalize.mdb at:

    http://www.RogersAccessLibrary.com

    HTH

    John

  7. #7
    2 Star Lounger
    Join Date
    Feb 2003
    Location
    Melbourne, Victoria, Australia
    Posts
    139
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: looping through fields (Access 2000 SR-1)

    Hi John,

    Thanks for the URL. There are certainly a lot things to look at.
    I'll need to take my time and see what I can use in the short term.

    Regards
    Michael

Posting Permissions

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