Just trying to copy a record
Quote:
>I am a novice trying to learn Access VBA and have a problem. I am simply(?)
>trying to copy the current record in a form (the Projects table) to a
second
>table (Revised) in a before update event. The code I have come up with is
as
>follows:
>Option Compare Database
>Option Explicit
>Private Sub Form_BeforeUpdate(Cancel As Integer)
>___________________________________________________________
> Dim dbCurrent As Database, Projects As TableDef, Revised As TableDef
> Dim Fields As Fields, i As Byte
> Set dbCurrent = CurrentDb ' Open Database.
> Set Projects = dbCurrent.TableDefs("Projects") ' Open Table.
> Set Revised = dbCurrent.TableDefs("Revised") ' Open Table.
> For i = 0 To Projects.Fields.Count - 1
> Revised.Fields(i).Value = Projects.Fields(i).Value
> Next i
>End Sub
>***************************************************************************
*
>It gets an error saying the Revised.Fields(i).Value and
>Projects.Fields(i).Value are invalid
>Is there someone that can help a new convert? Thanks
>George Schlotthauer
George,
Try the following code (note that I have put it in the AfterUpdate, rather
than BeforeUpdate event - I think this is a better place for it.)
Private Sub Form_AfterUpdate()
On Error GoTo Err_Form_AfterUpdate
Dim dbCurrent As Database
Dim rsClone As Recordset
Dim rsRevised As Recordset
Dim fld As Field
Set dbCurrent = CurrentDb()
Set rsRevised = dbCurrent.OpenRecordset("Revised")
Set rsClone = Me.RecordsetClone
rsClone.Bookmark = Me.Bookmark
rsRevised.AddNew
For Each fld In rsClone.Fields
rsRevised.Fields(fld.Name).Value = fld.Value
Next fld
rsRevised.Update
Exit_Form_AfterUpdate:
rsRevised.Close
Exit Sub
Err_Form_AfterUpdate:
Beep
MsgBox "There was an error."
Resume Exit_Form_AfterUpdate
End Sub
It assumes that there are fields in 'Revised' with the same names and types
as the form's recordset.
I hope that helps.
Simon Lewis
DXM Software Engineering
