Join Strangeness

[RobUCSD]

The query below is the source of two forms; 1) frmPmhDX 2) frmPmhDXdetails

When the source for the forms is only from tblPmhDx the forms work fine. But
with join the frmPmhDXdetails won't allow me to edit the fields. If I go to
the underlying table, tblPmhDX, it gives the same strange behavior.

What's going on here. As always, your help is greatly appreciated.
*********************************************************

SELECT tblPmhDX.fldPmhDxNo, tblPmhDX.fldPINO, tblPmhDX.fldDxDescription,
tblPmhDX.fldDateAware, tblPmhDX.fldDateResolved, tblPmhDX.fldReasonResolved,
tblPmhDX.fldNotes, tblPMHlookUp.fldICD9code
FROM tblPMHlookUp INNER JOIN tblPmhDX ON tblPMHlookUp.fldDxDescription =
tblPmhDX.fldDxDescription
WHERE (((tblPmhDX.fldPINO)=[Forms]![frmPtDemographicNew]![PINo]));

 

[Michel Walsh]

It seems your table tblPmhDX is not updateable. Is it really a table, or a
(not updateable) query in disguise? or a not-updateable linked table to some
source other than an mdb database?


Vanderghast, Access MVP

 

[RobUCSD]

Thanks for your response. It is updateable when I remove the join. Any ideas?

Thanks, Robert

It seems your table tblPmhDX is not updateable. Is it really a table, or a
(not updateable) query in disguise? or a not-updateable linked table to some
source other than an mdb database?


Vanderghast, Access MVP

The query below is the source of two forms; 1) frmPmhDX 2) frmPmhDXdetails

When the source for the forms is only from tblPmhDx the forms work fine.
But
with join the frmPmhDXdetails won't allow me to edit the fields. If I go
to
the underlying table, tblPmhDX, it gives the same strange behavior.

What's going on here. As always, your help is greatly appreciated.
*********************************************************

SELECT tblPmhDX.fldPmhDxNo, tblPmhDX.fldPINO, tblPmhDX.fldDxDescription,
tblPmhDX.fldDateAware, tblPmhDX.fldDateResolved,
tblPmhDX.fldReasonResolved,
tblPmhDX.fldNotes, tblPMHlookUp.fldICD9code
FROM tblPMHlookUp INNER JOIN tblPmhDX ON tblPMHlookUp.fldDxDescription =
tblPmhDX.fldDxDescription
WHERE (((tblPmhDX.fldPINO)=[Forms]![frmPtDemographicNew]![PINo]));

 

[Stefan Hoffmann]

hi,

Thanks for your response. It is updateable when I remove the join. Any ideas?

Of what data type is your field in the join operation?

mfG
--> stefan <--

 

[RobUCSD]

One field is a unique ID, autonumber

hi,

Of what data type is your field in the join operation?

mfG
--> stefan <--

 

[RobUCSD]

hi, but no msg

hi,

Of what data type is your field in the join operation?

mfG
--> stefan <--

 

[Stefan Hoffmann]

hi Rob,

One field is a unique ID, autonumber

Is it (part of) the primary key?


mfG
--> stefan <--

 

[Michel Walsh]

The join is probably not the main cause of the problem, else no inner join
would, in general.

What did you meant by


"It is updateable when I remove the join"


In the last case, what is the SQL statement you use? is it


-------------------
SELECT tblPmhDX.fldPmhDxNo, tblPmhDX.fldPINO, tblPmhDX.fldDxDescription,
tblPmhDX.fldDateAware, tblPmhDX.fldDateResolved, tblPmhDX.fldReasonResolved,
tblPmhDX.fldNotes, tblPMHlookUp.fldICD9code

FROM tblPMHlookUp , tblPmhDX

WHERE (((tblPmhDX.fldPINO)=[Forms]![frmPtDemographicNew]![PINo]));
------------------


or do you simply meant that you removed tblPMHlookUp? If this is the later,
what do you originally meant by:

"If I go to the underlying table, tblPmhDX, it gives the same
strange behavior"


(I assumed you meant tblPmhDX was producing an error too, when you were
trying to update anything in it).


Also, if there are primary keys, for each tables, are they present in the
SELECT clause? The presence of primary key may help to keep updateability
(and the possibility to append records).



Vanderghast, Access MVP

 

[RobUCSD]

Mike, I'm going to try a few things and see if I can get it to work. If I
can't I'll post back shortly.

Thanks, Rob

The join is probably not the main cause of the problem, else no inner join
would, in general.

What did you meant by


"It is updateable when I remove the join"


In the last case, what is the SQL statement you use? is it


-------------------
SELECT tblPmhDX.fldPmhDxNo, tblPmhDX.fldPINO, tblPmhDX.fldDxDescription,
tblPmhDX.fldDateAware, tblPmhDX.fldDateResolved, tblPmhDX.fldReasonResolved,
tblPmhDX.fldNotes, tblPMHlookUp.fldICD9code

FROM tblPMHlookUp , tblPmhDX

WHERE (((tblPmhDX.fldPINO)=[Forms]![frmPtDemographicNew]![PINo]));
------------------


or do you simply meant that you removed tblPMHlookUp? If this is the later,
what do you originally meant by:

"If I go to the underlying table, tblPmhDX, it gives the same
strange behavior"


(I assumed you meant tblPmhDX was producing an error too, when you were
trying to update anything in it).


Also, if there are primary keys, for each tables, are they present in the
SELECT clause? The presence of primary key may help to keep updateability
(and the possibility to append records).



Vanderghast, Access MVP

One field is a unique ID, autonumber