Archive Record

[Ciji]

Issue:
I have a table with trailer# , exit date, etc..
Once an exit date is entered I would like for the record to be moved to
another table or deleted all together.
I know that this moving data to another table is a no no usually but my
reasoning behind this...We could end up having duplicate trailer
numbers and I have a form with a command button that pulls up "Find"
and the person searchs the Trailer number and enters in data. I would
like the record to be archived, moved or deleted so that when they
search trailers there are never duplicates and it finds the only one.
Any suggestions.... Note: pretty new to VBA

 

[Steve Schapel]

Ciji,

Regading your comment "pretty new to VBA", the focus of this newsgroupo
is Macros, which in Access are unrelated to VBA.

You can make an Append Query, based on the main table, to write the
selected record to the archive table. You can then make a Delete Query
to remove this record from the main table. To automate this process
using a macro, you can use two OpenQuery actions, one for each of these
action queries.

 

[Ciji]

Additional information:
Sorry about the VBA comment... New to Access all together so chose an
access group didn't know that it was only for macros. My back
Can you or someone else expalain in a little more detail... I would
need the macro to run when the exit trailer field is updated... should
I... or can I attach the macro to the field and choose an event in that
field and have it run when someone updates that field in the form...
Thank you,

 

[Ciji]

Additional information:
Sorry about the VBA comment... New to Access all together so chose an
access group didn't know that it was only for macros. My back
Can you or someone else expalain in a little more detail... I would
need the macro to run when the exit trailer field is updated... should
I... or can I attach the macro to the field and choose an event in that
field and have it run when someone updates that field in the form...
Thank you,

 

[Steve Schapel]

Ciji,

Theoretically, yes you could. In practice, I don't think this is the
best. I would recommend you to use the After Update event of the Form
itself, as distinct from any event associated with a specific control on
the form. Or, it may be appropriate to use the Close event of the
Form... it is not clear so far whether this is a Single view or
Continuous view form, or whether you would be updating more than one
record in one session. If so, you could just go through and enter/edit
your data, and then have your archiving process happen at the end...
don't worry, ther queries will just process those records with an entry
in the ExitDate field in your table. Hope that makes sense.

 

[Ciji]

Thanks Steve,
I chose the "on close" option on the form. It is a single view form and
updates only one table. The queries are working fine! Thanks again... I
am going to search around in access and see if I can turn off the
warnings so that the users don't have to click yes for the events to
happen.

 

[Ciji]

Hey Steve,
I was able to find out how to turn off the warnings in macro...
SetWarnings off
Append Query
Delete Query
SetWarnins on

I think I did it right...
Thanks again for all the help

 

[Steve Schapel]

Great, that's excellent Ciji. In fact, it is not necessry in a macro to
put the SetWarnings/Yes at the end - it automatically reverts at the end
of the macro anyway - but does not harm either.

Besrt wishes with the rest of your project.