Showing data at a point in time

[Guest]

I have two subforms that have data that has to carry forward from one month
two another. The main form is linked to all the subforms by Location and
Month
Location is a 3 string numeric character, month is formatted as JUN06.

On the subforms and reports that need data carried forward I just linked
them by
Location only. The user also needs the ability to delete records out of these
subforms if they are not needed for the current month. I created a yes no
field with a check box, using no as the criteria so the records would appear
until the box was checked. This work fine going forward but My dilemma is
once the record is marked as deleted, I need to be able to still have the
record show in the forms and reports for the prior months the originally
appeared. Any ideas? The records are still in the table with a check in the
yes no field.

 

[pietlinden]

You do realize that what you are suggesting doesn't make any sense,
right? Why not just filter the subform's rowsource so that you only
get current data? Set the rowsource to something like

SELECT...
FROM Sometable
WHERE Month(SaleDate)=Month(Date) And Year(SaleDate)=Year(Date);

or something like that. All this deleting records you need is just
plain crazy.

 

[Nick 'The database Guy']

Hi Joe,

As pietlin correctly suggested you can not delete records and still
have them appear in a report. However I would suggest that you create
another boolean (yes/no) field that controls whether a record is
displayed in the sub-form.

Good luck

 

[John Spencer]

Instead of a yes/no field to mark the records as "inactive", I would use a
date field. With the date field you can make the record "inactive" as of a
specific date.

You can make it simple for your user's to set the date field by using code
that will set the date to the last day of the prior month when they click on
an "Inactive" button.

 

[Guest]

John,
Basically I sort of did what you suggest. I created a timestamp field in the
table that is created when the user clicks the yes no box. The idea being
that the record is inactive after that date. I have another question. The
user still has to be able to use the data as "active" for prior months. If he
runs the form/report for May, I need to return all records in the table
entered through May except records deleted in May or before but no records
entered after May. My table has a MonYr field for date entered and a date
deleted field (inactive). My subform is on a main form that is linked by
MONYR and location. I think I need to return the records for the "Header"
MonYr by selecting all records with a date entered that matches the header
date(Main form MonYr) and before that are active as of the end of that MONYR.
How can I write this query based on the MONYR the user selects(which appears