Updating 2 tables with 1 form - Access 2003

[Guest]

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!

 

[Ken Snell]

What is the SQL statement of the query that is the form's recordsource?

--
Ken Snell
<MS ACCESS MVP>

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!

 

[John Vinson]

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?

 

[Ken Snell]

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.aspx?scid=kb;en-us;209571&Product=acc

ACC2000: Status Bar Displays "Recordset Not Updateable" Message When You Try
to Update a Linked Table
http://support.microsoft.com/default.aspx?scid=kb;en-us;304179&Product=acc

INFO: Troubleshooting Errors That May Occur When You Update Data in Queries
and in Forms
http://support.microsoft.com/default.aspx?scid=kb;en-us;328828&Product=acc

ACC: Update Query Based on Totals Query Fails
http://support.microsoft.com/default.aspx?scid=kb;en-us;116142&Product=acc

--
Ken Snell
<MS ACCESS MVP>

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];