Make Adcess move records automatically 1

[Dawnit]

I have two tables. One for current records, another for inactive records. When I enter a termination date in a field of the current records table, is there any way to make Access automatically move that record from the current record table to the inactive record table? Hoping for help. Thanks!

 

[LonnieJohnson]

I would take the following steps.

1. Create an append query that will add a record in the "To" table. In a unique field of your query you will need to reference the same field on the form.

2. Create a delete query that will delete this same record from the "From" table. You will need to reference the unique field again.

3. In the form's before update event write code that would check your date field and then run both queries if need by.

If you are OK with writing SQL, you can use the DoCmd.RunSQL command in the same event instead of creating query objects.

Email me if you need more clarification. ljprodev@yahoo.com
Professional Development
MS Access Applications

 

[gusbrunston]

Are you sure you need two tables? Why not simply add a "status" field and use a check box to mark it "active" or "inactive". Then when you create your recordsets with queries choose only those records with the status you want. Gus Brunston
An old PICKer
padregus@home.com

 

[THoey]

The only reason I can see for the two tables would be it might speed queries up a little bit, but only then if the database gets compacted often... Terry M. Hoey
th3856@txmail.sbc.com
While I don't mind e-mail messages, please post all questions in these forums for the benefit of all members.

 

[DbaseInc]

The best way is to put code under the field. Using the After update have the code run. the Code would be similar to

Field_After_UPdate()
Dim Dbs as database
Dim RstNew, RstArchieve As Recordset
Dim sSQL, Criteria as string
Dim CRecord as string(or Integer based on Id for record)
Crecord=Me(CurrentForm).FieldName
Set Dbs=currentdb
Set RstArchieve =Dbs.OpenRecordset("Table name of Archieve"
RstArchieve.Addnew
RstArchieve!field1=Me.FieldOnForm1
RstArchieve!field2=Me.FieldOnForm2
RstArchieve!field3=Me.FieldOnForm3
RstArchieve!field4=Me.FieldOnForm4
RstArchieve!field5=Me.FieldOnForm5
RstArchieve!field6=Me.FieldOnForm6
RstArchieve!field7=Me.FieldOnForm7
RstArchieve!field8=Me.FieldOnForm8
RstArchieve.Update
DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70

DoCmd.close