Software

Ask something
Question Locked

Moving selected rows to a new table in ACCESS

0 helpful
6 replies

by bea_acca

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

Hi, I hope someone will be able to help me
with
this. Thank you in advance.

I have a cooking database.
Table1 contains recipe code and name
Table2 contains recipe version and reason for
change to new version
Table3 contains recipe ingredients,amounts
and
cooking steps.

I have a form for Table1 which contains
subform
sfTable2 which contains (at the same time)
subform sfTable3. So when you select a recipe
in main form, sfTable2 shows you all recipe
versions, and when you select a version,
sfTable3 shows you ingredients, process
details and amounts; actually this sfTable3
is a table view form.

What I need to do is:
a) prevent recipe composition modification;
b) allow creation of a new version of the
same recipe and, therefore a new list of
components and amounts; from chosen recipe
version
c) avoid MSACCESS to autosave new recipe (new
records) and make it ask me if I want to save
changes or not.

I've figure it out I can reach this by
selecting a list of ingredients and amounts
from a specific recipe and version and move
records to a new table, work with them in
this new table and, at the end, if needed or
decided, move new recipe version to TABLE3
(which contains ingrediens and amounts for
each recipe in TABLE1 and each recipe version
in TABLE2).

I guess this must be done using VBA
instructions... or not.

6 replies

by bea_acca

Thanks for your help.
What really will help me is if someone could
provide me with the code to do that:
I have a version for my recipe (i.e. version
4), this recipe version has several rows with
different components. I want to modify the
whole recipe and, once modified, decide if I
want to keep modifications. Access updates
every single row every time I modify it, I
want access allows me to modify every single
row in recipe version 4 but I want Access ask
me if I want to keep all changes and have a
new version 4 or if I want to discart all
changes and keep version 4 as it was before
changes, or if I want to use new data from
changes done, for next version, 5.

by tony hopkinson

Try
Create a data entry table.
Move the recipe you want into it.
Write some code to compare the new version with the old, then write some UI to present the user with the choices you wish to allow.
Then action them.

It's a goodly bit of code, and I'm far from sure VBA is the best thing to write it in.

If you had a full development emvironment with ADO access to create the front end, then there are ways to use a lot of built in functionality to do the bulk of it.....

by tony hopkinson

insert into Table2
Select * From Table1 Where something or other.

Assuming Table 1 and 2 have the same structures and Table1 doesn't have an Identity (autonumber column).

If not true then
Insert Into Table2(Column1, Column2 ... )
Select Column2, Column3...) From Table 1 Where ....

will do the job

HtHs

Access calls the above an append query in the query designer.

by bea_acca

Hi, I expected it was easier... I'll try to go your way

This discussion is closed to new replies.