Results 1 to 9 of 9
  1. #1
    Join Date
    Oct 2009
    Posts
    204

    Unanswered: RecordsetClone question

    Hello,

    I am trying to copy one record, with the exception of 2 fields (which are not in this code) and move the end date to the start date in the new record. This code works exactly as I want it to....with the exception that it doesn't copy only the record I want, but also every single record in the table!

    What else do I need to add to this to make it work the way I want it to?

    Code:
    Dim RSC As Recordset, RS As Recordset
    Set RSC = Me.RecordsetClone
    Set RS = CurrentDb().OpenRecordset("tbl_EmployeeTable")
    While Not RSC.EOF
    RS.AddNew
    RS![EmployeeName] = RSC![EmployeeName]
    RS![Primary Department] = RSC![Primary Department]
    RS![JobTitle] = RSC![JobTitle]
    RS![Supervisor] = RSC![Supervisor]
    RS![Q Num End Date] = RSC![Q Num Start Date]
    RS![Hire Date] = RSC![Hire Date]
    RS![Taylor Dunn] = RSC![Taylor Dunn]
    RS![Forklift] = RSC![Forklift]
    RS![Phone] = RSC![Phone]
    RS![Computer Name] = RSC![Computer Name]
    RS![Badge Number] = RSC![Badge Number]
    RS![Radio] = RSC![Radio]
    RS.Update
    RSC.MoveNext
    Wend
    RS.Close
    RSC.Close
    Forms![frm_Employee Lists].Requery
    Forms![frm_Employee Lists].Refresh

  2. #2
    Join Date
    May 2005
    Posts
    1,191
    Quote Originally Posted by chris07tibgs View Post
    I am trying to copy one record, with the exception of 2 fields (which are not in this code) and move the end date to the start date in the new record. This code works exactly as I want it to....with the exception that it doesn't copy only the record I want, but also every single record in the table!

    What else do I need to add to this to make it work the way I want it to?
    You're actually telling it to copy ever record with your WHILE loop; you're effectively saying start at the beginning and keep adding a new record until the code reaches the end (.EOF).

    Go to the record in question in your recordset, and then remove the WHILE loop and just execute the code once.

    Rule #8

    HTH
    Me.Geek = True

  3. #3
    Join Date
    Oct 2009
    Posts
    204
    Quote Originally Posted by nckdryr View Post
    You're actually telling it to copy ever record with your WHILE loop; you're effectively saying start at the beginning and keep adding a new record until the code reaches the end (.EOF).

    Go to the record in question in your recordset, and then remove the WHILE loop and just execute the code once.

    Rule #8

    HTH
    This makes it work as expected, but now, after doing it for one record, when I try to do it again it gives me run time error 3021: no current record. I thought this was why I had docmd****ncommand accmdcurrentrecord? Now I have to close the form and reopen it if I want to do this to another record. How do I fix this?

  4. #4
    Join Date
    May 2005
    Posts
    1,191
    Instead of

    RSC.Close

    try

    Set RSC = Nothing

    Off the top of my head, I think when you close a recordsetclone, it also closes the source recordset (which is then repopulated when you reopen the form).
    Me.Geek = True

  5. #5
    Join Date
    Oct 2009
    Posts
    204
    Quote Originally Posted by nckdryr View Post
    Instead of

    RSC.Close

    try

    Set RSC = Nothing

    Off the top of my head, I think when you close a recordsetclone, it also closes the source recordset (which is then repopulated when you reopen the form).
    My code has changed a little, I am trying to do something else to further streamline the process on the user's end by having an input box where they put an associate's new number that then populates into field [Q Number]

    The input box looks okay but it does not populate into the field....help?

    Also, I did what you suggested with the set rsc=nothing, I also took out the rs.close, and a few other ideas, but nothing changes the problem, it still does the function only once then cannot find the recordset. If the form is based on a query and it is looking up the table as the form's recordset, could that be where my problem is, or does that even matter?

    Here is my current code:
    Code:
    Dim Terminated As Boolean
    Terminated = True
    Dim strmsg As String
    Dim strinput As String
    
    Me.[Q Num End Date] = Date
    Me.Term = Terminated
    
    
    
    strmsg = "What is the new QX number?"
    strinput = InputBox(Prompt:=strmsg, title:="End Temp QX Number")
    
    
    Dim RSC As Recordset, RS As Recordset, db As Database
    Set db = CurrentDb
    Set RS = db.OpenRecordset("tbl_EmployeeTable")
    Set RSC = Me.RecordsetClone
    
    RS.AddNew
    DoCmd****nCommand acCmdSelectRecord
    RS![EmployeeName] = RSC![EmployeeName]
    strinput = RSC![Q Number]
    RS![Primary Department] = RSC![Primary Department]
    RS![JobTitle] = RSC![JobTitle]
    RS![Supervisor] = RSC![Supervisor]
    RS![Q Num Start Date] = RSC![Q Num End Date]
    RS![Hire Date] = RSC![Hire Date]
    RS![Taylor Dunn] = RSC![Taylor Dunn]
    RS![Forklift] = RSC![Forklift]
    RS![Phone] = RSC![Phone]
    RS![Computer Name] = RSC![Computer Name]
    RS![Badge Number] = RSC![Badge Number]
    RS![Radio] = RSC![Radio]
    RS.Update
    RSC.MoveNext
    Set RSC = Nothing
    
    
    Forms![frm_Employee Lists].Requery
    Forms![frm_Employee Lists].Refresh

  6. #6
    Join Date
    Oct 2009
    Posts
    204
    Ok, a new problem I have discovered....This code only clones the first record on the form. No matter which record I click on, it keeps duplicating the same record. How do I get it to specify another record?

  7. #7
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Your code does not create a single row recordset, it creates a recordset that contains the same number of rows as the one it is cloned from. However, as for any recordset, the current record when you open it is the first one (first row) if you do not perform any move operation on it prior to accessing its data. If you want to retrieve a particular row from the recordset, you have to make this row the current one first. Search for RecordsetClone and Bookmark in Access help, there are examples such as this one:
    Code:
    Sub SupplierID_AfterUpdate()
        Dim rst As Recordset
        Dim strSearchName As String
    
        Set rst = Me.RecordsetClone
        strSearchName = Str(Me!SupplierID)
        rst.FindFirst "SupplierID = " & strSearchName
            If rst.NoMatch Then
                MsgBox "Record not found"
            Else
                Me.Bookmark = rst.Bookmark
            End If
        rst.Close
    End Sub
    Have a nice day!

  8. #8
    Join Date
    Oct 2009
    Posts
    204
    Quote Originally Posted by Sinndho View Post
    Your code does not create a single row recordset, it creates a recordset that contains the same number of rows as the one it is cloned from. However, as for any recordset, the current record when you open it is the first one (first row) if you do not perform any move operation on it prior to accessing its data. If you want to retrieve a particular row from the recordset, you have to make this row the current one first. Search for RecordsetClone and Bookmark in Access help, there are examples such as this one:
    Code:
    Sub SupplierID_AfterUpdate()
        Dim rst As Recordset
        Dim strSearchName As String
    
        Set rst = Me.RecordsetClone
        strSearchName = Str(Me!SupplierID)
        rst.FindFirst "SupplierID = " & strSearchName
            If rst.NoMatch Then
                MsgBox "Record not found"
            Else
                Me.Bookmark = rst.Bookmark
            End If
        rst.Close
    End Sub
    I'm not sure how I can apply this to my situation....To my understanding, the example above takes the text put into a combo box and clones a record based on that. After reading bookmark, it looks like that will only work with a record specified by the user. How can I get this to select the current record?

  9. #9
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    If you have a primary key or another unique ID in your form, you can use it to build your search criteria. After using the FindFirst method the current row in the clone will be the same as the current row in your form (or more precisely it will be the same as the current row in the form recordset).

    Suppose that you have a column named RecordID that is the primary key of the source table of the form recordset, then:
    Code:
        Dim rst As DAO.Recordset
        Dim strCriteria As String
    
        Set rst = Me.RecordsetClone
        strCriteria  = "RecordID  = " & CStr(Me!RecordID )
        rst.FindFirst strCriteria 
            If rst.NoMatch Then
                MsgBox "Record not found"
            Else
                '
                ' the current record in the clone is now the same 
                ' as the current record in the form recordset
                '
            End If
        rst.Close
        Set rst = Nothing
    Once more, all this is explained in Access documentation and help.
    Have a nice day!

Posting Permissions

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