I am working on an update record form which at present has a command button that updates the latest record set by marking a field called archieve to true. The user then goes on to entering the latest information using a new form.

As the latest information is a change in a figure, is there a way of opening a new form with the old information contained the 'number field' blank so that the user just enters an updated figure.

Any suggestions are gratefully appreciated.

I am not sure what you are asking. You want the old data displayed but its original field blank? If so, do you need to be able to roll back the old data if the user does not enter new data?

Lemme know.

mmcdonal

To give a clearer picture users need to update sales figures for the same product but on a different date. For example they enter the sales figures in january 06 and need to update the same product figures for Feb 06 etc.

Currently they go to an update form which asks them to archive the current figures. The user then goes to an update form where they enter the product and sales figures. These are then written back to a table called sales.

I was hoping to create a form which has the latest data in for them just to update the sales figures and then post these back to the table as the latest information.

That all depends on the structure of the tables, which I am not getting.

I would have a product table, and a current price and sales table, then do my reports by filtering for month. It sounds like you have one table that has a product with its current price and current sales figures only - basically the query results you want from a more normalized structure.

I would pass the data to the archive table, then using where month = current month - 1, populate the fields with that data in your update form. Then push this back to the Sales table.

Really, Sales should look up to Products, and Product information should not be in the Sales table.

tblProduct
ProductID - PK
ProdDesc - text
other info

tblSales
SalesID - PK
ProductID - FK
CurrentDate - Date (for the Month)
MonthPrice
MonthSales

Then selec the Product, and the sales where currentdate("m") = the current month, etc.

Anyway, what specific code do you need? I would need your field names and table names.

Did that help any?

mmcdonal

Thanks very much - that gives me a clearer idea. The sales data is contained in a table called sales which has the following fields:

SalesID
AccountNumber
Date
BrandID - this is related to a table call brands which gives all the product info, which in turn is related to a table to give the manufacturer.
Sales
Archive

I would like to be able to archive the old info and use the BrandID,AccountNumber of the last entry to create a new entry in the above fields. eg

Old Entry

SalesID(Indexed)
Account Number: 123
Date:01/01/2006
BrandID: 1
Sales: 250
Archive: False this then becomes True after below

New Entry

SalesID(New)
AccountNumber: 123
Date: 01/02/2006
BrandID: 1
Sales 350
Archive: False

So the only information that has changed is the date and the sales figure.

Many thanks

Taraj