Solved This recordset is not updateable (1 Viewer)

[TonyB1983]

Have a really simple database:

- a switchboard (that opens when opening the database)
- "frmAllContacts" which is a list of all the names of people in my database, with an "open" button next to each record
- "frmContacts" which opens when you lick on the corresponding "open" button on the "frmAllContacts"

All works, but for some reason I cannot understand, if I have the "frmAllContacts" form open at the same time as the "frmContacts" form, I can't update any records as it tells me "This recordset is not updateable". But if I (manually) close "frmAllContacts" then I can amend the records in frmContacts again. However, if on loading "frmContacts" I put some code to close the form "frmAllContacts" (DoCmd.Close acForm, "frmAllContacts") then it still doesn't allow me to amend records.

I've never run into this issue before and have other databases that seem to work fine in the same way.

There are no queries in the database.

The "open" button next to each record in frmAllContacts links the data using:

Private Sub Command3_Click()
On Error GoTo Err_Command3_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmContacts"

stLinkCriteria = "[Contact ID]=" & Me![Contact ID]
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Command3_Click:
Exit Sub

Err_Command3_Click:
MsgBox Err.Description
Resume Exit_Command3_Click
End Su

Clearly frmAllContacts is somehow locking the records for some reason, when it's open.

Have attached a copy of the database (with data removed).

Any help much appreciated!

Thanks

 

[Pac-Man]

Have a really simple database:

- a switchboard (that opens when opening the database)
- "frmAllContacts" which is a list of all the names of people in my database, with an "open" button next to each record
- "frmContacts" which opens when you lick on the corresponding "open" button on the "frmAllContacts"

All works, but for some reason I cannot understand, if I have the "frmAllContacts" form open at the same time as the "frmContacts" form, I can't update any records as it tells me "This recordset is not updateable". But if I (manually) close "frmAllContacts" then I can amend the records in frmContacts again. However, if on loading "frmContacts" I put some code to close the form "frmAllContacts" (DoCmd.Close acForm, "frmAllContacts") then it still doesn't allow me to amend records.

I've never run into this issue before and have other databases that seem to work fine in the same way.

There are no queries in the database.

The "open" button next to each record in frmAllContacts links the data using:

Private Sub Command3_Click()
On Error GoTo Err_Command3_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmContacts"

stLinkCriteria = "[Contact ID]=" & Me![Contact ID]
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Command3_Click:
Exit Sub

Err_Command3_Click:
MsgBox Err.Description
Resume Exit_Command3_Click
End Su

Clearly frmAllContacts is somehow locking the records for some reason, when it's open.

Have attached a copy of the database (with data removed).

Any help much appreciated!

Thanks

I guess both forms have same table as control source. Why don't you put a close form statement before opening the new form. Try the following code:

Private Sub Command3_Click()
On Error GoTo Err_Command3_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmContacts"

stLinkCriteria = "[Contact ID]=" & Me![Contact ID]
DoCmd.Close acForm, "frmAllContacts" 'to close the frmAllContacts form
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Command3_Click:
Exit Sub

Err_Command3_Click:
MsgBox Err.Description
Resume Exit_Command3_Click
End Sub

And then on close event of frmContacts form or on_click event of close button (if you have one) on the form, you can again open the frmAllContacts form.

PS. Please do use code tags for posting code in your post. It makes it easy to read and copy the code.

 

[TonyB1983]

Thanks, but it's not solved it. Still unable to amend records, even using the code you provided

 

[cheekybuddha]

What are the RecordSource's of each form?

If they are queries, please post the SQL.

 

[MajP]

I do not understand it, but I would delete form all contacts. Somehow that form has opened the table exclusively. I do not think that is a feature that can be set that I know of. If you recreate that form from scratch it will work.

To test this open in reverse order and you will get an exclusive open message. I think this is corruption and would start fresh.

 

[TonyB1983]

Trying to avoid re-creating the form, but I'll give that a go. Thanks.

 

[isladogs]

Like MajP, I initially thought this was due to corruption but that wasn't the case
The problem was that you had locked the First Name / Last Name fields on frmAllContacts
As a result it was impossible to edit that form and as the same recordsource was used in frmContacts, that too became read only

Solution: Change frmAllContacts so both fields are Locked=No

I've made some other changes including:
1. Added Option Explicit to all code modules
2. Removed unnecessary stLinkCriteria filter when opening frmAllContacts

Strongly recommend you use meaningful names instead of Command1, Command3 etc.
Also recommend you don't use spaces in field names e.g. change Contact ID to ContactID

 

[TonyB1983]

Thanks isladogs, that's fixed it

Simply changed Record Locks to "No Locks" under the property sheet.

 

[isladogs]

Congratulations. It took me a while to realise what you had done.

EDIT: Sorry I forgot to mention that I had also changed the form Record Locks from All Records to No Locks
I've edited my previous post accordingly

 

[MajP]

@isladogs ,
Can you explain how this works because I cannot believe I have never run across this? I assumed was at the control level and that is the way MS explains it

The Locked property specifies whether you can edit data in a control in Form view.

I guess that is way overly simplified. It is not locking the control, or even the recordset, but actually the field in the table? I would never have guessed this.

 

[isladogs]

Hi @MajP
In fact, the OP had locked both the First Name / Last Name fields on frmAllContacts AND set RecordLocks to All Records on that form.
In fact it was the latter that was more important but inexplicably I forgot to mention that I changed that as well
So I may have misled you inadvertently

With Record Locks set to all records on the original form fromAllContacts., this meant that the same record was locked on frmContacts as well.

Once that was reset to NoLocks, locking the individual fields on frmAllContacts of course only affects that form.
In other words, the record can be edited on the other form frmContacts

To prevent any further confusion, I've edited my previous two posts.
Thanks for prompting me to clarify my earlier response

 

[MajP]

@isladogs
Thanks it is the recordlock of the form and not the lock of the controls. I build almost exclusively single user databases, and record locking a form is for multiuser databases mainly.

RecordLocks Property - Microsoft Support

support.microsoft.com

Locking a control in fact locks only the control, but the OP locked the records. So the MS explanation is in fact crystal clear

All records in the underlying table or query are locked while the form is open in Form view or Datasheet view, while the report is previewed or printed, or while the query is run. Although users can read the records, no one can edit, add, or delete any records until the form is closed, the report has finished printing, or the query has finished running.

Thanks.

 

[isladogs]

Even in multi-user databases, I never set record locks to all records.
If I want a form to be read only, I change the form settings such as Allow Edits/Allow Additions/Allow Deletions to No.
Alternatively, setting the form RecordsetType to Snapshot has a similar effect.