Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    Feb 2003
    Location
    Warwick, Warwickshire, England
    Posts
    189
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Appending records (2000)

    I'm trying to create a table which will have 2 fields - MatchDate and MatchNumber.

    The table will be blank to start with, then I want to add the following records:

    MatchDate MatchNumber
    Todays date 0
    Todays date + 1 MatchNumber + 1
    Todays date + 2 MatchNumber + 2
    etc

    So I end up with:
    MatchDate MatchNumber
    23/09/2005 0
    24/09/2005 1
    25/09/2005 2
    26/09/2005 3
    etc

    The maximum value is to be todays date + 100 days

    Any suggestions as to the best way to go about this?

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

    Re: Appending records (2000)

    The easiest way is to create the data in Excel, using Excel's fill down feature, then copy and paste them into the empty table.

    If you need to do this regularly, you can write code (using DAO or ADO) to creare the records. Post back if you need this.

  3. #3
    2 Star Lounger
    Join Date
    Feb 2003
    Location
    Warwick, Warwickshire, England
    Posts
    189
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Appending records (2000)

    It will be a regular thing, so need code (or a very neat query) to do this.
    I tested the rest of the routine I was running with some cut&paste data, but knew I'd need to get this query sorted before I could continue further.
    Thanks
    John

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

    Re: Appending records (2000)

    Here is some code. The table name is tblMatch. The code uses ADO, there is a reference to a Microsoft ActiveX Data Objects 2.n Library by default, so that shouldn't be a problem.

    Sub FillTable()
    Dim cnn As ADODB.Connection
    Dim rst As New ADODB.Recordset
    Dim i As Long

    On Error GoTo ErrHandler

    Set cnn = CurrentProject.Connection
    rst.Open "tblMatch", cnn, adOpenKeyset, adLockOptimistic, adCmdTableDirect
    For i = 0 To 100
    rst.AddNew
    rst!MatchDate = Date + i
    rst!MatchNumber = i
    rst.Update
    Next i

    ExitHandler:
    On Error Resume Next
    rst.Close
    Set rst = Nothing
    Set cnn = Nothing
    Exit Sub

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

Posting Permissions

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