Move Data

[Haytham]

Hi All...
I created a query to append and to delete data from my main table to the new history table. It's successful..
I have another headache point by which :
I have a FieldName: MyDate in my main table Date() which stores date of record entry. If MyDate < System Date which is Now() -30 ... i.e. if the data is stored for more than 30 days then it moves to history table.. by this time changes and editing can occur. After 30 days, it moves to history table where no one can do any changes.

The code is under CmdSave Button as follows :

' To Insert a record in a history table after one month of System Date...
If Me.Date < Now() - 30 Then
strSQL = "INSERT INTO tblHistory SELECT DISTINCTROW tblStaffData.*,FROM tblStaffData INNER JOIN tblFollowData ON [tblStaffData].[IdNo]=[tblFollowData].[IdNoMain] WHERE (idno = '" & IdNo & "');"
DoCmd.RunSQL strSQL

' To Delete a record from our main table ...
strSQL = "DELETE FROM tblStaffData WHERE (idno = '" & IdNo & "');"
DoCmd.RunSQL strSQL
MsgBox "Record Transfered to History"

The problem is that I have to view all records one by one and when press Save, it check for date and either move data or not which is not practical.

My aim is whenever I press save button, system should check all records and if MyDate is fulfilling the criteria it should follow the procedure etc..

Do I have to use Loop between all the records, and if Yes... How..

Help is highly appreciated.
Thanks

Haytham

 

[RichMorrison]

I would approach the problem this way:

1) create an "Append" query that selects all the records more than 30 days old. The selected records are copied to history with this query.

2) create a "Delete" query that deletes all records more than 30 days old.

In the code behind the cmdSave button, run the Append query then run the Delete query.

That should do it.

RichM

 

[Haytham]

100% right. and that what I did.
But the point is I open my form filtered by expression to a specific record and thereby I want to follow the criteria of Date Difference, but this works only for a single record and not for others.
It's impossible to open all records after 30 days so as to do so...
Any idea...

[This message has been edited by Haytham (edited 05-17-2002).]

 

[RichMorrison]

Sorry, I just don't understand what you are stating or what you are asking.

RichM

 

[Haytham]

Hi Rich,
Ok, my strSQL is of 2 steps:
1. Append Query to append certain records to history table.
2. Delete Query which do the same.

The problem is it appends and deletes a single record and not all the records in my main table..
So, the user has to open the main table record by record and press save button so as to move data to history table ( if date is more than 30 days of entry ).
The point is to append and delete all records if stored in more than 30 days in one click and not by going th' all the records..
Very thankful for you Rich

 

[RichMorrison]

OK, think I got it.

You should create and save a query that appends all records over 30 days old to the History table.

Then create and save a query that deletes all records over 30 days old.

There is no need to build a SQL string in a form or anywhere else.

In the code under cmdSave you just:
DoCmd.OpenQuery "AppendToHistory"
DOCmd.OpenQuery "DeleteOldRecords"

That will copy all records and then delete all records.

RichM

 

[Haytham]

Hi Rich..
Creating 2 queries solved the problem. Thanks a lot.