 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Forms Programming / August 2005Update data using form
Hello, I have 2 tables Organization & Quantity (Org & Qty): Org OrgId (PK) Orgname OrgAddr OrgCity OrgState OrgZip ... Qty OrgID (PK) QtyYear (PK) QtyAmount QtyDate QtyLoc Relationship betwen Org.OrgId & Qty.OrgId. There are many quantities to each organization. One quantity per year per organization hence QtryYear is 2002, 2003 etc & QtyAmount is total, QtyDate is date given & QtyLoc is location. I have been building some forms. Typically one form I have is for data entry for the 2005 year plus it displays the current information in the organization table allowing the fields to be updated if necessary. So I display the organization data, the 2004 QtyAmount (just for information) then I would like to be able to enter the 2005 quantity data - QtyAmount, QtyDate & QtyLoc. Unfortunately it seems I show the 2004 QtyAmount but then when I enter quantity data I overwrite 2004 rather than add 2005 ? Thanks Nigel, Not sure if I understand what's happening or the cause, but... I don't think you should use QtyYear as a PK. With multiple Orgs, you'll have multiple Qty records with say.. 2005. Just use a new unique record identifier in your Qty table... ex. [QtyID], just to make each Qty record unique. What you want is to just pull up a Org record on the form, and see all the yearly transactions for that Org in your subform... and be able to add a new record to the sub every year. Qty table.... OrgID (PK) linked to OrgID table) QtyID (PK) just for Qty uniqueness Qty QtyYear QtyAmount QtyDate QtyLoc hth Al Camp Link Org to Qty via the OrgID, and once a year you can add a new record. > Hello, I have 2 tables Organization & Quantity (Org & Qty): > Org [quoted text clipped - 31 lines] > quantity data I overwrite 2004 rather than add 2005 ? > Thanks I was thinking that with OrgId from the Org table in a one to many relationship with OrgId QtyYear in the quantity table there would be uniqueness. 1 ... .... 1.2002 1.2003 1.2004 I display the QtyAmount based on a query of QtyYear > 2003 which gives me the 2004 amount but I now want to add the 2005 data somehow. Thanks > Nigel, > Not sure if I understand what's happening or the cause, but... [quoted text clipped - 55 lines] > > quantity data I overwrite 2004 rather than add 2005 ? > > Thanks The OrgID to OrgID is all you need for "uniqueness". The QtyID is just a Qty transaction identifier. (more of a design issue, and not critical to this problem) While you're doing problem determination, why don't you remove the >2003 filtering, and let the sub records fall as they may. >> > Unfortunately it seems I show the 2004 QtyAmount but then when I enter >> > quantity data I overwrite 2004 rather than add 2005 ? In your subform, do you see a "new" record at the end of the recordset? If not, then I'm assuming that you really editing the 2004 record rather than addin a new 2005 record. Please explain a bit more... Al Camp >I was thinking that with OrgId from the Org table in a one to many > relationship with OrgId QtyYear in the quantity table there would be [quoted text clipped - 70 lines] >> > quantity data I overwrite 2004 rather than add 2005 ? >> > Thanks Hello. I removed the >2003. I think the mistake I was making was I was not putting the quantity data out in a subform. I had it with the organization data in one form. Anyway with a subform for the quantity data, I do see a new record at the end. I tried entering data for 2005 & then it said "You cannot add or change a record because a related record is required in table 'TblOrg'" I was trying to add the 2005 quantity entry for organization 1. > The OrgID to OrgID is all you need for "uniqueness". The QtyID is just a > Qty transaction identifier. (more of a design issue, and not critical to [quoted text clipped - 87 lines] > >> > quantity data I overwrite 2004 rather than add 2005 ? > >> > Thanks Hello again. I got around the error msg when adding the 2005 quantity data in the subform by including OrgId from the quantity table in the form & entering the appropriate OrgId. However, I would rather the user not have to do that & have the OrgId pre filled based on the record we are on at the time. Also be good to fill QtyYear with the current year. In the quantity subform, do I have to show all data for an organization, or is their a way to just show last years & allow entry of this years data ? Thanks > Hello. I removed the >2003. I think the mistake I was making was I was not > putting the quantity data out in a subform. I had it with the organization [quoted text clipped - 94 lines] > > >> > quantity data I overwrite 2004 rather than add 2005 ? > > >> > Thanks |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.