This doesn't work

[S. Jackson]

Why does this sql give me an un-updatable record set?

SELECT tblStatus.ClosedDate, tblCaseInfo.CaseId, tblCaseInfo.DHSNo,
tblCaseInfo.CaseName, tblHearings.HrgStart
FROM (tblHearings RIGHT JOIN tblCaseInfo ON tblHearings.CaseId =
tblCaseInfo.CaseId) LEFT JOIN tblStatus ON tblCaseInfo.CaseId =
tblStatus.CaseId
WHERE (((tblStatus.ClosedDate) Is Null));

TIA

 

[Duane Hookom]

I think it's your combination of left and right joins. I assume CaseID is
the primary key in tblCaseInfo. What happens if you make the joins into
INNER JOIN?

 

[S. Jackson]

Yes, CaseId is the primary key - I am in the process of normalizing the db
and I am untrained and unexperienced. I followed PC's instructions below in
"DB Normalization." Now the queries don't work.

How do I make them inner joins? Do I just type that in the SQL view?

 

[Duane Hookom]

I am not sure you will be able to make this editable. I assume tblCaseInfo
is related 1 to many to tblStatus and also 1 to many to tblHearings. This
may never be editable. Is there a reason why you need this to be editable
with all three tables? Generally, you would create a form based on one or
two of the tables with a subform based on the other.

 

[S. Jackson]

Thanks. I was taking a look at why I had the Hearings Table included - not
sure, can't remember. You are right - I shouldn't need it b/c the form uses
subforms.

Thanks for confirming my suspicions.
S. Jackson

 

[rkc]

Why does this sql give me an un-updatable record set?

SELECT tblStatus.ClosedDate, tblCaseInfo.CaseId, tblCaseInfo.DHSNo,
tblCaseInfo.CaseName, tblHearings.HrgStart
FROM (tblHearings RIGHT JOIN tblCaseInfo ON tblHearings.CaseId =
tblCaseInfo.CaseId) LEFT JOIN tblStatus ON tblCaseInfo.CaseId =
tblStatus.CaseId
WHERE (((tblStatus.ClosedDate) Is Null));

Probably because CaseId is not the primary key in all the tables involved.
There's no way to identify the records in tblStatus and tblHearing that
would be updated.

 

[PC Datasheet]

<<I followed PC's instructions below in DB Normalization.>>

Way to go, Shelly!!

You're certainly on the right track now.

Create a main form based on TblCaseInfo and subforms for each of the other
tables.

Steve
PC Datasheet

 

[PC Datasheet]

PS Hope it wasn't the nightmare you thought it was going to be!

Steve

 

[S. Jackson]

Thanks PC! I know I drove everyone crazy - I am almost there! Got all the
queries working (I think), all the forms - now working on the reports. If I
can't figure out how to use DLookup, I'll be back (grin).

Thanks again for all your patience and help.

Shelly

OH, and PS to EVERYONE: My boss called me in this morning and gave me a
Merit Award - one of only 8 given out for all of legal throughout our
agency - kinda makes all the blood, sweat and tears worth it! Thanks
again - without this support I never would have been able to do it. :-D

 

[PC Datasheet]

Shelly,

Congrats on the Merit Award! Way to go.

MyTextFieldName = DLookup("[FieldName]","NameOfTableOrQuery","[OtherFieldName] =
Somevalue")

[FieldName] is a field in the table or query.
SomeValue can be numeric ir string that you hardcode in.

You can get SomeValue from your form. If SomeValue Is numeric:
MyTextFieldName = DLookup("[FieldName]","NameOfTableOrQuery","[OtherFieldName] =
" & Me!NameOfAControOnYourForm)

If SomeValue Is A String:
MyTextFieldName = DLookup("[FieldName]","NameOfTableOrQuery","[OtherFieldName] =
'" & Me!NameOfAControOnYourForm & "'")

That's a space, single quote and double quote after the = sign. At the end is a
single quote enclosed in two double quotes.

Steve