Hi Steven,
Try using this article, from Allen Browne's web site, as a guide:
Archive: Move Records to Another Table
http://allenbrowne.com/ser-37.html
I'm not sure if you want to move records from the CATA table to the
PRODUCTIONS table, or simply copy them. If you want to copy only, then don't
execute the delete query in step 3. This example is intended to move all
fields of a record from one table to another table in an archive database.
You can eliminate the IN clause in step 2 ( "IN ""C:\My
Documents\MyArchive.mdb"" " & _ ) since you need to append records in the
PRODUCTIONS table in the same database.
I think you indicated previously that you want to add some additional
information prior to writing the record to the PRODUCTIONS table. One
approach that might work for you is to bind your form to the CATA table, and
add unbound textboxes to the form for any new fields that you want to add to
the record. Then modify the INSERT INTO and the SELECT portions of the strSql
statement shown in Step 2.
Suggestion: Try the example as written, but without the IN clause and the
delete query in step 3, on your database first. Get practice copying a record
from the CATA table to the PRODUCTIONS table first, without trying to add new
data.
Once you have success with that much, then try incremental modifications of
the INSERT INTO and the SELECT portions of the strSql statement in step 2 to
copy (or move) + add new data. Try adding the data from one new textbox at a
time, such as the unique production number(PRN) that you mentioned previously.
Good Luck,
Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________
:
Hi there
you say in your reply :
You can certainly do what you want using an append query that is created
with VBA code behind a form. It would grab the values from the various
textboxes that you wanted to add to the PRODUCTIONS table, along with any new
data in textboxes that you assigned
I think this is what I am after .. the Access equivilent of the old DBASE
SCATTER and GATHER commands.
Whilst I note your comments on design.. the Database is going to be
converted soon to HTML-DB by our IT section so I have included a wish list of
functionality
But if I can solve this problem it would be a great benefit for the moment.
Thanks for the help so far.. I have downloaded the suggested article etc for
reading.
Steven
