Results 1 to 4 of 4
  1. #1
    3 Star Lounger
    Join Date
    Sep 2001
    Location
    Stuck at work..., Missouri, USA
    Posts
    248
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Duplicating records (2003)

    I have a database used for quarterly reporting. In that database, I have roughly 10 tables that hold quarter-specific data. Each table has different columns. Each row of data in every table has a field named YearQtr which holds the Year and Quarter that I'm reporting on (200802, for example).

    Each quarter I need to add a new row to each table for the new quarter. Oddly enough, the data for the new quarter is an exact duplicate of the previous quarter, except for the YearQtr. (It could change later, but off the bat it has to be the same)

    Is there an easy way to code this so I don't have to do this manually each quarter?
    <font face="Comic Sans MS">Morgan Erickson</font face=comic>
    morgan.erickson@sprint.com
    <img src=/S/flags/USA.gif border=0 alt=USA width=30 height=18>-From <img src=/S/flags/Colorado.gif border=0 alt=Colorado width=30 height=18> but living in <img src=/S/flags/Missouri.gif border=0 alt=Missouri width=30 height=18>...and working in Kansas.

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

    Re: Duplicating records (2003)

    You could use the following code; it requires that you have a reference to the Microsoft DAO 3.6 Object Library.
    <code>
    Sub AddRecord(TableName As String)
    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Dim i As Integer
    Dim intCount As Integer
    Dim arrValues() As Variant
    Dim lngOldQtr As Long
    Dim lngNewQtr As Long
    Dim lngYear As Long
    Dim lngQtr As Long
    ' Determine most recent quarter in table
    lngOldQtr = DMax("YearQtr", TableName)
    ' Extract year and quarter
    lngYear = lngOldQtr 100
    lngQtr = lngOldQtr Mod 100
    ' Determine next quarter
    If lngQtr = 4 Then
    lngQtr = 1
    lngYear = lngYear + 1
    Else
    lngQtr = lngQtr + 1
    End If
    lngNewQtr = 100 * lngYear + lngQtr
    ' Open recordset on table
    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset(TableName, dbOpenDynaset)
    ' Initialize variables
    intCount = rst.Fields.Count
    ReDim arrValues(intCount - 1)
    ' Find most recent record
    rst.FindFirst "YearQtr=" & lngOldQtr
    ' Fill array with field values
    For i = 0 To intCount - 1
    rst.Fields(i).Type
    If rst.Fields(i).Name = "YearQtr" Then
    arrValues(i) = lngNewQtr
    Else
    arrValues(i) = rst.Fields(i).Value
    End If
    Next i
    ' Create new record
    rst.AddNew
    ' Populate fields except for AutoNumber fields
    For i = 0 To intCount - 1
    If rst.Fields(i).Attributes And dbAutoIncrField = 0 Then
    rst.Fields(i).Value = arrValues(i)
    End If
    Next i
    ' Save record
    rst.Update
    ' Clean up
    rst.Close
    Set rst = Nothing
    Set dbs = Nothing
    End Sub
    </code>
    Use it like this:
    <code>
    Sub AddMultiple()
    AddRecord "tblThis"
    AddRecord "tblThat"
    AddRecord "tblOther"
    End Sub</code>

  3. #3
    3 Star Lounger
    Join Date
    Sep 2001
    Location
    Stuck at work..., Missouri, USA
    Posts
    248
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Duplicating records (2003)

    Hans, thanks for the code.

    It worked for me, but only AFTER I removed this piece:
    'If rst.Fields(i).Attributes And dbAutoIncrField = 0 Then

    With that check, it always skipped the
    rst.Fields(i).Value = arrValues(i)
    so I ended up with an empty record.
    <font face="Comic Sans MS">Morgan Erickson</font face=comic>
    morgan.erickson@sprint.com
    <img src=/S/flags/USA.gif border=0 alt=USA width=30 height=18>-From <img src=/S/flags/Colorado.gif border=0 alt=Colorado width=30 height=18> but living in <img src=/S/flags/Missouri.gif border=0 alt=Missouri width=30 height=18>...and working in Kansas.

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

    Re: Duplicating records (2003)

    That's strange (the check was intended to prevent duplicating an AutoNumber field, if present, and tested OK), but if it works well for you without it, fine.

Posting Permissions

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