By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,214 Members | 2,072 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,214 IT Pros & Developers. It's quick & easy.

Updatable query - Many to Many

P: n/a
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
Share this question for a faster answer!
Share on Google+

This discussion thread is closed

Replies have been disabled for this discussion.