Re: [MSAccessProfessionals] Record deleted error | Run-time Error 3167

Bill

I still get the same error message.

Jim Wagner

---

From: MSAccessProfessionals@groups.io <MSAccessProfessionals@groups.io> on behalf of Bill Mosca <wrmosca@myself.com>
Sent: Monday, February 21, 2022 11:34 AM
To: MSAccessProfessionals@groups.io <MSAccessProfessionals@groups.io>
Subject: Re: [MSAccessProfessionals] Record deleted error | Run-time Error 3167

 

Jim - What happens if you run the query without the INSERT clause. That is to say, just the SELECT/FROM/WHERE clauses?

That might give you a clue as to what is gong wrong. It could be just a bad record is messing things up.


 
--

Regards,

Bill Mosca, Founder - MS_Access_Professionals

Microsoft Office Access MVP 2010-2016

My nothing-to-do-with-Access blog

http://wrmosca.wordpress.com

On Mon, Feb 21, 2022 at 08:29 AM, Jim Wagner wrote:

Hello all,
Well I have been avoiding this for 4 years since my boss retired. She had created around 24 queries to build one table. It is quite amazing but i never wanted to change it because for one it works and another it is messy. But here I am needing some help. Last friday an error came up during the running of this process that I have never seen before. "Record Deleted".  I have no idea what that means. I changed the query to get rid of the error but I am pretty sure the correct results of the query are going to be wrong. What i changed was to get rid of part of the where statement AND ((ALLSEPARATEDEMPLOYEES.[Person Id]) Is Null)
there are actually 2 queries in the process that fail but they are 2 different error messages. I will tackle one at a time.
Below is the SQL for the record deleted error.

INSERT INTO ALLSEPARATEDEMPLOYEES ( Effdt, [Acn Rsn Cd], [Acn Rsn Ld], [First Nm], [Last Nm], [Person Id], [Jobcode Id], [Jobcode Ld], [Empl Cls Ld], [Dept Id], [Dept Ld], [Empl Stat Cd], TFOutDesignation, [Acn Cd], [Acn Ld], RcdAddedDt, [Service Date], source, [Paygrp Ld] )
SELECT [FinalTermsAndTransfersOut-ALL].Effdt, [FinalTermsAndTransfersOut-ALL].[Acn Rsn Cd], [FinalTermsAndTransfersOut-ALL].[Acn Rsn Ld], [FinalTermsAndTransfersOut-ALL].[First Nm], [FinalTermsAndTransfersOut-ALL].[Last Nm], [FinalTermsAndTransfersOut-ALL].[Person Id], [FinalTermsAndTransfersOut-ALL].[Jobcode Id], [FinalTermsAndTransfersOut-ALL].[Jobcode Ld], [FinalTermsAndTransfersOut-ALL].[Empl Cls Ld], [FinalTermsAndTransfersOut-ALL].[Dept Id], [FinalTermsAndTransfersOut-ALL].[Dept Ld], [FinalTermsAndTransfersOut-ALL].[Empl Stat Cd], [FinalTermsAndTransfersOut-ALL].Transferout, [FinalTermsAndTransfersOut-ALL].[Acn Cd], [FinalTermsAndTransfersOut-ALL].[Acn Ld], Date() AS RcdAddedDt, [FinalTermsAndTransfersOut-ALL].[Service Dt], "PS" AS source, [FinalTermsAndTransfersOut-ALL].[Paygrp Ld]
FROM [FinalTermsAndTransfersOut-ALL] LEFT JOIN ALLSEPARATEDEMPLOYEES ON [FinalTermsAndTransfersOut-ALL].[Person Id] = ALLSEPARATEDEMPLOYEES.[Person Id]
WHERE ((([FinalTermsAndTransfersOut-ALL].Transferout) Like "tran*") AND ((ALLSEPARATEDEMPLOYEES.[Person Id]) Is Null));

I wonder if this is related to a office update because this has been working for 10 years.

Thank You Very much for any help

Jim Wagner