How To copy a record in a same table using VB?

[r143r]

Hi,

I am trying to copy a record from a form and trying to save it in the same table. Now I dont want to use the duplicate record button that access has (because it doesn't work). Now is there any other way that I can perform this task using vb (i.e to copy the record in the same table)?

 

[stickers]

There is a way of using vb to duplicate records. Are you using DAO or ADO? If ADO I can give you some code, but I'm not sure how you'd achieve it in DAO.

Post back with a few details (field names etc.) and I'll get back to you tomorrow.

 

[r143r]

Hey Stickers,

I think I am using DAO, but not sure. The fields are: Project#, TypeOfRequest, Manager, Associates, and such.

I am trying to take the record that is current in the form and then creating a copy of it in the same table but with a new version. The new version is just incrementing the current version by 1. So that my Manager can track the number of changes that they have to perform on each request.

Thanks for your help.

 

[psemianonymous]

Let me tell you a few things:

1. I had this exact requirement on my database. Each edit adds a new line to the table. I believe this sort of auditing can be handled using a module (any help here?), but I did not look into the subject. What I did is this:
-Went from bound to unbound form. It was not fun setting it up, but I saved a lot of debugging time by doing so.
-Every time a user clicked "Save", I checked the current table data vs form data and added a new record if the form data differed.
-I had a query which pulled the "most recent info" so that I would not have to worry about linking the tables correctly every time; instead I can refer to the query. I used a foreign key in the main table to hold the most recent info--in this way the query is still editable, but it also means that I have to keep track of what is the "most recent info".


If you stick to bound forms, you're going to have to deal with:
-If a user hits "Shift-Enter" or mouse-wheels across a record, you're going to have to intercept these "saves" or "accidental saves" and cancel them.
-If a user breaks validation rules while attempting to save a record, good luck


Anyway, I spent a great deal of time working on this. Good luck.


Aside: does anyone have a good alternate solution to this person's problem, i.e. stored audits module or somesuch?

--
Find common answers using Google Groups:

http://groups.google.com/groups?group=comp.databases.ms-access

Corrupt MDBs FAQ

http://www.granite.ab.ca/access/corruptmdbs.htm

 

[stickers]

As I said, I'm not sure how it would work in DAO.

However, if you search around, I'm fairly sure I've stumbled across a thread on this or one of the other Access forums that gives you quite a good audit trail. Basically you add a memo field to your table, and every time someone makes a change to a record, it will insert something like "Admin 06/06/03 Changed TypeofRequest from x to y"

Type Audit track or Audit trail into the Advanced search box.

When I was replicating a record, it was one that had already been created. The way I did it was to open a recordset that just contained the record that I wanted to copy.

I created a load of variables to correspond to each field that I wanted to copy, and then set each variable to the value of each field in the recordset (containing only one record)

Then I used the addnew method of the recordset to add a new record, and just set the value of each field in the new record to the value of the variable. If you wanted to add 1 to the version, you would just put

Recordset!Version = Variable + 1

That works, but I'm not sure it's the simplest way around your problem! I think I would try the audit track line of enquiry.

Good luck!

 

[Betsvigi9]

I don't know if this will help, but I use the following code. It creates a new record and then picks up the date and employee number from fields in the previous record and copies them into a new record. Bit long winded perhaps, but it could be adapted to pick up any or all of the fields in a record.

Private Sub CopyToNewRecord_Click()
Dim db As DAO.Database, rst As Object
Dim strEmpNo As String, strWeeks As String
    Const vbinformation = 64
    On Error GoTo ErrorHandler_Err
    
    DoCmd.GoToRecord , , acNewRec

    Set rst = Me.RecordsetClone
    If RecordsetClone.BOF And RecordsetClone.EOF Then Exit Sub
    
    If Not NewRecord Then Exit Sub
    
    RecordsetClone.MoveLast
    strEmpNo = RecordsetClone!EMP_NO
    strWeeks = RecordsetClone!Weeks

    RecordsetClone.AddNew
    RecordsetClone!EMP_NO = strEmpNo
    RecordsetClone!Weeks = strWeeks
    RecordsetClone.Update

    Code.SetFocus

CopyToNewRecord_Click_Exit:
    Exit Sub
ErrorHandler_Err:
    Select Case Err
        Case 2448    'Action Close Form Cancelled
        Case Else
            msgbox "Contact the Administrator Error: " & Error & " (" & Err & ")"
            
     End Select
     Resume CopyToNewRecord_Click_Exit

End Sub

JHB

 

[hymn]

You could try something like this on a button


DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdCopy
DoCmd.GoToRecord , , acNew
DoCmd.RunCommand acCmdPaste
DoCmd.RunCommand acCmdPaste

BUT this will copy ever field of that record
To make fields blank

field=""
fielda=""

Hope this helps
Hymn

 

[r143r]

Hi Everyone,

Thanks a lot for your help, I am sure I will be able to copy a record using one of your code's.

But here are some of the problems that I encountered while using the codes:

When I was trying hymn's code, I got an error message saying that I cant copy the record since I may be at end of the record set.

Do you guys know how to solve this error?

And then I tried Betsvigi9's code and it is working for the most part, but I have Date/Time fields in my table and sometimes they are null and as a result I dont know what to do when they are null, bcoz if they are null then I get an error message saying INVALID USE OF NULL. I am declaring the variables (to store all the date/time fields) as strings to store all the values.

Can any one help me out with this error?

Thanks a lot for your help

r143r