If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Create a button to edit and save new record
Hi there
I would like to create a button for a form that would allow me to edit a record, make changes, and save it as a new record. I don't want to save a record and have it overwrite earlier information. How will I create this button? Thank you in advance |
#2
|
|||
|
|||
Create a button to edit and save new record
The best way would be to copy the existing record into a new record and then
edit the copy. I wonder why you need to do this. It indicates to me that you ***might*** have design problems in your table structure if you need to duplicate most (or many) of the fields in one record in the next record. Here is an old posting from Dirk Goldgar that I have in my archives. I've not used it, but Dirk Goldgar is an MVP and I believe his coding skills are superior. Duplicate record except for primary key, non-updatable fields, and specified list of fields From: "Dirk Goldgar" 'Needs to check to see if form's addnew property is true and if a current record exists. 'Needs to make sure that the recordsetclone bookmark matches the form's recordset bookmark '----- 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. -- Dirk Goldgar, MS Access MVP www.datagnostics.com John Spencer Access MVP 2002-2005, 2007-2010 The Hilltop Institute University of Maryland Baltimore County forest8 wrote: Hi there I would like to create a button for a form that would allow me to edit a record, make changes, and save it as a new record. I don't want to save a record and have it overwrite earlier information. How will I create this button? Thank you in advance |
#3
|
|||
|
|||
Create a button to edit and save new record
Hi
This is just what I needed. The reason for this is that I have created a case management database file. One of the form deals with reviewing previous week's entries and making changes for the current week. I though it would be easier to review the older record and make changes and save it as a new record. Thanks again. "John Spencer" wrote: The best way would be to copy the existing record into a new record and then edit the copy. I wonder why you need to do this. It indicates to me that you ***might*** have design problems in your table structure if you need to duplicate most (or many) of the fields in one record in the next record. Here is an old posting from Dirk Goldgar that I have in my archives. I've not used it, but Dirk Goldgar is an MVP and I believe his coding skills are superior. Duplicate record except for primary key, non-updatable fields, and specified list of fields From: "Dirk Goldgar" 'Needs to check to see if form's addnew property is true and if a current record exists. 'Needs to make sure that the recordsetclone bookmark matches the form's recordset bookmark '----- 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. -- Dirk Goldgar, MS Access MVP www.datagnostics.com John Spencer Access MVP 2002-2005, 2007-2010 The Hilltop Institute University of Maryland Baltimore County forest8 wrote: Hi there I would like to create a button for a form that would allow me to edit a record, make changes, and save it as a new record. I don't want to save a record and have it overwrite earlier information. How will I create this button? Thank you in advance . |
Thread Tools | |
Display Modes | |
|
|