Archiving one record at time - Access 2003

[How do you clear a report of data after]

What I would like to do is create a archive table but instead of archiving
all of the table I would like to archive just the record I 'am viewing in the
form view via a command button reason being the record on view has tasks to
compelete and when completed I would like to archive that record.
I had a response but I' am not sure how to go about it it was as follows,
Add a field to your table design called something like CompleteFlag.
Set its default to False.
In your form, in the Before Update event, add some VBA in an Event
Procedure that checks for completeness and sets that flag to True.
Something like (air code):
If Me.MyCheckbox1 = True And Me.MyCheckbox2 = True then
' this record is complete
Me.CompleteFlag = True
End If
Then, change any queries or reports that you would like to ignore the
Completed records. You would add a Where clause, like this:
"Select * from MyTable Where CompleteFlag = False"
This query will return only those records that are not yet "complete".
Is there a way to do it via SQL I know a little about this subject

Regards
George

 

[BruceM]

Is there a compelling need to move the record? Couldn't you just use a
query to exclude it from the recordset?

If a value (CompleteFlag) is dependent entirely on other values there is
probably no reason to store it. If you choose to go that route you will
need to provide a way to clear the Completed flag if, for instance, a check
box is checked in error.

I am puzzled by your question as to whether you can use SQL to return only
incomplete records, since you posted SQL to accomplish that.

A better choice may be to check the individual fields:

SELECT * from MyTable
WHERE Check1 = False
AND Check2 = False

 

[How do you clear a report of data after]

Yes there is compelling need to move the record
On my form i have a main task pane within i six sub tasks on going over a
period of time at the same time i will have other main tasks on going, so
that the user will not get confused i would like to move from view.
My sub tasks are check box's when all sub tasks are complete i like o
archive that record

 

[Philip Herlihy]

What I would like to do is create a archive table but instead of archiving
all of the table I would like to archive just the record I 'am viewing in the
form view via a command button reason being the record on view has tasks to
compelete and when completed I would like to archive that record.
I had a response but I' am not sure how to go about it it was as follows,
Add a field to your table design called something like CompleteFlag.
Set its default to False.
In your form, in the Before Update event, add some VBA in an Event
Procedure that checks for completeness and sets that flag to True.
Something like (air code):
If Me.MyCheckbox1 = True And Me.MyCheckbox2 = True then
' this record is complete
Me.CompleteFlag = True
End If
Then, change any queries or reports that you would like to ignore the
Completed records. You would add a Where clause, like this:
"Select * from MyTable Where CompleteFlag = False"
This query will return only those records that are not yet "complete".
Is there a way to do it via SQL I know a little about this subject

Regards
George

That all sounds perfectly feasible to me, although I wonder if it might
be simpler and more reliable to have a check-box so the user can mark
something as "Complete" manually? No need for VBA, just a checkbox
bound to a field in the table.

You may not even need that. If (like the example above) you had two
existing checkboxes, then your query (underlying your report) need only
check if they were both true.

You can use the query builder to create a query which will test for
multiple conditions - you simply put in more than one criterion on the
same line (note that if you put them on different lines that means OR
rather than AND).

HTH

Phil, London

 

[BruceM]

If you can design a query to select the necessary records to be moved the
same idea can be used to present the user with only the records in which
CompleteFlag is False. Better yet would be to evaluate the individual
fields.

By "compelling reason" I meant in terms of the database. For instance, are
there so many records that performance is degrading? Clearly there is a
business or practical need to exclude certain records from view, but that is
an entirely different matter than physically moving the records to another
table.

If you wish for the recordset to exclude records as soon as they are
completed you will need to requery the recordset each time that happens. If
you go ahead with moving the records, the same requirement holds true.

In any case, the place to start is to devise a query that selects only
completed records (if you are going to go ahead with moving the records) or
one that includes only incomplete records (if you are going to keep all
records, complete and incomplete, in a separate table). It is probably a
matter of using True as the criteria for certain fields in one case, and
False for those same fields in the other.

If you are going to move the records you will need to run an Append query to
add records to the Archive table, then a Delete query to delete the same
records from the current table.

 

[Philip Herlihy]

Yes there is compelling need to move the record
On my form i have a main task pane within i six sub tasks on going over a
period of time at the same time i will have other main tasks on going, so
that the user will not get confused i would like to move from view.
My sub tasks are check box's when all sub tasks are complete i like o
archive that record

