Error 91

[jmark@stayintouch.us]

I have the code below that is supposed to check if a record is locked by any other user, and if so, pop up a warning message. The oncurrent event runs this single line: DisplayLockStatus. It seems to work fine when User1 is in a record and User2 tries to view that same record, but the problem is that every time any user opens the form, it pops up the message even if no one else has access open at all. Split DB. I temporarily put a msgbox to pop up the error code and it is showing 91 on load. Any help is appreciated!


The code for the sub is:

Private Sub DisplayLockStatus()
Dim frm As Form
Dim rst As DAO.Recordset
On Error Resume Next
' We clone the form recordset and sync up to the form current record using the bookmark
Set rst = frm.RecordsetClone
rst.Bookmark = frm.Bookmark

' If the current record is locked, then editing the recordset will produce an error that we will trap
rst.Edit

If Err.Number <> 0 Then
MsgBox ("WARNING! This record is currently being edited by another user and is locked. To make any changes to this record you will need to have exclusive access!")
End If
rst.Close

End Sub

 

[theDBguy]

Hi. Which line is causing the error? For troubleshooting purposes, you might comment out the On Error Resume Next line for now until you fix the problem.

 

[jmark@stayintouch.us]

thanks for the reply. i put a msgbox(err.number) in the code and can see error 91 is being set on form load. research is telling me the i have some Declaration statements maybe set wrong? Is there anything else I can tell or show you that might make it easier for you to help me with this? thanks again for your expertise!

 

[theDBguy]

thanks for the reply. i put a msgbox(err.number) in the code and can see error 91 is being set on form load. research is telling me the i have some Declaration statements maybe set wrong? Is there anything else I can tell or show you that might make it easier for you to help me with this? thanks again for your expertise!

Hi. There are two things that could definitely help us help you. First, tell us the error message for error 91 (not very many of us know them by heart), and second, determine which line is causing the error. I know you said you know (found out) which event caused the code to execute, but we need to know the specific line in the code that's tripping up. You should be able to go to Debug mode to find that out. Also, to help you locate it, temporarily disable any error handler you may have implemented.

 

[June7]

Form object variable is declared but never set. This is possibly cause of error.

Where is this procedure located?

I tried testing. Initiated edit of record then caused another edit via code to simulate another user. The code edit was committed and conflict error message triggered when trying to save the first edit. Changing app recordlocking setting gives same result.

Review https://www.tek-tips.com/viewthread.cfm?qid=153766.

 

[arnelgp]

frm Object is Nothing, meaning you just declare it but never Points to any form.

 

[jmark@stayintouch.us]

thanks to all for your attempts to help. I am an experienced access programmer but certainly not an expert and am unfamiliar with advanced programming such as this. I tried to copy this code and modify it from some other forums, but obviously, I am doing it slightly wrong.

I have a standard form called Transaction, which is an Order Form for products. The oncurrent event of that form has the statement:

DisplayLockStatus


The code for DisplayLockStatus is:

Private Sub DisplayLockStatus()
Dim frm As Form
Dim rst As DAO.Recordset

' We clone the form recordset and sync up to the form current record using the bookmark
Set rst = frm.RecordsetClone
rst.Bookmark = frm.Bookmark
' If the current record is locked, then editing the recordset will produce an error that we will trap
rst.Edit

'I added this to see what error was occurring when the form was loaded. The code is
MsgBox (Err.Number)

If Err.Number <> 0 Then
MsgBox ("WARNING! This record is currently being edited by another user and is locked. To make any changes to this record you will need to have exclusive access!")
End If
rst.Close
End Sub

When the form is first loaded, it displays "Run time error '91'. Object variable or With Block variable not set." If I click "END", the form does not load. If I click "DEBUG", it takes me to this line as the culprit:

Set rst = frm.RecordsetClone

Does this help anyone with more information? Thanks again to everyone!

 

[The_Doc_Man]

Going to all this trouble? If you are planning to modify the contents of a record from a bound form by diddling with the .RecordsetClone, you are working too hard. You have the field already available to you without the need of mucking about with the cloned recordset. Me.field-name is all you need.

If you were worried about interference between two users, look at this link:

Locking Table in Multi User - Microsoft: Access Modules (VBA Coding)

I have multi users (on different machines on the network) accessing a certain table to read a record. I want to lock the table when 1 user reads the table, so the other user gets a message that the table is locked, and will not be able to read that record until the table is released. Is there a...

www.tek-tips.com

 

[jmark@stayintouch.us]

Doc_man, I have seen many of your very educated responses to people, so I know you know what you are talking about. I'm not nearly as advanced with Access, so would you be willing to modify my code to allow me to accomplish my goal. Originally I wanted to have a dedicated field on the form that would say either "Unlocked" or "This record is locked by USERXX on ComputerXX", but that seems too complicated for me, so I am settling for a simple "Locked" or "Unlocked" status indication. I have a split FE and BE on a single PC, that 4 users access, all via RDP, with their own logins and their own identical copies of the FE, all pointing to same single BE. Can you offer me any more help? Thank you.

 

[theDBguy]

When the form is first loaded, it displays "Run time error '91'. Object variable or With Block variable not set." If I click "END", the form does not load. If I click "DEBUG", it takes me to this line as the culprit:

Set rst = frm.RecordsetClone

Does this help anyone with more information? Thanks again to everyone!

Looks like @June7 and @arnelgp were correct. Try changing that line into this.

Set rst = Me.RecordsetClone

Hope that helps...

PS. Guess you'll also have to change the following line as well.

rst.Bookmark = Me.Bookmark

 

[jmark@stayintouch.us]

Yes, DBguy! That did it! Thanks so much!

 

[theDBguy]

Yes, DBguy! That did it! Thanks so much!

Glad to hear you got it sorted out. Good luck with your project.