Copying Mainform and Subform data onto new Mainform/Subform

[Hector]

I have a mainform (containing an autonumber id field) that we use as a batch
number and a subform containing detailed records for the batch.

Sometimes we have all the same data in the mainform and subform and wish to
copy everything to a new form (just with a new autonumber i.e. batch number).

Can access do this and how- just some pointers at this stage for me to think
about as I have really gone blank for ideas on this.

At the moment we print out the existing record then manually enter
everything on the new record page (to get the new auto batch number), I feel
for sure there must be a way around this.

 

[John W. Vinson]

I have a mainform (containing an autonumber id field) that we use as a batch
number and a subform containing detailed records for the batch.

Correction: you have TABLES with autonumber fields and data. Data is NOT
stored in forms or subforms; Tables can have autonumbers, forms and subforms
cannot. They're just windows, tools to manage data in tables.

Sometimes we have all the same data in the mainform and subform and wish to
copy everything to a new form (just with a new autonumber i.e. batch number).

Can access do this and how- just some pointers at this stage for me to think
about as I have really gone blank for ideas on this.

You can run an Append query to copy data from one table into another table...
but there's a fundamental problem if you're storing "the same" data in two
different tables, or even (usually) stored in different records in the same
table.

At the moment we print out the existing record then manually enter
everything on the new record page (to get the new auto batch number), I feel
for sure there must be a way around this.

Well, that's certainly not making the best use of Access!!

What are your Tables? How are they related? Are you perhaps storing lots of
repeating data in the table, which might be better stored in two or more
tables in one-to-many relationships?

 

[Hector]

Many thanks John (and for precisely correcting my explanation, yes I do have
underlying tables with data.

I have a table for the package data and another table for the ingredients in
that package. The package table has a package id (autonumber) as the primary
key (we use it as a batch number), the ingredients table has an ingredient id
as a primary key (autonumber) and the package id as the foreign key (number,
it fills in automatically in the subform).
Both have REPEATING information in them (i.e. The package data can be almost
the same for different packages and the same ingredients can occur across
packages).
The only info that is the same which is stored in both tables is the foreign
key field in the ingredients table (it comes from the Package table).

My mainform/subform runs off a select query based on the above two tables.

I need to read up on append queries, so any further info and help you can
offer would be appreciated.

 

[John W. Vinson]

Many thanks John (and for precisely correcting my explanation, yes I do have
underlying tables with data.

I have a table for the package data and another table for the ingredients in
that package. The package table has a package id (autonumber) as the primary
key (we use it as a batch number), the ingredients table has an ingredient id
as a primary key (autonumber) and the package id as the foreign key (number,
it fills in automatically in the subform).
Both have REPEATING information in them (i.e. The package data can be almost
the same for different packages and the same ingredients can occur across
packages).

It sounds like you in fact have a Many to Many relationship: each Package
contains one or more Ingredients; each Ingredient can occur in zero, one, or
many Packages. Is that the case?

If so you should consider an Ingredients table with one record per ingredient,
and a Contents table with fields for PackageID, IngredientID, and any other
fields relevant to *this* ingredient in *this* package (quantity perhaps?).
This would let you enter the ingredient description once and once only, and
just refer to it (using a combo box on the subform bound to Contents).

The only info that is the same which is stored in both tables is the foreign
key field in the ingredients table (it comes from the Package table).

My mainform/subform runs off a select query based on the above two tables.

Well... typically a Form is based on a query of the "One" side table, and the
subform on a (different) query of the "Many" - in your case, Ingredients; in
my suggestion Contents.

I need to read up on append queries, so any further info and help you can
offer would be appreciated.

It's still not clear to me where you're getting the information to be appended
or what it is you need to append.