Unable to delete record due to record lock (1 Viewer)

[John Sh]

I have a form that displays an image depending on the data input to two combo boxes.
A small table is created with the image location info and multiple images can be scrolled through.
This all works fine until the data is changed.
Before adding new data to the table I am attempting to delete the old records. The error indicates all current records are locked.
The locking protocol is "edited"
Apart from adding some data from the table into bound text boxes, there is no interaction with the data.
A) why are the records locked? and
B) How to delete them?
I have tried runsql and db.execute with the same result.
I have filtered the table with the current data and this works until previously entered data is reused.
This results in a double up of the filtered data.
On close the table is successfully emptied using runsql.

 

[arnelgp]

If there is a "pencil" icon on your form, it means the form is being edited and
not saved since, try to save the form record to remove the icon, then carry on
with the deletion.

you can force to save the form via vba by using Me.Dirty = False.

 

[John Sh]

me.dirty = false did the trick but apart from coding there is no other interaction with the form.
I had to leave the filter in place, even though the old records were deleted, because it showed "Deleted" in the bound controls.
This is not happening with the filter on.
Once again, I thank you.
John

 

[arnelgp]

you need to Requery the form after deleting the records to remove the "deleted".

 

[DickyP]

A comment in passing. Access doesn't actually lock records but pages - not usually a problem but two or more small records may be using a single page in memory.

 

[arnelgp]

i don't think that is entirely true.
here's what the AI has to say:

In Microsoft Access, the record-locking mechanism can operate at the record level or page level, depending on the chosen setting. A record is locked when a user starts editing it, preventing other users from modifying it before the first user finishes. However, a page of records is locked when a user starts editing any field within a record on that page, and it remains locked until the user moves to another record, according to Microsoft Support.

Elaboration:

• Record-level locking:
  When a user starts editing a record, Access automatically locks that specific record, preventing other users from modifying it until the current user finishes editing and saves their changes or moves to another record.
  
  
• Page-level locking:
  In forms and queries, a page of records is locked as soon as any user begins editing any field within a record on that page. This page remains locked until the user navigates to another record or closes the form or query.
  
  
• No locks:
  Some settings allow for "No Locks," where a record isn't locked until the user saves changes. While this reduces lock contention, it can lead to data conflicts if multiple users simultaneously modify the same record.
  
  
• Edited record:
  With the "Edited record" setting, a record is locked as soon as a user starts editing it, which reduces the likelihood of data conflicts but also increases the time the record is locked for other users.
  
  
• All records:
  This setting locks all records in a table while any form or datasheet that uses that table is open.

 

[The_Doc_Man]

me.dirty = false did the trick but apart from coding there is no other interaction with the form.

Something that many people overlook is that you can dirty a record when you have a "Default Value" property on a given field and that field comes in with - in essence - a blank, 0, or null value contrary to the requirements of the other properties. I'm not saying that is your culprit - but it is something worth double-checking.