Results 1 to 5 of 5
  1. #1
    Star Lounger
    Join Date
    Mar 2002
    Location
    Decatur, AL
    Posts
    53
    Thanks
    2
    Thanked 0 Times in 0 Posts

    cycle thru changing fields (Office 2000)

    I have a table that is imported from an ever-changing Excel spreadsheet that I need to convert to a usable table structure. The imported table specifies the quantity of dynamic items (columns) to include for each system (rows). The 2 leftmost columns identify the system and are static, the rest of the imported column names (table fields) and quantities change regularly. Actual data is either a number or a blank, i.e. there are no zeroes in the Excel file.

    Here's what I want to accomplish:
    1. Import the latest Excel spreadsheet to a temporary table.
    2. Iterate through each field in a particular record of the temporary table and, if there is a number in that field, append a new record into a table with structure System identifier 1, System identifier 2, (the 2 leftmost columns in the Excel file and the 1st two fields in the temp table), the current field NAME, and the value in that field.

    Does that make sense? Does anyone know how to do that?

    Thanks for any hints.
    Kathi

  2. #2
    Star Lounger
    Join Date
    Mar 2002
    Location
    Decatur, AL
    Posts
    53
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: cycle thru changing fields (Office 2000)

    Hans, thanks for replying. I set the reference, pasted your code as a Public sub on an unbound form that contains a single command button. I named the sub ProcessTable and changed the constants to the names of my tables. The OnClick event for the button has one line "ProcessTable"

    What have I missed? The very informative (sarcasm) error I get when I click the button is "Item not found in this collection". No identification of which item was not found, no debug button and highlighted code, nothing.

    Any ideas?
    Kathi

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

    Re: cycle thru changing fields (Office 2000)

    I'm very sorry, that's my fault. I didn't test the code before posting it. Change the line

    For i = 2 To rstTemp.Fields.Count

    to

    For i = 2 To rstTemp.Fields.Count - 1

    (fields are numbered starting at 0, so the last one is Count-1, not Count)

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

    Re: cycle thru changing fields (Office 2000)

    Code corrected by HansV - see below

    Try the following. You must set a reference to the Microsoft DAO 3.6 Object Library in Tools | Reference in the Visual Basic Editor. Substitute the correct names of the tables in the constants at the beginning of the code.

    Sub ProcessTable()
    ' Substitute actual table names
    Const strTemp = "tblTemp"
    Const strDest = "tblTarget"

    Dim dbs As DAO.Database
    Dim rstTemp As DAO.Recordset
    Dim rstDest As DAO.Recordset
    Dim i As Integer

    On Error GoTo ErrHandler

    ' Reference to database
    Set dbs = CurrentDb
    ' Open recordsets on tables
    Set rstTemp = dbs.OpenRecordset(strTemp, dbOpenDynaset)
    Set rstDest = dbs.OpenRecordset(strDest, dbOpenDynaset)

    ' Loop through records of imported table
    Do While Not rstTemp.EOF
    ' Loop through fields (starting with 3rd field)
    ' originally had For i = 2 To rstTemp.Fields.Count
    For i = 2 To rstTemp.Fields.Count - 1
    If Not IsNull(rstTemp.Fields(i)) Then
    ' Add new record to destination table
    rstDest.AddNew
    rstDest.Fields(0) = rstTemp.Fields(0)
    rstDest.Fields(1) = rstTemp.Fields(1)
    rstDest.Fields(2) = rstTemp.Fields(i).Name
    rstDest.Fields(3) = rstTemp.Fields(i)
    rstDest.Update
    End If
    Next i
    rstTemp.MoveNext
    Loop

    ExitHandler:
    ' Clean up
    On Error Resume Next
    rstDest.Close
    rstTemp.Close
    Set rstDest = Nothing
    Set rstTemp = Nothing
    Set dbs = Nothing
    Exit Sub

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

  5. #5
    Star Lounger
    Join Date
    Mar 2002
    Location
    Decatur, AL
    Posts
    53
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: cycle thru changing fields (Office 2000)

    And VOILA!!! There we have it.

    I should have seen that myself, I suppose, but I got so wrapped up in making sure all the DAO references were set and the items were declared. You know, "Can't see the forest for the trees."

    Once again, you are my hero!

    Thanks, Kathi

Posting Permissions

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