Command Button to update tables

[DCitti]

I have designed a database that contains 5 tables. Occaisionally we need to transfer data from one table to another.

I've created a main form that contains a tab that contains a sub-form for each table. Each sub-form contains a check-box to send the 'marked' records to the specified table.
Example:

Mark record to be transfered to obsolete
Mark record to be transfered to Lab

I've place a command button on each sub-form called "Update Table." what I'd like to have happen is for the marked records to be transfered to the correct table then deleted from the original table when the button is clicked on the active sub-form.

Any suggestions on how to accomplish this?

I've tried using append and delete queries and a macro to run them - however, adding the second transfer option as limited my success.

I've considered created ADO objects but I'm not a VBA programmer so figuring this out is confusing.

Any assistance would be greatly appreciated.
Thank you!

 

[BarkerD]

You will need to use a transaction to accomplish this. This will keep your database in a consistent state. If an update fails between the BeginTrans and UpdateTrans, you can RollBack the updates.

On Error GOTO UpdateError

Dim wrkDefault as Workspace
dim rst as DAO.recordset
dim rst2 as DAO.recordset
dim db as DAO.database
dim bolUpdatePending as Boolean
dim strSQL as string

bolUpdatepending=false

Set wrkDefault=DBEngine.Workspaces(0)
Set db=CurrentDB

'Populate a recordset with records that are marked as obsolete.
strSQL="SELECT * FROM TableName WHERE [ObsoleteFIeldName] = True ;"
set rst=db.OpenRecordset(strSQL)
Set rst2=db.OpenRecordset("TableNametoAppend")

rst.MoveFirst

'Loop through the recordset, and append and delete as necessary. The changes will not happen until the CommitTrans is called.

If rst.EOF Then
'recordset not populated.
Else
wrkDefault.BeginTrans
bolUpdatePending=True

Do Until rst.EOF

rst2.AddNew
rst2![AppendtableFieldName1]=me.[ControlNameofData]
...Continue for each field name
rst2.Update

'Delete the record.
rst.Delete

rst.MoveNext
Loop

wrkDefault.CommitTrans

Endif
UpdateError:

If bolUpdatePending=True then
wrkDefault.RollBack
Endif

This is the basic code execution. You may want to add more error checking, and close all open objects.

Duane Barker