If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Updating 2 tables with 1 form - Access 2003
Hello,
I'm using Access 2003 with an Access 2000 database. I've got 1 form which is correctly pulling data from 2 tables. I'm having problems updating both tables with data though. It's only updating table 1 with correct data. Table 2 is getting nothing at all. I'm confused, because as I said earlier, It's pulling data from both tables, but not updating them both. What could I be doing wrong? All replies are MUCH appreciated! |
#2
|
|||
|
|||
Updating 2 tables with 1 form - Access 2003
What is the SQL statement of the query that is the form's recordsource?
-- Ken Snell MS ACCESS MVP "Linger1974" wrote in message ... Hello, I'm using Access 2003 with an Access 2000 database. I've got 1 form which is correctly pulling data from 2 tables. I'm having problems updating both tables with data though. It's only updating table 1 with correct data. Table 2 is getting nothing at all. I'm confused, because as I said earlier, It's pulling data from both tables, but not updating them both. What could I be doing wrong? All replies are MUCH appreciated! |
#3
|
|||
|
|||
Updating 2 tables with 1 form - Access 2003
Hi Ken,
Thanks for responding. I know it's a little long, but here's the query. -------------------------------------------------------------------------------- SELECT [Calibration Record Form].[Instument Make], [Calibration Record Form].[Performed by], [Calibration Record Form].[Date], [Calibration Record Form].[Ratemeter Model], [Calibration Record Form].[Serial Number], [Calibration Record Form].[Detector Model], [Calibration Record Form].[Detector Serial Number], [Calibration Record Form].[Battery Check], [Calibration Record Form].[Background (cpm)], [Calibration Record Form].[High Voltage Check], [Calibration Record Form].[Function Check], [Calibration Record Form].[Visual Check], [As-Found Calibration].[Observed Gross CR (cpm) - (open)], [As-Found Calibration].[Observed Net Count Rate (cpm) - open], [As-Found Calibration].[Acceptance Range (cpm) - (open)], [As-Found Calibration].[Pass/Fail - (open)], [As-Found Calibration].[Observed Gross CR (cpm) -(152)], [As-Found Calibration].[Observed Net Count Rate (cpm) - 152], [As-Found Calibration].[Acceptance Range (cpm) -152], [As-Found Calibration].[Pass/Fail - 152], [As-Found Calibration].[Observed Gross CR (cpm) -047], [As-Found Calibration].[Observed Net Count Rate (cpm) - 047], [As-Found Calibration].[Acceptance Range (cpm) -047], [As-Found Calibration].[Pass/Fail - 047], [As-Found Calibration].[Observed Gross CR (cpm)], [As-Found Calibration].[Observed Net Count Rate (cpm)], [As-Found Calibration].[Acceptance Range (cpm)], [As-Found Calibration].[Pass/Fail], [As-Found Calibration].[Adjustment Notes], [Final Calibration].[Observed Gross CR (cpm) - open], [Final Calibration].[Observed Net Count Rate (cpm) - open], [Final Calibration].[Acceptance Range (cpm) - open], [Final Calibration].[Pass/Fail - open], [Final Calibration].[Observed Gross CR (cpm) - 152], [Final Calibration].[Observed Net Count Rate (cpm) - 152], [Final Calibration].[Acceptance Range (cpm) - 152], [Final Calibration].[Pass/Fail - 152], [Final Calibration].[Observed Gross CR (cpm) - 047], [Final Calibration].[Observed Net Count Rate (cpm) - 047], [Final Calibration].[Acceptance Range (cpm) - 047], [Final Calibration].[Pass/Fail - 047], [Final Calibration].[Observed Gross CR (cpm)], [Final Calibration].[Observed Net Count Rate (cpm)], [Final Calibration].[Acceptance Range (cpm)], [Final Calibration].[Pass/Fail] FROM ([Calibration Record Form] INNER JOIN [Final Calibration] ON [Calibration Record Form].[Serial Number]=[Final Calibration].[Ratemeter Serial Number]) INNER JOIN [As-Found Calibration] ON [Calibration Record Form].[Serial Number]=[As-Found Calibration].[RatemeterSerial Number]; |
#4
|
|||
|
|||
Updating 2 tables with 1 form - Access 2003
On Wed, 26 May 2004 07:41:05 -0700, "Linger1974"
wrote: Hello, I'm using Access 2003 with an Access 2000 database. I've got 1 form which is correctly pulling data from 2 tables. I'm having problems updating both tables with data though. It's only updating table 1 with correct data. Table 2 is getting nothing at all. I'm confused, because as I said earlier, It's pulling data from both tables, but not updating them both. What could I be doing wrong? All replies are MUCH appreciated! The simplest way to update two tables with one form is to use a main form for the "one" side table, with a Subform for the "many" side table. How are the two tables related? John W. Vinson[MVP] Come for live chats every Tuesday and Thursday http://go.compuserve.com/msdevapps?loc=us&access=public |
#5
|
|||
|
|||
Updating 2 tables with 1 form - Access 2003
I'm a bit confused by your statement that one table gets updated but the
other one doesn't. I would expect all or none, assuming that your form does not have a subform and that all the query's fields are used on the form. It may be necessary (if what I've posted below is not the solution) for you to give us more info about your form's setup and what you're doing with the form. I'm guessing that [RatemeterSerial Number] is the primary key in both the [Final Calibration] and the [As-Found Calibration] tables? If yes, your query does not include these fields in the SELECT list, thus ACCESS will not know which records are to be updated in those tables. If you open the query in datasheet view (without using the form), does it let you add records or edit any data? I'm guessing that it won't -- and that this is a nonupdatable query. In order for a query to be updatable, a number of things must be present in (or absent from) the query. See these MS Knowledge Base articles for more info about updatable queries: ACC2000: Cannot Edit or Update Record on a Form http://support.microsoft.com/default...71&Product=acc ACC2000: Status Bar Displays "Recordset Not Updateable" Message When You Try to Update a Linked Table http://support.microsoft.com/default...79&Product=acc INFO: Troubleshooting Errors That May Occur When You Update Data in Queries and in Forms http://support.microsoft.com/default...28&Product=acc ACC: Update Query Based on Totals Query Fails http://support.microsoft.com/default...42&Product=acc -- Ken Snell MS ACCESS MVP "Linger1974" wrote in message ... Hi Ken, Thanks for responding. I know it's a little long, but here's the query. -------------------------------------------------------------------------- ------ SELECT [Calibration Record Form].[Instument Make], [Calibration Record Form].[Performed by], [Calibration Record Form].[Date], [Calibration Record Form].[Ratemeter Model], [Calibration Record Form].[Serial Number], [Calibration Record Form].[Detector Model], [Calibration Record Form].[Detector Serial Number], [Calibration Record Form].[Battery Check], [Calibration Record Form].[Background (cpm)], [Calibration Record Form].[High Voltage Check], [Calibration Record Form].[Function Check], [Calibration Record Form].[Visual Check], [As-Found Calibration].[Observed Gross CR (cpm) - (open)], [As-Found Calibration].[Observed Net Count Rate (cpm) - open], [As-Found Calibration].[Acceptance Range (cpm) - (open)], [As-Found Calibration].[Pass/Fail - (open)], [As-Found Calibration].[Observed Gross CR (cpm) -(152)], [As-Found Calibration].[Observed Net Count Rate (cpm) - 152], [As-Found Calibration].[Acceptance Range (cpm) -152], [As-Found Calibration].[Pass/Fail - 152], [As-Found Calibration].[Observed Gross CR (cpm) -047], [As-Found Calibration].[Observed Net Count Rate (cpm) - 047], [As-Found Calibration].[Acceptance Range (cpm) -047], [As-Found Calibration].[Pass/Fail - 047], [As-Found Calibration].[Observed Gross CR (cpm)], [As-Found Calibration].[Observed Net Count Rate (cpm)], [As-Found Calibration].[Acceptance Range (cpm)], [As-Found Calibration].[Pass/Fail], [As-Found Calibration].[Adjustment Notes], [Final Calibration].[Observed Gross CR (cpm) - open], [Final Calibration].[Observed Net Count Rate (cpm) - open], [Final Calibration].[Acceptance Range (cpm) - open], [Final Calibration].[Pass/Fail - open], [Final Calibration].[Observed Gross CR (cpm) - 152], [Final Calibration].[Observed Net Count Rate (cpm) - 152], [Final Calibration].[Acceptance Range (cpm) - 152], [Final Calibration].[Pass/Fail - 152], [Final Calibration].[Observed Gross CR (cpm) - 047], [Final Calibration].[Observed Net Count Rate (cpm) - 047], [Final Calibration].[Acceptance Range (cpm) - 047], [Final Calibration].[Pass/Fail - 047], [Final Calibration].[Observed Gross CR (cpm)], [Final Calibration].[Observed Net Count Rate (cpm)], [Final Calibration].[Acceptance Range (cpm)], [Final Calibration].[Pass/Fail] FROM ([Calibration Record Form] INNER JOIN [Final Calibration] ON [Calibration Record Form].[Serial Number]=[Final Calibration].[Ratemeter Serial Number]) INNER JOIN [As-Found Calibration] ON [Calibration Record Form].[Serial Number]=[As-Found Calibration].[RatemeterSerial Number]; |
Thread Tools | |
Display Modes | |
|
|