How to make a copy of a (large) record at the click of a button using VBA

  • Thread starter Thread starter sundarun
  • Start date Start date
S

sundarun

Hi Forumds,

I have a table of about 150 fields, some are images!. It's a machine setup
instructions table.
The primary keys would be Instruction No, Issue No.

If I am to make a changes to few fields (even one field) I need to have
record copied and increment the issue number. This is a requirement.

I have a combobox for part number which afterupdate lists the latest
instruction in a listbox.

I need to update the instructions for this instruction number.

At the click of a button (say cmdUpdate), I need the desired record copied
as new, without exiting the record (to avoid duplicate record problem), need
to only increment the issue number, and open the data entry interface (the
form with the new copied, incremented record) with the new record.

I believe that looping through fieldcollection would provide a solution.
But how do I implement this. I am just a novice programmer, fairly
comfortable with form and report event properties, but not recordset
manipulation. Can anyone help me please?
Just in case.. my email: (e-mail address removed)
Thanks in advance.
sundarun
 
Here you go - this code will 'clone' the current record - attach it to
whichever event makes sense for you. I don't know whether this will work
with images but you can give it a whirl and let me know.

A word of caution though, a table with 150 fields suggests that your data
might not be normalized. Before you write a bunch of elaborate code you
might want to look at your table design first and make sure that it is
right.


Dim rst As Dao.Recordset
Dim fld As Dao.Field
Dim lngCustid As Long
Set rst = Me.RecordsetClone.Clone
Me.RecordsetClone.Bookmark = Me.Bookmark
rst.Bookmark = Me.Bookmark
With Me.RecordsetClone
rst.AddNew
For Each fld In .Fields
'skip the primary key field -
'chg CustomerID to the name of your primary key field
If fld.Name <> "Customerid" Then
rst.Fields(fld.Name).Value = fld.Value
End If
Next fld
' If your Primary key (Customerid) is autonum you don't need this
' otherwise, replace this with whatever method you use for getting
' the next primary key value. This one is a simple incrementing value
'
lngCustid = Nz(DMax("Customerid", "Customers"), 0) + 1
rst.Fields("Customerid") = lngCustid
' Update the recordset with the new value
rst.Update
rst.Bookmark = rst.LastModified
' set the form's book mark to the new record
Me.Bookmark = rst.Bookmark
End With
Set rst = Nothing
Set fld = Nothing
 
Back
Top