Share via

Microsoft Access: Record is deleted (Error 3167)

Manish Malhotra 20 Reputation points
2025-12-17T12:28:42.4566667+00:00

Hi!

Suddenly while clicking a cmd button ion form to open another data entry form, I was trapped in an event Record is deleted. (Error 3167).

How to over come this error. Kindly help me

Microsoft 365 and Office | Access | Other | Windows
0 comments

Answer accepted by question author

George Hepworth 22,855 Reputation points Volunteer Moderator
2025-12-29T13:35:04.5766667+00:00

To be clear, this problem only occurs with one record, i.e. the record which has as its Primary Key value 5. Is that correct?

The query you show retrieves records from a table, VEHICLES, and from another query called OWT_BaseQry.

In that source query, it appears that you are retrieving records from still other tables.

In the absence of data to analyze, my first assumption would be that the vehicle ID you indicate, i.e. the one with ID 5, does not appear in the sub query, OWT_BaseQry due to the way it is constructed. That results in errors in this query when you try to concatenate values from it.

The error message itself might be misleading in that regard. It could mean that a recordset isn't pointing to the expected record, and that would correlate with the record not being returned in the sub query.

Open the query called OWT_BaseQry outside of the form. Inspect it to be sure it includes the record which is causing this error. My guess is that it probably will not appear in that query. If it does, then we'll need to dig deeper.

Was this answer helpful?

1 person found this answer helpful.

3 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Manish Malhotra 20 Reputation points
    2025-12-29T08:44:32.81+00:00

    Greetings

    Pardon me for the delay

    I furnish bellow the required particulars.

    The CMD button has embedded macro to open a form "OWT_UpdateCurrentEntryUnitAndTeamWise"

    Tne SQL of the query which fetches data to the form is

     SELECT VEHICLES.PdlTeam_id, OWT_BaseQry.OdoWaTblID, OWT_BaseQry.VehID, OWT_BaseQry.ServiceMode, [vehno] & "  - " & [make] & " -  " & [attachedto] & " - " & [team] AS VEHNumber, OWT_BaseQry.ReadTakenOn, OWT_BaseQry.OdoReadOnDt, OWT_BaseQry.OdoChangedOn, OWT_BaseQry.OdoRemarks, OWT_BaseQry.DrAv, OWT_BaseQry.FuelAllot, OWT_BaseQry.EOChange, OWT_BaseQry.AFChange, OWT_BaseQry.OFChange, OWT_BaseQry.DFChange, OWT_BaseQry.GoilChange, OWT_BaseQry.CoOilChange, OWT_BaseQry.SMOH, OWT_BaseQry.ALTOH, OWT_BaseQry.FCPending, OWT_BaseQry.TyreIssued, OWT_BaseQry.NoOfTyres, OWT_BaseQry.BattIssued, OWT_BaseQry.NoOfBatt, OWT_BaseQry.Accident, OWT_BaseQry.TyrebattReq, OWT_BaseQry.MHCost, OWT_BaseQry.ConsumablesCost, OWT_BaseQry.SparesCost, OWT_BaseQry.QandLcost, OWT_BaseQry.TyresCost, OWT_BaseQry.BatteryCost, OWT_BaseQry.OTHExp, Nz([MHCost],0)+Nz([ConsumablesCost],0)+Nz([SparesCost],0)+Nz([QandLcost],0)+Nz([TyresCost],0)+Nz([BatteryCost],0)+Nz([OTHExp],0) AS ServiceExp, OWT_BaseQry.RepairsRecomend
FROM OWT_BaseQry INNER JOIN VEHICLES ON OWT_BaseQry.VehID = VEHICLES.VehIDPo
WHERE (((VEHICLES.PdlTeam_id)=[Forms]![WelcomeScreen]![Cbo_SelectTeamAtWS]))
ORDER BY OWT_BaseQry.ReadTakenOn DESC

    PdlTeam -- is the table which has 33 records (But ID-5 created this problem) bound to the Calling form (, and the called form is to open to show records pertaining to the PdlTeam-ID 5

    NB: The source query of the opened from has been assigned with form criteria.

    This is the Calling form

    Callingform

    This is the CMD button To click Names as Entry Form

    CMD button Named EntryForm

    Kindly help me to come out of this trap.

    With kind regards.

    Was this answer helpful?

    0 comments
  2. George Hepworth 22,855 Reputation points Volunteer Moderator
    2025-12-17T13:28:28.76+00:00

    We need more details to diagnose the problem.

    Show us the actual code behind the command button.

    Also, details about the form itself will be helpful. Is it bound to a table or to a query? Show us the SQL from the query is it is the latter.

    Which record had the focus in the calling form when you clicked on the command button?

    What is the relationship between the tables bound to the calling form and and the called form--the one you open with the command button?

    In words, describe how this is supposed to work.

    Thank you.

    Was this answer helpful?

  3. AI answer

    2025-12-17T12:28:56.3166667+00:00

    The error message "Record is deleted. (Error 3167)" typically occurs when you attempt to access a record that has been deleted from a table or when the record pointer is not positioned on a valid record. Here are some steps you can take to troubleshoot and resolve this issue:

    1. Check Recordset State: Ensure that your Recordset is open and positioned on a valid record before attempting to access it. You can check the state of the Recordset using the State property.
    2. Error Handling: Implement error handling in your code to gracefully manage situations where the record might have been deleted. You can use the On Error statement in VBA to catch the error and take appropriate action.
    3. Requery the Form: If the data has changed (e.g., records have been deleted), you might need to requery the form to refresh the data displayed. You can do this by calling the Requery method on the form.
    4. Check for Deleted Records: If you are using a DAO Recordset, ensure that you are not trying to access a record after it has been deleted. If you attempt to access a deleted record, you will receive the error 3167.
    5. Debugging: Use debugging techniques to step through your code and identify where the error occurs. This can help you determine if the issue is with the record access or if it is related to the data entry form itself.

    By following these steps, you should be able to identify the cause of the error and implement a solution to avoid it in the future.

    References:

    AI-generated content may be incorrect. Read our transparency notes for more information.

    Was this answer helpful?

Your answer

Answers can be marked as 'Accepted' by the question author and 'Recommended' by moderators, which helps users know the answer solved the author's problem.