Update multiple records/forms by making changes to 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
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??????

0
laurasuperior
3/21/2007 11:04:47 PM
access.forms 6864 articles. 2 followers. Follow

4 Replies
974 Views

Similar Articles

[PageSpeed] 21

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.

Jacqueline wrote:
>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??????

0
laurasuperior
3/21/2007 11:48:08 PM
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




Jacqueline wrote:
>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??????

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-forms/200703/1

0
laurasuperior
3/22/2007 4:15:46 PM
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


"laurasuperior via AccessMonster.com" wrote:

> 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
> 
> 
> 
> 
> Jacqueline wrote:
> >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??????
> 
> -- 
> Message posted via AccessMonster.com
> http://www.accessmonster.com/Uwe/Forums.aspx/access-forms/200703/1
> 
> 
0
Utf
3/22/2007 4:45:21 PM
Jacqueline,
I finally got it working. Thank you so much for your time & help!!
Laura

Jacqueline wrote:
>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]
>> >> >> can I have every record in every form updated whenever I make a change to
>> >> >> that base table??????

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-forms/200703/1

0
laurasuperior
3/22/2007 8:59:40 PM
Reply:

Similar Artilces:

Changing Cells and entering data in them
Thanks for the help again. Big thanks to Steve you've got me this far. I went out and bought a book, but it's like reading a foreign language. I was informed today that I can't have message boxes come up. I need to have the code point at the cells and if they are blank turn which ever one is blank red or if both are then both turn red then pause for each cell to be filled in. Cell F14 "Last Name" then automatically go to Cell F16 "First Name" on tab or enter. Basically if Cell F22 or F23 has an X in it, Cells F14 an F16 turn red and cell F14 has the focus...

Pivot Table Question #5
How do I make the row headers show up in front of each row on pivot table instead of just once on the first row of a section? Thanks Try this: Copy the pivot table Do a Paste Special > Values into another sheet Ensure that the top left cell is A1 Run the Sub FillBlanks() below (from MVP Debra D) Sub FillBlanks() 'by Debra Dalgleish 7-Dec-2001 'fill blanks cells with data from above Range("A1").CurrentRegion _ .SpecialCells(xlCellTypeBlanks) _ .FormulaR1C1 = "=R[-1]C" Range("A1").CurrentRegion.Copy Range("A1").PasteS...

Excel corrupts when asking to update vlookups
We are experiencing weird behavior with some Office 2K3 Excel spreadsheets that contain lots of calculations, but no macros. On some pc’s Excel acts normally, on others you get the error. I have a couple of screen shots available. Any help is appreciated. If desired, send your file to my address below. I will only look if: 1. You send a copy of this message on an inserted sheet 2. You give me the newsgroup and the subject line 3. You send a clear explanation of what you want 4. You send before/after examples and expected results. -- Don Gu...

Change position ID in HR
We would like to change the position ID in human resources. Does anyone have a suggestion on this. You would need to do it behind the scenes using a tool like Query Analyzer. -- Charles Allen, MVP "KT" wrote: > We would like to change the position ID in human resources. Does anyone have > a suggestion on this. careful though when you change it on the background as you need to know all the tables that use this position ID or Position Code and change it there too otherwise all the link would be gone and you end up with orphan records that its just the same as creatin...

New Record
I'm using the following in the On Click of a command button. DoCmd.GoToRecord acDataForm, "frmMyForm", acNewRec Anyway I can do this without referenceing the Object (Form) name?? James James, Yes... DoCmd.GoToRecord , , acNewRec God Bless, Mark A. Sam "JamesJ" <jjy@adelphia_darwin.net> wrote in message news:er2loxDPIHA.1208@TK2MSFTNGP03.phx.gbl... > I'm using the following in the On Click of a command button. > > DoCmd.GoToRecord acDataForm, "frmMyForm", acNewRec > > Anyway I can do this without referenceing the Object (Form)...

explanation of codes in Visual Basic when creating User form
Hi, I am trying to create a user form in Visual Basic however I'm trying to teach myself by reading/watching tutorials. (www.contectures.o.ca, etc) A lot of the instructions I am seeing simply give the code rather than explain how to actually write one from scratch. So... I need to know what each 'term' means so I can understand how the codes work. Any help is much appreciated :) One of the first codes is for the Add button Private Sub cmdAdd_Click() Dim iRow As Long Dim ws As Worksheet Set ws = Worksheets("PartsData") What d...

