copying data

[jprma]

I am designing an Access 97 database to calculate statistical data on monthly basis.
I have created a form to add data to a table named TABLE1
In this table there are the following fields:
DATE
LASTMONTH
NEWWORK
CLEAREDWORK
PENDINGWORK

The DATE field is used to add the month for which the statistical data is referred to. I will start with January 2001 as the first month.

The LASTMONTH field is used as a reminder for the new month and should show all the data stored the month before (meaning December 2000) into the field PENDINGWORK.

The NEWWORK field is used to add the number of cases we actually receive during a specific month.

The CLEARWORK is used to indicate the work we have processed.

The PENDINGWORK is used to indicate the work we have not been able to process at the end of each month.

I understand that what I am trying to do is adding the same data twice and ACCESS does not like this, but it is important that the form keeps this design.

What I need is some code that whenever I add a new record (meaning data for a new month), the data shown in the field PENDINGWORK of the previous month, is automatically stored into the field LASTMONTH.
Example:

DATE = 12/2000
LASTMONTH = 0
NEWWORK = 10
CLEAREDWORK = 20
PENDINGWORK = 99
New Record

DATE = 01/2001
LASTMONTH = 99 (same as 12/2000's PENDINGWORK)
NEWWORK = 2
CLEAREDWORK = 1
PENDINGWORK = 7

Thanks for your help

 

[ntp]

Create a recordset based on your table.

Dim rst as Recordset
set rst = currentdb.TableDefs("tblYourTable").openrecordset

when you add a new record, search this recordset for the record for the previous month.

Dim intlastMonth as Integer
Dim intlastYear As Integer

intlastMonth = ((Month(me.datefield) -1) mod 12)
intlastYear = iif((intlastmonth = 12), (Year(me.datefield) -1), Year(me.datefield))
rst.findfirst "Month([DATE]) = " & intlastMonth & " AND " & "Year([DATE]) = " & intlastYear

Once you have found it replace the LastMonth value with the pending value.

me.LASTMONTH = rst!PENDING


ntp