Query Error Message - Repost from CDMA

[Tom]

This is a repeat of my post in CDMA.

Using AccessXP in 2000 mode.

I have the following tables ---
TblLocation
LocationID
PropertyID
StateID
CountyID
CityID

TblState
StateID
State

A Type 1 relationship exists between StateID in both tables. I created a
query based on both tables that includes all the fields in TblLocation and
the State field in TblState. StateID is joined between the two tables in the
query. If I run the query, enter 1 for PropertyID and then try to close the
query, I get the following error message:
"The Microsoft Jet Database Engine can not find a record in the table
'TblState' with key matching field(s) 'StateID'.
When I click OK this message appears:
"You can not save the record at this time".
I get the same behaviour if I delete the relatinship between the tables and
just rely on the join in the query.
Why do I get this error message?

Thanks,

Tom

 

[tina]

are you using a Lookup field in TblLocation? if so, suggest you don't.
other than that, can you post the complete SQL statement so we can look at
it?

 

[Tom]

Tina, Thank you for your response.

I am not using a lookup field. Here's the SQL:
SELECT TblLocation.LocationID, TblLocation.PropertyID, TblLocation.StateID
FROM TblState INNER JOIN TblLocation ON TblState.StateID =
TblLocation.StateID;

Tom

 

[tina]

try

SELECT TblLocation.LocationID, TblLocation.PropertyID, TblLocation.StateID
FROM TblState LEFT JOIN TblLocation ON TblState.StateID =
TblLocation.StateID;

hth

 

[Tom]

The Right Join worked rather than the Left Join.

Thanks, Tina!!

 

[tina]

oh yes, duh - didn't read the SQL statement too carefully, did i? <g>
glad it got you on the "right" path (pun intended) anyway.