AR invoicing update GL but not receivables
We have an issue where the GL is updated with invoicing activity, but in some cases the receivables side is not. Any piror issues with this, advice on how to figure out the problem, etc? Thanks I have only heard of the opposite happening - AR subledger is updated, but the GL is not. Can you walk through exactly what happens? What type of AR document in what screen? What is the 'On Account' amount? What are the GL distributions? Where are you going to see that the AR subledger is not updated? Where do you see that the GL is updated? -- Victoria Yudin Microsoft MVP - Gre...

print multiple pages on one sheet of paper
I am using mailmerge in Publisher to create placecards for a party we are hosting. The final size of the placecards is 1.5" by 1.5" and we have to print 100 final cards. Publisher gives me the option of printing multiple copies of the same page on one sheet of letter sized paper or one page on one sheet of letter sized paper. What I would like to do, however, is print multiple different pages on one sheet of paper. If I cannot find a solution for this, I will need to print 100 separate pages with a 1.5" square box of copy in the center of each sheet. In page setup, sel...

How to automate increasing the form cache registry/file etc...
I want to roll out a batch file to make a number of tweaks to CRM The body of it would go REGEDIT /S Kerberosefix.reg REGEDIT /S ForceFormreload.reg REGEDIT /S OutlookFix.reg It would also rename OSA.exe to OSA.bad Remove OSA.exe From the startup menu I need help finding a way to use my batch file to increase the Outlook Form cache from the default 4MB to 50 MB.. This makes CRm more stable and faster for communications. I dont want to manually do this, as it time consuming, are my end users would not be reliable in doing it themselves. I also want to make another batch file or button that...

Show date, time & day of week in one cell
Can I show the date, time and day of week in one cell. I have: 09/03/07 8:30 AM in one cell using the format (Format/Cells/Custom): [$-409]mm/dd/yy h:mm AM/PM;@ Excel refuses to accept ddd for Mon or dddd Monday at the end of the format I want it to read: 09/03/07 8:30 AM Monday in 1 cell. I have Excel 2003. One way: mm/dd/yy h:mm AM/PM dddd;@ In article <Xns99B8A3CAF9130pencilunlistedcom@208.49.82.220>, Burp <burp@beep.comINVALID> wrote: > Can I show the date, time and day of week in one cell. > > I have: > 09/03/07 8:30 AM > in one cell using the for...

Security Updates-Uninstall
I was wondering if anyone knows how to backdown security updates? We ran the latest security updates for our servers and now most of our applications won't run properly. Has anyone had this experience before? Can we back these down? Cheers. Control Panel -> Add remove programs ??? "Geoff" wrote: > I was wondering if anyone knows how to backdown security > updates? We ran the latest security updates for our > servers and now most of our applications won't run > properly. Has anyone had this experience before? Can we > back these down? > ...

Default scene is solid blue but only on one account
I have 2 accounts with WLM, on one the default scene is as it should be, a mix of blue and white. But on the other account it is solid blue. If i change the scene in the program it changes to that but any contact windows i open continue to be solid blue by the persons name but above that is the new scene. I've done a repair job on 2 computers but does not fix the problem. Guesses? ...

Business Portal Development
All: I know how to use Rational XDE, VS and the BP SDK to create new entities in BP, but how does one go about chaning labels? For example, if I wanted to change two labels on a requisition mgt screen: Can I just crack open the ASPX page and change the caption property? Thanks, Dwight -- Hi, To edit the lables you can use front page server extensions 2003. "Dwight Specht" wrote: > All: > > I know how to use Rational XDE, VS and the BP SDK to create new entities in > BP, but how does one go about chaning labels? > > For example, if I wanted to ch...

form fields not saving to table
I have created a database with a form and three of the fields are not saving the information to the table. I have checked the row and control sources and now at a loss and thoughts? As you scroll through existing records is the data of the table displayed? Post the SQL of the query or form source. -- Build a little, test a little. "Kim" wrote: > I have created a database with a form and three of the fields are not saving > the information to the table. I have checked the row and control sources and > now at a loss and thoughts? > > ...

Cannot Delete Any Records
We're running CRM 3 Rollup v3 and we have recently found that we cannot delete any records in CRM. We recently migrated over to a Windows 2003 Enterprise install running SQL 2000 Enterprise. Everything else is working without issue for us. For example I am a Sys Admin, and I receive a generic SQL Server error when going to delete an activity? I know that in the past this was possible, any thoughts? Sean; sounds like something went wrong with your migration. you can run a CRM trace or a SQL Profiler trace while performing the deletion to see if you can pin-point the error. Dave Ire...

