Copying records

S

Sharon A

This was a message I posted the other day, with the helpful response I
received, but I need help with the other part of the action. Can anyone let
me know whether what I'm trying to do is possible.....

I could do that, but it's the updating to another table from the form I'm
looking at that I'm not sure how to do. Is there a process I can set up that
will copy part of the record over if the user clicks on a button on the form?
 
P

Pieter Wijnen

I'm not quite sure if this it what you want but you could try this:
make a select query against the fields you want to keep (qryTrackFields)
ie SELECT FIELD1, FIELD2,.. FIELDX FROM MYTABLE

Also make an INSERT Query (insTrackFields)
INSERT INTO TrackTable(FIELD1,... FIELDX) VALUES(pFIELD1,..pFIELDX)


Then on your update form make a command button btnSave
and put this code in it's click event:

Sub btnSave_Click()
Dim Db As DAO.Database
Dim sQDef As DAO.QueryDef, iQdef As DAO.QueryDef
Dim Fld As DAO.Field
Dim C As Access.Control
Dim VitalChange As Boolean

Set Db = Access.CurrentDb
Set sQdef = Db.QueryDefs("qryTrackFields")
For Each Fld In sQdef.Fields
Set C = Me.Controls(Fld.Name) ' Assume same name as Field
If Nz(C.Text,vbNullString) <> Nz(C.Value,vbNullString) Then ' Changed!
VitalChange = True
Exit For
End If
Next 'Fld
If Not VitalChange Goto Save_Exit
Set iQDef = Db.QueryDefs("insTrackFields")
For Each Fld In sQdef.Fields
Set C = Me.Controls(Fld.Name) ' Assume same name as Field
iQdef.Parameters("p" & Fld.Name).Value = C.Text ' Old Value before
change
Next ' Fld
iQdef.Execute DAO.DbSeeChanges

Save_Exit:
Set C = Nothing
Set iQdef = Nothing
Set sQDef = Nothing
Set Db = Nothing ' Free Object Pointers manually - never wait for
autocleanup!

Me.Dirty = False ' Save

' Access.DoCmd.Close Access.acForm, Me.Name ' if that's what U want
End Sub


HTH (bar any typos)

Pieter
 
Top