update issue with SQL

[smk23]

I have the following query:

SELECT a.PatientPhoneID, a.PatientID, a.PhoneID, ph.PhoneID AS PhPhoneID,
ph.PhoneTypeID, ph.PhoneNumber, ph.Extension
FROM Phone AS ph INNER JOIN PatientPhone ON a ph.PhoneID = a.PhoneID;

a.PatientPhoneID is the PK of the PatientPhone table and is autonumber.

ph.PhoneID is the PK of the Phone table and is autonumber.

tables are SQL 2000

This query works 90% of the time, but occasionally will throw an error "the
microsoft jet database cannot find a record in "phone" with key matching
field "PhoneID"

When this error occurs, no further phone numbers can be entered. THe user
has to close the form and reopen. When it reopens, it works fine again for
awhile, and after perhaps 50 phone numbers, starts throwing the error again
and have to close and reopen again.

I've tried everything I can think of, i.e. requering after every entry,
programmatically putting PatientID and phoneID in the PatientPhone table,etc.
Nothing seems to help.
Any suggestions?

 

[Jeff Boyce]

I didn't notice in your SQL statement where you defined "a".

Also, following "ON", what is "a ph.PhoneID"?

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

 

[smk23]

Sorry,
It's PatientPhone AS a ON ph.PhoneId=a.PhoneID

 

[Jeff Boyce]

I'm going to guess that there's a record with the PhoneID missing (i.e., the
table is corrupted). Do you have a backup?

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/