Results 1 to 4 of 4
  1. #1
    Star Lounger
    Join Date
    Jun 2002
    Location
    Ontario
    Posts
    67
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Copying columns (Access 2000)

    I have a report from an enterprise system that I am importing and appending to a table. The report is an Accounting summary that shows expenses posted to an account. The report only includes the account number at page breaks and at change of account. I need account number in each record. I cannot get at the raw data for the report. Is there any way to copy the account number from the previous record? I am dealing with thousands of transactions so using CTRL ' through the file is not practical.

  2. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Copying columns (Access 2000)

    I would have a autonumber field to keep the table in input file sequence.
    I would then write some VBA code to read each record in the table and if the Account Number is not null I would save it in a local field, but if it is null I would copy the local field to the Account Number in the table and .Update the table entry.
    If this does not make much sense or you need some help with the VBA code just post back.
    Pat

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

    Re: Copying columns (Access 2000)

    You can use DAO (or ADO) code to loop through the records and set the account number.

    Assumptions:
    The report has been imported into a table; I'll call it tblReport.
    The account number field is named AccountNumber.

    First, make a backup copy of the database. Just in case...

    Switch to the Visual Basic Editor (Alt+F11).

    If you don't have a reference to DAO, set it now - select Tools/References... and check Microsoft DAO 3.6 Object Library.

    Create a new module (Insert/Module).

    Copy the following code into the module:

    <img src=/w3timages/blueline.gif width=33% height=2>

    Sub FillAccountNumber()
    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Dim varAccountNumber

    On Error GoTo ErrHandler

    ' Set database and recordset
    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset("tblReport")

    ' Loop through records
    Do While Not rst.EOF
    If IsNull(rst!AccountNumber) Then
    ' Set account number to last stored value
    rst.Edit
    rst!AccountNumber = varAccountNumber
    rst.Update
    Else
    ' Store current value
    varAccountNumber = rst!AccountNumber
    End If
    rst.MoveNext
    Loop

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

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

    <img src=/w3timages/blueline.gif width=33% height=2>

    Of course, you must substitute the actual table name and field name.

    Save the module now.

    Click somewhere in this code and press F5 to run it.

    Switch back to Access and check the table.

  4. #4
    Star Lounger
    Join Date
    Jun 2002
    Location
    Ontario
    Posts
    67
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Copying columns (Access 2000)

    Exactly! I just hit a mental roadblock but this will do. Thanks!

Posting Permissions

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