will said:
i'm working on an Access 2003 frontend, where all the data is stored
in a MySQL backend. there is a form for a table, and it needs a
"duplicate record" button. the normal way to do this would be:
DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdCopy
DoCmd.RunCommand acCmdPasteAppend
and if the data was in Access, it would know not to copy the PK field,
because its type would be AutoNumber. but all Access knows is that
the PK is a number, and so it copies it just like all the other
fields.
i could just make an SQL query that would manually copy all the values
in the row, but there are more than 50 fields, and they will probably
change, so the query would have to be constantly updated.
is there some smart way to say in Access VB, "insert a new record,
copying all these values, EXCEPT for this one, which is the primary
key"? then MySQL would take care of the primary key as usual.
You might try this (barely tested) function:
'----- start of code -----
Function CloneCurrentRecord(frm As Access.Form, ParamArray NotField())
Dim fld As DAO.Field
Dim strFieldName As String
Dim blnCopyField As Boolean
Dim intI As Integer
' Save the current record, if it's dirty
If frm.Dirty Then
On Error GoTo Err_SavingRecord
frm.Dirty = False
End If
' If we're at a new record, then there's nothing to copy.
If frm.NewRecord Then
MsgBox _
"Unable to copy blank record.", _
vbInformation, _
"Not Copied"
Exit Function
End If
On Error GoTo Err_General
' Create a new record in the form's recordsetclone, and
' copy all the updatable fields from the current record.
With frm.RecordsetClone
.AddNew
For Each fld In frm.Recordset.Fields
strFieldName = fld.Name
' Should this field be copied?
blnCopyField = True
If (fld.Attributes And dbAutoIncrField) <> 0 Then
blnCopyField = False
ElseIf (fld.Attributes And dbUpdatableField) = 0 Then
blnCopyField = False
ElseIf UBound(NotField) >= LBound(NotField) Then
For intI = LBound(NotField) To UBound(NotField)
If strFieldName = NotField(intI) Then
blnCopyField = False
Exit For
End If
Next intI
End If
If blnCopyField Then
.Fields(strFieldName).Value = fld.Value
End If
Next fld
.Update
.Bookmark = .LastModified
frm.Bookmark = .Bookmark
End With
Exit_Point:
Exit Function
Err_SavingRecord:
MsgBox _
"Error: Unable to save the current record " & _
"in order to copy it." & _
vbCr & vbCr & _
"Error number = " & Err.Number & _
", description = '" & Err.Description & "'", _
vbExclamation, _
"Can't Save Record"
Resume Exit_Point
Err_General:
MsgBox _
"Error: Unexpected error occurred while copying record." & _
vbCr & vbCr & _
"Error number = " & Err.Number & _
", description = '" & Err.Description & "'", _
vbExclamation, _
"Error Copying Record"
Resume Exit_Point
End Function
'----- end of code -----
The function is designed to copy all updatable fields from the current
record to a new record, and then move to that record. It uses the
form's Recordset and RecordsetClone, and assumes these are DAO
recordsets, so it wouldn't work in an ADP (though it could probably be
modified to do so). Autonumber fields and nonupdatable fields are
excluded automatically, but it also provides for an optional list of
fields not to be copied.
You could call it using a line of code in a form's module, like this:
CloneCurrentRecord Me
or
CloneCurrentRecord Me, "ExcludeThisField", "ThisOneToo"
Or you could call it from a function expression in the OnClick event
property of a command button, like this:
=CloneCurrentRecord([Form])
As I said, I've only barely tested this, so there could be bugs or
unknown twists to it. In particular, I haven't tested the
"nonupdatable" check for fields.