Here's what I'd do. I'm envisaging a situation where the form provides
the user with a view of tasks that need some attention, and when (more
than one) issues can be marked as "dealt with" then you'd want to remove
the record from view. Is that right?

I wouldn't move the record, merely remove it from the displayed set.
Otherwise, if you ever want to search across all historical records
you'll be looking at more than one table.

I'd adjust the form's underlying record source (a query) to include
FALSE in the Criteria: lines for all the fields corresponding to the
checkboxes. I'd put them on separate criteria lines, so that a record
will be selected if checkbox1 OR checkbox2 (etc) is unchecked (FALSE).
Then the user will see records where at least one checkbox is unticked.

As the user moves from one record to the next, the On Current event
handler could be made to requery the form, so that the just-completed
records are no longer selected and shown.

On the right lines?

Phil

 

[How do you clear a report of data after]

Hi Philip
Thank you you are on the right lines
How do I clear Sub Tasks with no text in my main task window for instance on
my main task form I have 6 Sub Tasks but within that main task there might
only be 3 sub tasks so therefore user has to tick all 6 check box's before it
will update but I don't think they would remember to do that.

 

[Philip Herlihy]

Hi Philip
Thank you you are on the right lines
How do I clear Sub Tasks with no text in my main task window for instance on
my main task form I have 6 Sub Tasks but within that main task there might
only be 3 sub tasks so therefore user has to tick all 6 check box's before it
will update but I don't think they would remember to do that.

If your table design is right (my hobby-horse) you'd have a table for
Main tasks and a table for Sub tasks (it's sometimes appropriate for
these tables to be one and the same). A Subtask record has a field to
hold the ID/key of its parent Main task.

If your form design is right, you'd have a form displaying main tasks,
and a subform displaying subtasks, and as you move from one Main task to
another, the subform will automatically display the appropriate
subtasks. If you haven't got to this stage yet, then that's what you
should be working on first (assuming I do understand you correctly).

If you're already there, then you're describing a situation in which the
user wants to clear everything (all subtasks) at the Main task level.
Logically, you'd want a button on the Main form which would have the
effect of checking all the boxes (however many records there might be)
in the records displayed in the subform.

In this case you'd probably want the user to see all the boxes ending up
checked, rather than the record disappearing - or maybe it's enough to
have a MsgBox pop up saying "Record #### subtasks marked as complete".

What I'd do is to have that "Clear All" command button event handler
fire up a SQL update query which updated (to TRUE) all the relevant data
items in that Main task's Subtask records. The VBA should also requery
the subform (not the main form) to show the ticked boxes, leaving the
Main form not requeried (or the current main task would no longer be
displayed). The On Current event on the main form could be used to
trigger a requery of the Main form, so preventing the just-cleared item
from reappearing.

Do bear in mind this is a "thought experiment" and there may be reasons
why it won't work. There are folk here so smart they have their own
magnetic fields, but I'm not one of them...

HTH

Phil

 

[How do you clear a report of data after]

Thank You very much for help

If your table design is right (my hobby-horse) you'd have a table for
Main tasks and a table for Sub tasks (it's sometimes appropriate for
these tables to be one and the same). A Subtask record has a field to
hold the ID/key of its parent Main task.

If your form design is right, you'd have a form displaying main tasks,
and a subform displaying subtasks, and as you move from one Main task to
another, the subform will automatically display the appropriate
subtasks. If you haven't got to this stage yet, then that's what you
should be working on first (assuming I do understand you correctly).

If you're already there, then you're describing a situation in which the
user wants to clear everything (all subtasks) at the Main task level.
Logically, you'd want a button on the Main form which would have the
effect of checking all the boxes (however many records there might be)
in the records displayed in the subform.

In this case you'd probably want the user to see all the boxes ending up
checked, rather than the record disappearing - or maybe it's enough to
have a MsgBox pop up saying "Record #### subtasks marked as complete".

What I'd do is to have that "Clear All" command button event handler
fire up a SQL update query which updated (to TRUE) all the relevant data
items in that Main task's Subtask records. The VBA should also requery
the subform (not the main form) to show the ticked boxes, leaving the
Main form not requeried (or the current main task would no longer be
displayed). The On Current event on the main form could be used to
trigger a requery of the Main form, so preventing the just-cleared item
from reappearing.

Do bear in mind this is a "thought experiment" and there may be reasons
why it won't work. There are folk here so smart they have their own
magnetic fields, but I'm not one of them...

HTH

Phil