469,609 Members | 1,682 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,609 developers. It's quick & easy.

Updatable query - Many to Many

CC
Dear all,

Following Many-to-Many table structure is part of my database

-----------------------------------
TbGeneral (dossier information)
PK: dsr_id

TbDossierSample
PK: dsr_sample_id
FK: dsr_id
FK: sample_id

TbCRL (sample information)
PK: sample_id
-----------------------------------

+++ When the Dossier form is open (linked to TbGeneral), a button
exists that should take the user to the related samples (on the sample
form).

Following statement achieves this:

"SELECT * FROM TbCRL, TbDossierSample WHERE
TbCRL.sample_id=TbDossierSample.sample_id AND TbDossierSample.dsr_id="
& Dossier

(Dossier dsr_id: Form_Dossier.Text142)
But then the fields are not updatable.

+++ When I use following SQL statement
SELECT TbCRL.* FROM TbCRL INNER JOIN TbDossierSample ON
TbDossierSample.sample_id = TbCRL.sample_id WHERE
TbDossierSample.dsr_id="& Dossier

It return the correct results when testing as a query object, but not
when implemented with the "RecordSource" command.

I've read it should be possible to retrieve updatable fields with the
above mentioned table structure IF the recordset type for the second
form is set to "Dynaset (Inconsistent Updates)", and the query is build
with JOINS...

However I cannot do so...

ANY advice is welcome!

Thx in advance,

Seppe

Jan 10 '06 #1
0 1274

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

reply views Thread by Mario Zoratti | last post: by
2 posts views Thread by Enterprise | last post: by
reply views Thread by Solution2021 | last post: by
reply views Thread by devrayhaan | last post: by
reply views Thread by gheharukoh7 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.