Unable to edit form - Many-to-Many relationship

CC

Dear,

I have a form that is opened using a sql statement:

Part of the table structure:
-----------------------------------------
TbGeneral:
PK - dsr_id

TbDossierSample:
PK - dsr_sample_id
FK - sample_id
FK - dsr_id

TbCRL
PK - sample_id
----------------------------------------
On the form that is linked to TbGeneral, there is a button that opens
the form linked to TbCRL.
"Dossier" mentioned below is the primary key of TbGeneral (Me!dsr_id)

SQL = "SELECT * FROM TbCRL, TbDossierSample WHERE
(([TbCRL.sample_id]=[TbDossierSample].[sample_id]) AND
([TbDossierSample.dsr_id]=" & Dossier & "))"

Once the form (linked to TbCRL) is opened, records cannot be edited.

I've already read some threads about the issue, explaining the problem
is located at the table structure and saying editable fields are only
possible with a form linked to a single table.

Isn't there any alternative?
(I've tried "Allow.Edits" in VBA without success)
ANY help is most welcome...

Thanks in advance,

CC

Dec 21 '05 #1

salad

CC wrote:

Dear,

I have a form that is opened using a sql statement:

Part of the table structure:
-----------------------------------------
TbGeneral:
PK - dsr_id

TbDossierSample:
PK - dsr_sample_id
FK - sample_id
FK - dsr_id

TbCRL
PK - sample_id
----------------------------------------
On the form that is linked to TbGeneral, there is a button that opens
the form linked to TbCRL.
"Dossier" mentioned below is the primary key of TbGeneral (Me!dsr_id)

SQL = "SELECT * FROM TbCRL, TbDossierSample WHERE
(([TbCRL.sample_id]=[TbDossierSample].[sample_id]) AND
([TbDossierSample.dsr_id]=" & Dossier & "))"
Perhaps you could enter, just under the SQL line
Debug.Print SQL
Now go to the debug window, copy to clipboard the result, and try to
create a query with it. Does it work?

One reason I question your SQL is that you have
SELECT * FROM TbCRL, TbDossierSample
and I'd think you'd want
SELECT TbCRL.* , TbDossierSample.* ...

Also, verify that you don't have the recordsource type set to snapshot
in the form (under the Data tab for the Form's properties). But I tend
to think you SQL is flawed.


Once the form (linked to TbCRL) is opened, records cannot be edited.

I've already read some threads about the issue, explaining the problem
is located at the table structure and saying editable fields are only
possible with a form linked to a single table.

Isn't there any alternative?
(I've tried "Allow.Edits" in VBA without success)
ANY help is most welcome...

Thanks in advance,

CC

Dec 21 '05 #2

Chris2

"CC" <se********@gmail.com> wrote in message
news:11**********************@f14g2000cwb.googlegr oups.com...

Dear,

I have a form that is opened using a sql statement:

Part of the table structure:
-----------------------------------------
TbGeneral:
PK - dsr_id

TbDossierSample:
PK - dsr_sample_id
FK - sample_id
FK - dsr_id

TbCRL
PK - sample_id
----------------------------------------
On the form that is linked to TbGeneral, there is a button that opens the form linked to TbCRL.
"Dossier" mentioned below is the primary key of TbGeneral (Me!dsr_id)

CC,

SELECT *
FROM TbCRL
,TbDossierSample
WHERE (([TbCRL.sample_id] = [TbDossierSample].[sample_id])
AND ([TbDossierSample.dsr_id] = " & Dossier & "))"

Ok, it appears you have a cartesian product, where each row of TbCRL
is joined to every row of TbDossierSample (rows * rows), (the WHERE
is only applied after that).

This is, as far as I know, a non-updateable query.

You must specify an SQL-92 standard join (INNER, RIGHT, LEFT) on
primary key values in order for the query to be updateable (in most
cases, anyway).
Sincerely,

Chris O.

Dec 21 '05 #3

pietlinden

one option might be to create the SQL statement in the query builder
and then open the query and then try to add a record to that... if it
won't, then try modifying from there.

Dec 22 '05 #4

CC

Dear Chris,

I haven't had the time yet to come back to the problem (because of
other priorities).
INNER, RIGHT and LEFT JOINS were new concepts to me.

Having read some of the literature, I can now see the problems related
to
a cartesian product.

For the moment I have tested following:

"SELECT TbDossierSample.sample_id, TbCRL.sample_id FROM TbDossierSample
INNER JOIN TbCRL ON TbCRL.sample_id = TbDossierSample.sample_id WHERE
TbDossierSample.dsr_id='" & dossier &"'"

dossier in the above statement is TbGeneral.dsr_id for the record that
is currently displayed.

However now I even get the desired output for the query... Is it
possible to combine multiple INNER JOIN's ?
Best regards,

Seppe

Jan 9 '06 #5

CC

Dear Chris, Dear all,
Executing following SQL statement as a "query object", returns the
desired results...
It also allows for new entries to be added.

SQL = "SELECT TbDossierSample.sample_id, TbDossierSample.dsr_id FROM
TbDossierSample INNER JOIN TbCRL ON TbDossierSample.sample_id =
TbCRL.sample_id WHERE TbDossierSample.dsr_id=" & Dossier & ""

(where "Dossier" is the primary key of TbGeneral, Dossier =
Form_FrmGeneral.Text142)

However, when retrieving the results fails when using lower mentioned
statement.
Forms![fm_CRL_Main].RecordSource = SQL

I've been struggeling with this for some time now!
Thx in advance,

Seppe

Jan 10 '06 #6