Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2004
    Posts
    45

    Unanswered: dinamical population of a table problem

    Hallo,
    I have a query that gives me two hours: a start hour and a end hour.
    I'm trying to dinamically populate a table with these two hours and all the half hours that stays in the middle.
    That means:

    I copy the first record of the query (startHour) in the new table, then I start a loop that add an half an hour until it reaches the end_hour.
    Does someone have an idea?


    That's the code till now (same as the just posted thread):

    Sub AddValues()
    Dim cnn1 As ADODB.Connection
    Dim cat1 As New ADOX.Catalog
    Dim rst1 As New ADODB.Recordset
    Dim rst2 As New ADODB.Recordset

    'Set context for populating new table (Timetable)
    Set cnn1 = CurrentProject.Connection
    Set cat1.ActiveConnection = cnn1
    Set rst1.ActiveConnection = cnn1
    ' Set rst2.ActiveConnection = cnn1

    'Open recordsets based on new and original tables
    rst1.Open "Timetable", , adOpenKeyset, _
    adLockOptimistic, adCmdTable
    rst2.Open "qry_StartEndHour", cnn1, adOpenForwardOnly, _
    adLockReadOnly, adCmdTable

    'Loop through recordsets to copy from original to new table
    With rst1
    Do Until rst2.EOF
    .AddNew
    .Fields(0) = rst2.Fields(0)

    .Update
    rst2.MoveNext
    Loop
    End With

    End Sub

  2. #2
    Join Date
    Feb 2004
    Posts
    142
    dim two date vars tm_Start, tm_End

    be sure that you have the full date/time in the qry

    populate the first record w/ tm_Start

    do until tm_Start >= tn_End
    tm_start = dateadd("n",30,tm_Start)
    insert record
    loop

    this assumes that the first and last times are on the hour.
    KC

Posting Permissions

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