Append a single record in a subform

[martinmike2]

Hello, I would like to append a single record from one table to an
archive table. This record is being displayed in a subform. I am
trying to execute the query with a command button.

1) Would it be easier to place the command button on the subform
instead of the main form?

2) I have a working query but it won't execute using docmd.openquery
-- the code I have in the Click property of the command button is:
docmd.openquery ("qryDEPHIST"). When I go to load the main form I get
a warning saying that the expression is typed incorrectly, but this is
the correct syntax for this function is it not?

 

[Klatuu]

First, the OpenQuery is not the correct method to use for Action queries.
Second, If you are simply moving a record to a different table to archive
it, a better method is to add a Yes/No field to your main table with a
default value of True. You could name it something like ActiveRecord. The
to archive it, just make the value of the field False. Then use queries
filtered on that field to show only active records.

There are many advantages to this. Not the least of which is OMG!!!! I just
deleted that record and I really didn't mean too! Now what do I do?

 

[martinmike2]

First, the OpenQuery is not the correct method to use for Action queries.
Second, If you are simply moving a record to a different table to archive
it, a better method is to add a Yes/No field to your main table with a
default value of True.  You could name it something like ActiveRecord.  The
to archive it, just make the value of the field False.  Then use queries
filtered on that field to show only active records.

There are many advantages to this.  Not the least of which is OMG!!!! Ijust
deleted that record and I really didn't mean too! Now what do I do?
--
Dave Hargis, Microsoft Access MVP







- Show quoted text -

I'll take a look into it, thank you Dave.

 

[Klatuu]

Good. If you decide there is an overriding reason to use an archive table,
post back and I can help with that.

 

[martinmike2]

Good.  If you decide there is an overriding reason to use an archive table,
post back and I can help with that.
--
Dave Hargis, Microsoft Access MVP






- Show quoted text -

I am creating a manpower db to track personnel on assignment. We need
the archive table to show a history of assignments.

 

[Klatuu]

Actually, that would be a case for keeping it in the current table with an
archived flag. You would then be able to filter for a specific person and
see all his records regardless of whether they are current or archived.

But, the way to accomplish what you want to do would be to use an append
query to add the record to the archive table, then delete the current record
in the subform's recordset.

 

[martinmike2]

Actually, that would be a case for keeping it in the current table with an
archived flag.  You would then be able to filter for a specific person and
see all his records regardless of whether they are current or archived.

But, the way to accomplish what you want to do would be to use an append
query to add the record to the archive table, then delete the current record
in the subform's recordset.
--
Dave Hargis, Microsoft Access MVP






- Show quoted text -

Worked!! thanks Dave.