A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » New Users
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Create a button to edit and save new record



 
 
Thread Tools Display Modes
  #1  
Old March 6th, 2010, 10:18 PM posted to microsoft.public.access.gettingstarted
forest8
external usenet poster
 
Posts: 196
Default 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  
Old March 7th, 2010, 01:51 PM posted to microsoft.public.access.gettingstarted
John Spencer
external usenet poster
 
Posts: 7,815
Default 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  
Old March 7th, 2010, 04:26 PM posted to microsoft.public.access.gettingstarted
forest8
external usenet poster
 
Posts: 196
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 08:23 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.