Getting "record is deleted" msg on Insert statement with left join

[Doug Goodenough]

Hi,

I am trying to build a query which will combine records from 3 tables.

DBO_RA (master table)
DBO_IRO (optional Child table)
DBO_RA_Corp_Panel_Rvw (optional child table)

Rows in these tables are related by the RA_ID column. Since related rows in
the two child tables may not exist I coded the from statement using left
joins. When I run the query with a RA_ID that exists in the first child
table the insert statement works fine. When I run it with an RA_ID that
exists in the 2nd child table I get a message saying record is deleted. I
then tried swapping the position of the two child tables in the from
statement and then the problem occured again for the child table in the 2nd
part of the left join so it does not appear to be a data issue.

Here is the sql statement.....

INSERT INTO dbo_RA_HISTORY ( RA_ID, RN_Deter, RN_Rvwr_ID, RA_Type_ID,
RN_Rmk, Dt_RN_Deter, Dt_RA_Submit_to_MD, Dt_RA_Submit_to_IRO,
Involved_RN_Ind, IRO_Reviewer, IRO_Solution_Rmk, IRO_Determination,
Dt_Recd_from_IRO, APPEAL_COORD_ID, MD_ID, ADMINISTRATOR_ID, Dt_of_Invitation,
Member_Attend_Ind, Rep_Attend_Ind, Attorney_Attend_Ind,
Representative_Description, Solution_Rmk, Determntn_ID, Dt_Response,
Dt_LstUpd, UserID_LstUpd )
SELECT dbo_RA.RA_ID, dbo_RA.RN_Deter, dbo_RA.RN_Rvwr_ID, dbo_RA.RA_Type_ID,
dbo_RA.RN_Rmk, dbo_RA.Dt_RN_Deter, dbo_RA.Dt_RA_Submit_to_MD,
dbo_RA.Dt_RA_Submit_to_IRO, dbo_RA.Involved_RN_Ind, dbo_iro.IRO_Reviewer,
dbo_iro.IRO_Solution_Rmk, dbo_iro.IRO_Determination,
dbo_iro.Dt_Recd_from_IRO, dbo_RA_Corp_Panel_Rvw.APPEAL_COORD_ID,
dbo_RA_Corp_Panel_Rvw.MD_ID, dbo_RA_Corp_Panel_Rvw.ADMINISTRATOR_ID,
dbo_RA_Corp_Panel_Rvw.Dt_of_Invitation,
dbo_RA_Corp_Panel_Rvw.Member_Attend_Ind,
dbo_RA_Corp_Panel_Rvw.Rep_Attend_Ind,
dbo_RA_Corp_Panel_Rvw.Attorney_Attend_Ind,
dbo_RA_Corp_Panel_Rvw.Representative_Description,
dbo_RA_Corp_Panel_Rvw.Solution_Rmk, dbo_RA_Corp_Panel_Rvw.Determntn_ID,
dbo_RA_Corp_Panel_Rvw.Dt_Response, Now() AS Expr1, " " AS user_id
FROM (dbo_RA LEFT JOIN dbo_RA_Corp_Panel_Rvw ON dbo_RA.RA_ID =
dbo_RA_Corp_Panel_Rvw.RA_ID) LEFT JOIN dbo_iro ON dbo_RA.RA_ID = dbo_iro.RA_ID
WHERE dbo_RA.RA_ID=[Forms]![frmRoute_RA]![txtRAID];

Anybody have any ideas what might be causing this?

Thx very much,
Doug