Results 1 to 4 of 4
  1. #1
    3 Star Lounger
    Join Date
    Aug 2001
    Location
    Cape Town, South Africa, South Africa
    Posts
    399
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Copy a value to all records (Access 2000)

    I imported a text file with headers. Now I need the headers to be repeated on all the records. Example:

    Fld1 Fld2
    Peter
    Swimming
    Running
    John
    Boxing
    Running

    should be

    Fld1 Fld2
    Peter Swimming
    Peter Running
    John Boxing
    John Running


    For some reason I have problems with the first section. Field 1 is the name and Field 2 is the activity. In the table the name and activity is on differant records and I would like the name to be repeated on all the activity records as value for Field 1

    I hope I've stated it clear enough

    Thanks

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

    Re: Copy a value to all records (Access 2000)

    In your post it looks as if all values are in Fld1 now, and nothing is in Fld2. Is that what you intended? If so, please confirm. If not, take a look at <!post=Making Tables in the Lounge,162644>Making Tables in the Lounge<!/post> and <!post=Copying a Word or Excel table to a post,164109>Copying a Word or Excel table to a post<!/post> to learn how to display tables in a post.

  3. #3
    3 Star Lounger
    Join Date
    Aug 2001
    Location
    Cape Town, South Africa, South Africa
    Posts
    399
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Copy a value to all records (Access 2000)

    Here is the proper table example, thanks for the guidance Hans:

    <table border=1><td>Field1</td><td>Field2</td><td>Peter</td><td> </td><td> </td><td>Swimming</td><td> </td><td>Running</td><td>John</td><td> </td><td></td><td>Boxing</td><td></td><td>Swimming</td></table>

    The new table should be:

    <table border=1><td>Field1</td><td>Field2</td><td>Peter</td><td>Swimming</td><td>Peter</td><td>Running</td><td>John</td><td>Boxing</td><td>John</td><td>Swimming</td></table>

    I hope this is clearer Hans.

    Thanks

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

    Re: Copy a value to all records (Access 2000)

    Hello Mario,

    Yes, that is much clearer. The code below will do what you want. You need to do the following:
    <UL><LI>The code uses DAO, so you must set a reference for that. In the Visual Basic Editor, select Tools | References... Locate the reference to the Microsoft DAO 3.6 Object Library, check the corresponding box and then click OK.
    <LI>Copy your table to the clipboard, then paste it and select the option to paste only the structure, not the data. Now, you have an empty table with the same structure as the one you want to transform.
    <LI>Copy the code from this post into a standard module.
    <LI>In the code, replace "tblIn" by the name of the table to be transformed, and "tblOut" by the name of the empty copy you just made.
    <LI>If necessary, replace all occurrences of "Field1" and "Field2" by the actual field names.
    <LI>Run the code.[/list]Note: this code doesn't modify the original table. If things go wrong, just delete any records from the target table (the copy), and after modifying the code if necessary, run it again.

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

    Sub TransformTable()
    Dim dbs As DAO.Database
    Dim rstIn As DAO.Recordset
    Dim rstOut As DAO.Recordset
    Dim strField1 As String

    On Error GoTo ErrHandler

    Set dbs = CurrentDb
    Set rstIn = dbs.OpenRecordset("tblIn")
    Set rstOut = dbs.OpenRecordset("tblOut")

    Do While Not rstIn.EOF
    strField1 = rstIn!Field1
    rstIn.MoveNext
    Do While Not rstIn.EOF
    If Not IsNull(rstIn!Field1) Then
    Exit Do
    End If
    rstOut.AddNew
    rstOut!Field1 = strField1
    rstOut!Field2 = rstIn!Field2
    rstOut.Update
    rstIn.MoveNext
    Loop
    Loop

    ExitHandler:
    On Error Resume Next
    rstIn.Close
    Set rstIn = Nothing
    rstOut.Close
    Set rstOut = Nothing
    Set dbs = Nothing
    Exit Sub

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

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

Posting Permissions

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