Insert query with two left outer joins gives "Record is Deleted" m

Hi,

I have built an insert query to combine data from 3 tables into one table 
based on a key value (RA_ID) on a form.  Two of the from tables may or may 
not have data associated with the main from table.  So I have coded the 
select from statement using left outer joins.   When I run the query with a 
row in the first child table but not in the second it works fine but when I 
run the query with a key value that exists in the 2nd child table but not in 
the first I get a message saying "record is deleted".  Anybody have any ideas 
what is causing this?

Here is the query.........

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 )
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
FROM (dbo_RA LEFT JOIN dbo_iro ON dbo_RA.RA_ID = dbo_iro.RA_ID) LEFT JOIN 
dbo_RA_Corp_Panel_Rvw ON dbo_RA.RA_ID = dbo_RA_Corp_Panel_Rvw.RA_ID
WHERE dbo_RA.RA_ID=[Forms]![frmRoute_RA]![txtRAID];
0
Utf
6/5/2007 8:11:01 PM
access.queries 6343 articles. 1 followers. Follow

0 Replies
788 Views

Similar Articles

[PageSpeed] 24

Reply:

Similar Artilces:

Error 3167 Record is deleted
Error 3167 Record is deleted And Sometimes the main entry form displays a record in the form where all fields are "#Delete" Why do we get this error sometimes (about 2 times a week)? It happens from different users and on different workstations. Also in this application no records are ever deleted and would be difficult for a user to delete a record Environment: Access 2003 (converted from Access 97) XP workstations, jet 4 with SP 2 Split into Front and Back end, both mdb's Large databases, FE, 39m, BE, 140m The main table has a large record length but the system functioned fin...

Record is deleted error
Hello, I have a table called Orders that keeps track of a list of Orders for a week. I'm trying to append to a table called Orders Archive which keeps track for all orders ever received. For some reason, when I try to execute the append query, I get the "record is deleted" message. I'm not sure why its happening, because the query used to work up until a few weeks ago. The two tables are actually SQL Server tables that I'm using with an access front end. Does anyone know what the culprit is behind this message? Thanks! "mattieflo" wrote: > > I ha...

Insert query with two left outer joins gives "Record is Deleted" m
Hi, I have built an insert query to combine data from 3 tables into one table based on a key value (RA_ID) on a form. Two of the from tables may or may not have data associated with the main from table. So I have coded the select from statement using left outer joins. When I run the query with a row in the first child table but not in the second it works fine but when I run the query with a key value that exists in the 2nd child table but not in the first I get a message saying "record is deleted". Anybody have any ideas what is causing this? Here is the query......... ...

Next and Previous won't work after record is deleted
Hi I have a form which allows the user to search for duplicate compnay details and merge and delete records which are duplicates. The merge button opens a new form which does the job of moving all the child records of the duplicate record before allowing the user to delete it. When the user closes this form and returns to the calling form, all the fields display "#deleted". Now, when the user clicks on Next or Previous, I get an error message - Record is deleted, and the next / previous record does not appear. Is there any way round this? Stapes You need to refresh the data in the...

"Record is Deleted" message
Good Morning, I am encountering a rather strange problem with my Access Database. This morning when I try to run a report, I got the message "Record is Deleted" I get the same message when I try to run the query that the report is base on. I have never experience this problem and am at a loss as to how to solve it. Any insight, suggestions or help will be greatly appreciated. Thanks. Joe P.S. I have requested a backup copy from our IT department. I will get the back up but am curious as to what happened and how to avoid it from happening again. well that could depend o...

Record is deleted error when inserting into a table
Hello, I was wondering if anyone has ever had a problem with inserting info into a table. I get a "Record is deleted" error when I try to do this. I have a query setup that is pulling the information from 4 tables and inserting into what I call a History table. I've used insert statements before and never received this. Sometimes the code will insert just fine then other times we'll get the error. Any help is greatly appreciated. Thanks, jsg Look here: http://support.microsoft.com/kb/308630 -- Dorian "Give someone a fish and they eat for a day;...

4.0 Workflow
I am trying to setup a workflow to send an email when an Account record gets deleted. I want to include the name of the Company in the email and who deleted it. The workflow fires, but always has a Status of Waiting because it says "The requested record was not found or you do not have sufficient permissions to view it". The reason this happens, I am assuming, is that the record has already been deleted so data from the record cannot be included in the email. Is there a way I can accomplish what I want? Workflow plugins are asynchronous so by the time you get to the databas...