Simple....I know (1 Viewer)

[Rick]

Hi Gang, I have a field "Status" and 2 fields called "Checkpoint date" One of the checkpoint date fields are in a subform. How can I archive only the closed records with "Checkpoint dates" less than today? (In both main form and subform) Thanks in advance

 

[Fizzio]

You will more than likely need some query configuration or VBA recordset manipulation. When archiving, it depends on whether you want to copy the records to a archived table, move them to an archived table or simply not show them on your form. To copy them, use an append query(ies - depending on how many tables are involved) including the tables with the fields you want to interrogate. In the date fields set the criteria to <Date() and the Status field to True (assuming that a checked box means that the record is closed).

If you want to move the records, you will need to perform an append then delete query(ies).

Easiest is to just not show the records on the form without moving them but if you are planning a large recorset then archiving may be a better option. Archiving gets a little more tricky when there are linked records spanning many tables.

 

[Rick]

Can you show me an example of not showing the records if the following conditions are met:
Status = Complete AND Checkpoint date is < Today

Thanks

 

[Rich]

DoCmd.ApplyFilter "", "[Status]=Yes And [MyDate]<Date()" assuming status is a checkbox

 

[Rick]

Sorry

Sorry I was not clear, The status field is a drop down box that containes: 1-Active
2-Pending
4-On-Hold
5-Closed/Comp
# 5 is the one in question.
Hope this helps. Thanks again

 

[Fizzio]

To rip Rich's Code (as he's not around)

DoCmd.ApplyFilter "", "[Status]=5 And [MyDate]<Date()" 'If Combo bound column is numeric if it is text

DoCmd.ApplyFilter "", "[Status]='Complete' And [MyDate]<Date()"

 

[Rick]

Dumb Again

Sorry Fizzio, Where would I put this bit of code? On the "Form" Property? Thanks

 

[Fizzio]

s'ok

There are a number of ways to filter a form's recordset. Rich's example allows you to apply a filter to the form's recordset from the form itself and likewise to remove the filter if you want to see all the records. An alternative is to set the criteria in a query (that you then subsequently use as the form's recordsource) The advantages of using Rich's example is that you can apply / remove the filter whenever you like - simply by using vb code.
The example given is VBA code. On the On Current property or On Open property of the form, select [event procedure], click the ... button beside it and you are in the VBA editor - paste the code at the cursor and then close that window.

you can then open the form and hopefully your records will be filtered (you should have (filtered) on the record selectors on the bottom to show that the filter is 'on')

OK?