Update multiple records/forms by making changes to one table

[laurasuperior]

What I need to do is have one base table containing all the current
information for raw materials (item #s, cost, etc..) that gets updated every
time we receive one of the items already in the table with new information.
Like if the cost has increased since the last time it was received, I can
change the cost for one specific item, then have it update every record that
one item is used in. I have 2 subforms where that item would be used
repeatedly for different finished products that are made up of that item. How
can I have every record in every form updated whenever I make a change to
that base table??????

 

[Jacqueline]

Where do the subforms you referance obtain their data? Is the item cost
stored in more than one table?

 

[laurasuperior]

So far, they're not obtaining their data from anywhere. Each record in the
main form will have a set of records in the subform, which will be a few of
the items from that base table, but I think I'll have to manually input each
record set because I don't want the subform to pull all the records from that
base table, only a few of the records. So as of now, all the records are
stored in only one table.

Where do the subforms you referance obtain their data? Is the item cost
stored in more than one table?

What I need to do is have one base table containing all the current
information for raw materials (item #s, cost, etc..) that gets updated every

[quoted text clipped - 5 lines]

can I have every record in every form updated whenever I make a change to
that base table??????

 

[Jacqueline]

This is hard not seeing the structure of the DB. First, it does not make
sense that you could have a form field that is not connected back to your
tables somewhere, that is what the forms are for, user interface to your
tables.

What you are describing is a flat tabel with no relationships to any other
tables? If your data is structured correctly,each table related with primary
key or foreign key there is a function within Access that allows for
cascading updates. If your data is stored in multipal tables that are not
related to each other, then you are correct you will need to mannyally update
each and every place the data is stored.

If this is the case, the first thing you sould do is reconstruct the
database correctly. This will take some time and you will need to make good
backups because you are working in a DB already populated. I would look at a
rebuild, it will save you tons of work in the long run.
Good luck

--
Jacqueline

So far, they're not obtaining their data from anywhere. Each record in the
main form will have a set of records in the subform, which will be a few of
the items from that base table, but I think I'll have to manually input each
record set because I don't want the subform to pull all the records from that
base table, only a few of the records. So as of now, all the records are
stored in only one table.

Where do the subforms you referance obtain their data? Is the item cost
stored in more than one table?

What I need to do is have one base table containing all the current
information for raw materials (item #s, cost, etc..) that gets updated every

[quoted text clipped - 5 lines]

can I have every record in every form updated whenever I make a change to
that base table??????

 

[laurasuperior via AccessMonster.com]

I don't think I really explained it very clearly because I think you're
misunderstanding. It is hard when you can't see it. I know I need to do
cascading updates and relate the tables, but now here's the problem I'm left
with (forget everything I originally said!):

Here's some background so you'll get the idea of how it's set up. I have a
table that will have a huge list of all raw materials and information about
each that I need to relate to a raw materials received subform, but that
subform won't be using the raw materials table because the subform will only
have a few of the raw materials listed for each record on its main form. I
don't want all the raw materials in the table displayed on the subform - only
a few for each record. So I've created a separate table for the subform with
only field names (which are exactly the same as the fields in my big raw
materials table) and no data because I'll need to go manually enter each
record since each main form record will have a different set of raw materials.
So I have 2 tables that I want to relate so that when I make changes to the
raw materials table, it will update the raw materials received subform.

Here's where I come across the problem: I figured I needed to create a one-to-
many relationship using cascading updates, but it won't let me because for
some reason, it won't allow me to create the primary keys I want in the raw
materials table. The primary key is the item # & cost combined, which I know
are unique records, but it keeps telling me that they're not. Does it
recognize part of each item # in other item #s and thinks they're duplicates?
Do you know how to get around this problem? All I want to do is create that
primary key and then I can get the relationship & cascading updates I need,
but I'm stuck on that stupid primary key!

Thanks so much for your help Jacqueline!! I really appreciate you taking the
time

Laura

This is hard not seeing the structure of the DB. First, it does not make
sense that you could have a form field that is not connected back to your
tables somewhere, that is what the forms are for, user interface to your
tables.

What you are describing is a flat tabel with no relationships to any other
tables? If your data is structured correctly,each table related with primary
key or foreign key there is a function within Access that allows for
cascading updates. If your data is stored in multipal tables that are not
related to each other, then you are correct you will need to mannyally update
each and every place the data is stored.

If this is the case, the first thing you sould do is reconstruct the
database correctly. This will take some time and you will need to make good
backups because you are working in a DB already populated. I would look at a
rebuild, it will save you tons of work in the long run.
Good luck

So far, they're not obtaining their data from anywhere. Each record in the
main form will have a set of records in the subform, which will be a few of

[quoted text clipped - 10 lines]

 

[Jacqueline]

Laura,
OK, I see the picture a little better now.

The table that houses your item numbers should be a ONE table, correct? The
table housing the raw materials, what is the primary key for each type of raw
material, shouldn't it also be a ONE tabel?

I would then create a third table that holds Material Used. In this table
use an auto number as the primary key just as a unique identifier, then bring
in the Keys from both the ITEMS table and MATERIALS table, this table will be
your MANY table and the table you use to build your subform to show only the
materials used for each item.

Laura, a good rule of thumb when constructing DB, in most cases, if you
change the subject build a new table. I hope I am on the right track
helped a little.
Jacqueline

I don't think I really explained it very clearly because I think you're
misunderstanding. It is hard when you can't see it. I know I need to do
cascading updates and relate the tables, but now here's the problem I'm left
with (forget everything I originally said!):

Here's some background so you'll get the idea of how it's set up. I have a
table that will have a huge list of all raw materials and information about
each that I need to relate to a raw materials received subform, but that
subform won't be using the raw materials table because the subform will only
have a few of the raw materials listed for each record on its main form. I
don't want all the raw materials in the table displayed on the subform - only
a few for each record. So I've created a separate table for the subform with
only field names (which are exactly the same as the fields in my big raw
materials table) and no data because I'll need to go manually enter each
record since each main form record will have a different set of raw materials.
So I have 2 tables that I want to relate so that when I make changes to the
raw materials table, it will update the raw materials received subform.

Here's where I come across the problem: I figured I needed to create a one-to-
many relationship using cascading updates, but it won't let me because for
some reason, it won't allow me to create the primary keys I want in the raw
materials table. The primary key is the item # & cost combined, which I know
are unique records, but it keeps telling me that they're not. Does it
recognize part of each item # in other item #s and thinks they're duplicates?
Do you know how to get around this problem? All I want to do is create that
primary key and then I can get the relationship & cascading updates I need,
but I'm stuck on that stupid primary key!

Thanks so much for your help Jacqueline!! I really appreciate you taking the
time

Laura

This is hard not seeing the structure of the DB. First, it does not make
sense that you could have a form field that is not connected back to your
tables somewhere, that is what the forms are for, user interface to your
tables.

What you are describing is a flat tabel with no relationships to any other
tables? If your data is structured correctly,each table related with primary
key or foreign key there is a function within Access that allows for
cascading updates. If your data is stored in multipal tables that are not
related to each other, then you are correct you will need to mannyally update
each and every place the data is stored.

If this is the case, the first thing you sould do is reconstruct the
database correctly. This will take some time and you will need to make good
backups because you are working in a DB already populated. I would look at a
rebuild, it will save you tons of work in the long run.
Good luck

So far, they're not obtaining their data from anywhere. Each record in the
main form will have a set of records in the subform, which will be a few of

[quoted text clipped - 10 lines]

can I have every record in every form updated whenever I make a change to
that base table??????

 

[laurasuperior via AccessMonster.com]

Jacqueline,
I finally got it working. Thank you so much for your time & help!!
Laura

Laura,
OK, I see the picture a little better now.

The table that houses your item numbers should be a ONE table, correct? The
table housing the raw materials, what is the primary key for each type of raw
material, shouldn't it also be a ONE tabel?

I would then create a third table that holds Material Used. In this table
use an auto number as the primary key just as a unique identifier, then bring
in the Keys from both the ITEMS table and MATERIALS table, this table will be
your MANY table and the table you use to build your subform to show only the
materials used for each item.

Laura, a good rule of thumb when constructing DB, in most cases, if you
change the subject build a new table. I hope I am on the right track
helped a little.
Jacqueline

I don't think I really explained it very clearly because I think you're
misunderstanding. It is hard when you can't see it. I know I need to do

[quoted text clipped - 52 lines]