Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2003
    Posts
    311

    Unanswered: Using a CMD to insert from a collection and ...

    I have a table with 3 colms

    CkDate CkNum Dept


    i have a collection with 1 or more items.

    i would like to insert the items into the ckNum colm from the collection and 1 date and 1 Dept How can this be done?


    ending tbl would be something like

    CkDate CkNum Dept
    10/15/2003 2 ACCT
    10/15/2003 4 ACCT
    10/15/2003 9 ACCT
    10/15/2003 16 ACCT
    10/15/2003 52 ACCT
    10/15/2003 53 ACCT
    10/15/2003 55 ACCT


    So the date and dept will remain constant but the cknum will be from the collection and varying.



    Thanks to all the help so far!!!!


    M~

  2. #2
    Join Date
    Oct 2003
    Location
    Roanoke, Va
    Posts
    445

    Smile Re: Using a CMD to insert from a collection and ...

    Originally posted by mikezcg
    I have a table with 3 colms

    CkDate CkNum Dept


    i have a collection with 1 or more items.

    i would like to insert the items into the ckNum colm from the collection and 1 date and 1 Dept How can this be done?


    ending tbl would be something like

    CkDate CkNum Dept
    10/15/2003 2 ACCT
    10/15/2003 4 ACCT
    10/15/2003 9 ACCT
    10/15/2003 16 ACCT
    10/15/2003 52 ACCT
    10/15/2003 53 ACCT
    10/15/2003 55 ACCT


    So the date and dept will remain constant but the cknum will be from the collection and varying.



    Thanks to all the help so far!!!!


    M~
    OK. This is using DAO. You can also use ADO to accomplish this.

    Private Sub Command1_Click()
    Dim colMissChecks As New Collection
    Dim x As Integer, strItem As String
    Dim db As Database, rs As Recordset

    Set db = CurrentDb
    Set rs = db.OpenRecordset("tblChecks", dbOpenDynaset)

    'I'll Add to the collection so we can add it to the table.

    For x = 1 To 10
    strItem = "Check No. " & x
    colMissChecks.Add strItem
    Next

    With rs

    For Each varItem In colMissChecks

    .AddNew
    .Fields("CheckNo") = varItem
    .Fields("Date") = #10/15/2003#
    .Fields("Dept") = "Acct"
    .Update

    Next varItem

    End With

    End Sub

    There it is. Good Luck

    Gregg

  3. #3
    Join Date
    Oct 2003
    Posts
    311

    Re: Using a CMD to insert from a collection and ...

    IM embaraced,,, Ha ha ha,, good job thanks!!!

    ~M~

Posting Permissions

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