Query Error Message - Repost from CDMA

  • Thread starter Thread starter Tom
  • Start date Start date
T

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
 
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?
 
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
 
try

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

hth
 
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. :)
 
Back
Top