Results 1 to 7 of 7

Thread: Copying records

  1. #1
    Join Date
    Apr 2004
    Posts
    7

    Unanswered: Copying records

    I have the following code and I'm trying to look at the "login_date" for each individual record and determine if it equals a given login date, if so I then want it to stick that record into a new array of records. How can I do this??

    Set appAccess = CreateObject("Access.Application")
    ' Open database in Microsoft Access window.
    appAccess.OpenCurrentDatabase strConPath, True
    ' Open Orders form.
    appAccess.DoCmd.SelectObject acQuery, , True
    appAccess.DoCmd.OpenQuery "CustomerService", acReadOnly
    appAccess.DoCmd.GoToRecord , acFirst
    Do
    appAccess.DoCmd.GoToControl "login_date"
    appAccess.DoCmd.GoToRecord , acNext
    Loop Until (False)


    Thanks in advance!!!

  2. #2
    Join Date
    Apr 2004
    Location
    Kingsland, Georgia
    Posts
    231

    Re: Copying records

    Are you going to be putting it into an array held in memory, or into another table?

    Originally posted by NickS2000
    I have the following code and I'm trying to look at the "login_date" for each individual record and determine if it equals a given login date, if so I then want it to stick that record into a new array of records. How can I do this??

    Set appAccess = CreateObject("Access.Application")
    ' Open database in Microsoft Access window.
    appAccess.OpenCurrentDatabase strConPath, True
    ' Open Orders form.
    appAccess.DoCmd.SelectObject acQuery, , True
    appAccess.DoCmd.OpenQuery "CustomerService", acReadOnly
    appAccess.DoCmd.GoToRecord , acFirst
    Do
    appAccess.DoCmd.GoToControl "login_date"
    appAccess.DoCmd.GoToRecord , acNext
    Loop Until (False)


    Thanks in advance!!!

  3. #3
    Join Date
    Apr 2004
    Posts
    7
    I will be putting it into an array held in memory.

  4. #4
    Join Date
    Apr 2004
    Location
    Kingsland, Georgia
    Posts
    231
    I'm not too familiar with the docmd stuff. just a suggestion, but wouldn't it be just as easy to use a recordset? example:
    (at top)

    Code:
    Option Explicit
    Dim rst as DAO.Recordset
    
    Private Sub ToArray()
       Set rst = CurrentDb.OpenRecordset("SELECT * FROM tblCustomer")
    
       Dim dteTemp As Date
       'while the recordset isn't empty
       While rs1.EOF = False
          dteTemp = rst!Login_Date
          If dteTemp = desired_Date Then
           'copy the value from current record to the array, however you
           'want to. use your own field names in place of mine.
             With array(n)
                .Fields("Name") = rst.Fields("Name")
                .Fields("Field2") = rst.Fields("Field2")
                .Update
             End With
          End If
          'go to the next record
          rst.MoveNext
       Wend
        
        'release resources
        Set rst = Nothing
    End Sub
    I don't mean to make you change your code all around or anything. It just seems easier to me. Anyone else who is docmd.Savvy please feel free to answer. Good luck.

  5. #5
    Join Date
    Apr 2004
    Posts
    7
    What is DAO.Recordset. I'm not that familiar with Recordset but I'm willing to try it.

    Meanwhile, anyone have any ideas on my previous way would be good too.

  6. #6
    Join Date
    Apr 2004
    Posts
    7
    When I put DAO.Recordset in, it says user defined type not defined.

  7. #7
    Join Date
    Apr 2004
    Location
    Kingsland, Georgia
    Posts
    231
    Originally posted by NickS2000
    When I put DAO.Recordset in, it says user defined type not defined.
    sorry, man. there's two ways to do it. to use DAO, go up to the Tools menu (in the code window), and to References. Go downa dn put a check next to Microsoft DAO 3.5 object library.

    now it'll work. if you don't want to use DAO, in your declarations section under Option Explicit, put
    Code:
    Dim db as Database
    Dim rst as Recordset
    then, at the top of you in your function or sub or wherever you're putting the code to shove the stuff into the array, put
    Code:
    set db = CurrentDb
    set rst = db.OpenRecordSet("SELECT * FROM tblCustomer ")
    also noticed i left in "rs1" instead of "rst" on line 9 of the previous post. be sure to change that.

    one other thing i noticed that i didn't pay attention to...the array you're shoving these values into: how is it defined? the .Field("") syntax is used for recordsets, so that's probably not gonna work for your left hand side.
    Last edited by jmahaffie; 04-15-04 at 09:42.

Posting Permissions

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