Need to update linked tables using a form created from a query

[KevinS]

I have a BE of Access 97 and a FE of Access 2000. I have linked to the A97
files from the A2000 and created a query that produces the corrent results.
The form I created from the query does NOT let me update the records. The
form will show me the records but I cannot update.

Why?
Thank you for any suggestions.

 

[Dirk Goldgar]

I have a BE of Access 97 and a FE of Access 2000. I have linked to the A97
files from the A2000 and created a query that produces the corrent
results.
The form I created from the query does NOT let me update the records. The
form will show me the records but I cannot update.

Why?

First check to see if the query itself is updatable -- not all queries are.
If you open the query directly as a datasheet, can you update or add
records? If not, you may want to look at the help topic, "When can I update
data from a query?". That may not be the exact title of the topic, but if
you search the help file for that sentence, you should find it.

If your query is a totals query or uses the DISTINCT keyword, it won't be
updatable. but there are other ways of constructing queries that result in
a non-updatable query. If the query isn't updatable and you can't figure
out why, post the SQL here and maybe we can see the problem.

If the query *is* updatable, make sure that your form's AllowAdditions
property is set to Yes and that you aren't explicitly opening it read-only.
Also make sure that the database itself isn't read-only, and that you can
update records directly in the tables.

 

[KevinS]

Query will not let me add new records.

SELECT [Clearance Main Table].ACCYF, [Clearance Main Table].[Caregiver
Types], IntakeMain.KFName, IntakeMain.KLName, [Clearance Main Table].[Child
Clearance], [Clearance Main Table].[FBI Clearance], [Clearance Main
Table].[CIU Clearance], [Clearance Main Table].[Criminal Clearance],
[Clearance Main Table].[Child Clearance Sent], [Clearance Main Table].[FBI
Clearance Sent], [Clearance Main Table].[CIU Clearance sent], [Clearance Main
Table].[Criminal Clearance sent], [Clearance Main Table].[Child Clearance
Received], [Clearance Main Table].[FBI Clearance Received], [Clearance Main
Table].[CIU Clearance Received], [Clearance Main Table].[Criminal Clearance
Received], [Clearance Main Table].[In-Home]
FROM (IntakeChild INNER JOIN IntakeMain ON IntakeChild.CaseID =
IntakeMain.CaseID) INNER JOIN ([Clearance Main Table] INNER JOIN IntakeCYF ON
[Clearance Main Table].ACCYF = IntakeCYF.ACCYF) ON (IntakeCYF.ACCYF =
IntakeChild.ACCYF) AND (IntakeCYF.CaseID = IntakeChild.CaseID) AND
(IntakeMain.CaseID = IntakeCYF.CaseID);

I really don't care to update the Access 97 database; I just want to pull
the key and the names from it so everything is consistent.
Thank you!

 

[Dirk Goldgar]

Query will not let me add new records.

SELECT [Clearance Main Table].ACCYF, [Clearance Main Table].[Caregiver
Types], IntakeMain.KFName, IntakeMain.KLName, [Clearance Main
Table].[Child
Clearance], [Clearance Main Table].[FBI Clearance], [Clearance Main
Table].[CIU Clearance], [Clearance Main Table].[Criminal Clearance],
[Clearance Main Table].[Child Clearance Sent], [Clearance Main Table].[FBI
Clearance Sent], [Clearance Main Table].[CIU Clearance sent], [Clearance
Main
Table].[Criminal Clearance sent], [Clearance Main Table].[Child Clearance
Received], [Clearance Main Table].[FBI Clearance Received], [Clearance
Main
Table].[CIU Clearance Received], [Clearance Main Table].[Criminal
Clearance
Received], [Clearance Main Table].[In-Home]
FROM (IntakeChild INNER JOIN IntakeMain ON IntakeChild.CaseID =
IntakeMain.CaseID) INNER JOIN ([Clearance Main Table] INNER JOIN IntakeCYF
ON
[Clearance Main Table].ACCYF = IntakeCYF.ACCYF) ON (IntakeCYF.ACCYF =
IntakeChild.ACCYF) AND (IntakeCYF.CaseID = IntakeChild.CaseID) AND
(IntakeMain.CaseID = IntakeCYF.CaseID);

I really don't care to update the Access 97 database; I just want to pull
the key and the names from it so everything is consistent.

I don't understand that last sentence, but I suspect that your query may
fall into this category (from the help topic I mentioned, accessible online
via this link:
http://office.microsoft.com/assistance/hfws.aspx?AssetID=HP051880011033):

+ Query based on three or more tables in which there is a
many-to-one-to-many relationship

The article gives this solution:

+ Though you can't update the data in the query directly, you can update
the data in a form or data access page based on the query if the form's
RecordsetType property is set to Dynaset (Inconsistent Updates).

So if this is the problem with your query, you maybe able to fix it on your
form by setting the form's RecordsetType property as described. The
RecordsetType property is on the Data tab of the form's property sheet in
Design View.

 

[KevinS]

Tried changing the record type to no avail. Tried changing the Join
properties with no success.
Thank you for the suggestion though!
--
Is it the times or the Zeitgiest?

Query will not let me add new records.

SELECT [Clearance Main Table].ACCYF, [Clearance Main Table].[Caregiver
Types], IntakeMain.KFName, IntakeMain.KLName, [Clearance Main
Table].[Child
Clearance], [Clearance Main Table].[FBI Clearance], [Clearance Main
Table].[CIU Clearance], [Clearance Main Table].[Criminal Clearance],
[Clearance Main Table].[Child Clearance Sent], [Clearance Main Table].[FBI
Clearance Sent], [Clearance Main Table].[CIU Clearance sent], [Clearance
Main
Table].[Criminal Clearance sent], [Clearance Main Table].[Child Clearance
Received], [Clearance Main Table].[FBI Clearance Received], [Clearance
Main
Table].[CIU Clearance Received], [Clearance Main Table].[Criminal
Clearance
Received], [Clearance Main Table].[In-Home]
FROM (IntakeChild INNER JOIN IntakeMain ON IntakeChild.CaseID =
IntakeMain.CaseID) INNER JOIN ([Clearance Main Table] INNER JOIN IntakeCYF
ON
[Clearance Main Table].ACCYF = IntakeCYF.ACCYF) ON (IntakeCYF.ACCYF =
IntakeChild.ACCYF) AND (IntakeCYF.CaseID = IntakeChild.CaseID) AND
(IntakeMain.CaseID = IntakeCYF.CaseID);

I really don't care to update the Access 97 database; I just want to pull
the key and the names from it so everything is consistent.

I don't understand that last sentence, but I suspect that your query may
fall into this category (from the help topic I mentioned, accessible online
via this link:
http://office.microsoft.com/assistance/hfws.aspx?AssetID=HP051880011033):

+ Query based on three or more tables in which there is a
many-to-one-to-many relationship

The article gives this solution:

+ Though you can't update the data in the query directly, you can update
the data in a form or data access page based on the query if the form's
RecordsetType property is set to Dynaset (Inconsistent Updates).

So if this is the problem with your query, you maybe able to fix it on your
form by setting the form's RecordsetType property as described. The
RecordsetType property is on the Data tab of the form's property sheet in
Design View.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Dirk Goldgar]

Tried changing the record type to no avail. Tried changing the Join
properties with no success.
Thank you for the suggestion though!

Does every participating table have a primary key? Are the join fields all
indexed?