Table of Contents for each section
I have tried several times to create a TOC for each section of my document. They way it is now I can simply go to the Insert a TOC and it will insert e perfect TOC. All my text has been changed to the appropriate Level and I have no problem there. But I need to seperate the chapters to the start of their respective sections (I have next page breaks inserted at the start of every chapter). I have tried the bookmark method, no luck. I have tried other methods but I think my problem is that I have selected the appropriate fields and changed their levels appropriately. I have not mes...

How do you change the APR?
How do you change the APR in a Credit Card account? Like if you have to change it from 13.250% to 14.240% Depends on the version but (in M2007) go to the CC Account and click on Change Account Settings. Scroll down to Credit Information and click on Change Credit Details. If you want to change the Minimum payment rate/amount as well, click on the Minimum payment amount and a side window opens. -- Regards Bob Peel, Microsoft MVP - Money For UK tips & fixes see http://support.microsoft.com/default.aspx?scid=fh;EN-GB;mny. I do not respond to any emails that I have not specifically ...

After editing example1.xls and click SAVE, the filename changes to AABBEE.xls
After editing example1.xls and click SAVE, the filename changes to AABBEE.xls. The original file still exist and has been updated but the 'funny' filename also have the same content, and editable. The example1.xls is stored in a server and accessed by many people within the company. Everyone accessing to the file will change the filename unknowingly after saving it. ...

Status in table PurchaseOrderEntryDetail
Hello, We're trying to populate serial # into the PurchaseOrderEntryDetail table and run across the column 'Status'. What are the possible values for Status column in this table? Most of the time we see status of '2'. What does that mean? Please help. Many Thanks & Best Regards, Nikki ...

Bulk Import
Hi All. I have a need to import leads (about 20,000 of them) and then I need to update each lead (with data from an outsourced Telemarketing supplier) every 2 weeks. I will create new attributes every 2 weeks for the lead to match the data coming back from my supplier but would I be able to "update" my existing 20,000 leads and not re-import as I would like to keep the activity history. Any idea how I could do this? Thanks in advance. ...

COUNTIF MULTIPLE CRITERIA #2
To count if Col A=aa and Col B=bb and Col C=cc. Must meet all situation. Thanks will A Try this: =SUMPRODUCT(--(A1:A10="aa"),--(B1:B10="bb"),--(C1:C10="cc")) Adjust range references to suit your situation Does that help? *********** Regards, Ron "will A" wrote: > To count if Col A=aa and Col B=bb and Col C=cc. Must meet all situation. > Thanks > will A =SUMPRODUCT(--(A1:A1000="aa"),--(B1:B1000="bb"),--(C1:c1000="cc")) SUMPRODUCT does not work on a complete column, just a defined range, and all ranges must b...

Automatically copy input from one cell to another
After I enter a value in one cell, how can I have it automatically enter it into another cell, within the same worksheet, or into a different worksheet. Thanks, Tom picktr@wowway.co -- Message posted from http://www.ExcelForum.com If you enter the value in A1 of sheet1, put this in the other cell in sheet1: =A1 or in another worksheet: =Sheet1!A1 In article <picktr.15c6uy@excelforum-nospam.com>, picktr <<picktr.15c6uy@excelforum-nospam.com>> wrote: > After I enter a value in one cell, how can > I have it automatically enter it into another cell...

changing budget period -how?
WE have set up a budget in Money 2002. But my paycheck gets deposited a few days before the 1st of each month. Thus, when I run the buget report for that month, it shows that I have no income yet for that month but plenty of expenses already. Is there any way to ge the budget period to change from the calendar month to a period starting on the date of my paycheck deposit and running until the day before the next deposit? No. "CLP" <anonymous@discussions.microsoft.com> wrote in message news:25c1701c46118$d169fcb0$a501280a@phx.gbl... > WE have set up a budget in Mo...

form base authentication #2
This is killing me. I'm implementing form base authentication. I have SSL and everything working but for one problem when use won't to get to owa they type mail.domain.com which then defaults to http://mail.domain.com. the problem I'm having is that FBA need it to default to https not http. How can I get it to default to HTTPS with out telling the uses to type https? I am implementing this on a FE/BE environment wayne .. What I do is change the http port on the main site to something like 8080, and then create a new site that listens on 80. For that site, instead of...

Query by Form-TJ
I need to do a Query by Form based on multiple selections from combo boxes on a form using Access 2000 I need to bring back the appropriate records in a subform. On Mon, 31 May 2010 07:08:01 -0700, ambushsinger <ambushsinger@discussions.microsoft.com> wrote: Query by Form can't do that. It can only select records in the current form view. After that, you can potentially open a new form with that subset of records in a subform. -Tom. Microsoft Access MVP >I need to do a Query by Form based on multiple selections from combo boxes on >a form using Access 200...