Recordset not updateable

[albycindy]

I have this message when I try to update information in forms based on
queries that have been created from two or three tables.

I have checked Help and searched this forum but can't really find the
answer. I hav checked all the relationships in my database, adjusted a few
but still can't figure out why this is happening?

The tables I have are (not all information):

tblContacts
*pkCRDNumber (one to many with other tables CRDNumber)

tblClaims
pkLynxClaimReference
*CRDNumber

tblProducts
*pkClaimID (one to one relationship with Returns pkClaimID)
*CRDNumber

tblReturns
*pkClaimID
CRDNumber (no relationship)

I wish I could post a screen thing so you get the picture but the ppl here
who can help probably understand.

Also, if this helps, my query is
SELECT tblClaims.LynxClaimReference, tblProducts.ReplacementOrder,
tblProducts.ReplacementReference, tblClaims.CRDNumber,
tblClaims.ReplacementCon, tblClaims.ConsignmentNumber,
tblClaims.DeliveryDate, tblClaims.Product, tblClaims.LossOrDamage,
tblClaims.DamageDescription, tblClaims.NotificationDate,
tblClaims.ClaimLetterSent, tblClaims.ClaimDate, tblClaims.ClaimAccepted,
tblClaims.AmountPaid, tblClaims.CreditNoteDate, tblClaims.Complete
FROM (tblContacts INNER JOIN tblClaims ON tblContacts.pkCRDNumber =
tblClaims.CRDNumber) INNER JOIN tblProducts ON tblContacts.pkCRDNumber =
tblProducts.CRDNumber
WHERE (((tblClaims.Complete)=No));

Any advice appreciated. Cheers

 

[Rick Brandt]

I have this message when I try to update information in forms based on
queries that have been created from two or three tables.

I have checked Help and searched this forum but can't really find the
answer. I hav checked all the relationships in my database, adjusted
a few but still can't figure out why this is happening?

The simple answer is that multi-table queries are often not updateable. Any
time a multi-table query makes it difficult for the Database engine to determine
which table should have the updates applied to it will switch to read only as a
mechanism to prevent data corruption.

Access/Jet is actually one of the most forgiving databases in this regard.
There are many database engines where multi-table queries or views are never
updateable. In Access it varies depending on how the query is structured. In
many cases the design can be tweaked so that edits are again possible. In other
cases this can not be achieved. The more tables you add to the query the less
likely that you will be able to make edits.

Multi-table queries are mostly for processing and reporting, not for use in
forms for data entry.

 

[Sprinks]

I presume you would like to use a multi-table query so that you can display
helpful text information to your users on-screen rather than an arcane code.
The way to do this is through combo boxes and, optionally, unbound textboxes.
For example, I will assume that CRDNumber in tblClaims is a foreign key to
tblContacts. You can bind a combo box to to the field, such that you *store*
the numerical code in the field, but *display* the more meaningful name by
setting the ColumnWidth of the Bound Column to 0".

To display other fields from the same table, include them in the fieldlist
in the combo box' RowSource, and use the Column property, setting an unbound
textbox' ControlSource to:

=YourComboBox.Column(x), where x is the column number, starting from 0.

Hope that helps.

Sprinks