Results 1 to 2 of 2

Thread: Locked Record

  1. #1
    Join Date
    Apr 2002
    Posts
    5

    Question Unanswered: Locked Record

    I have created a database for a client and was told that it was to be a
    one-user database. Well, you know the next statement ... now they want 3
    people to be able to use the database. (FYI, I have never created a
    database for multiusers. I've done some searching but not finding what I
    want.)

    I have split the database. In Tools, Options, I have set the following:

    Default open mode = Shared
    Default record locking = Edited Record
    Checked Open databases using record-level locking

    And this is some of the code behind one of my forms:

    Private Sub Form_Open(Cancel As Integer)
    If Me.RecordLocks <> 2 Then
    Me.RecordLocks = 2 'lock at the record level
    End If
    End Sub

    Private Sub Form_Dirty(Cancel As Integer)
    Me.cmdSave.Visible = True
    Me.cmdUndo.Visible = True
    Me.cmdAdd.Visible = False
    Me.ComboStudent.Enabled = False
    Me.cmdDelete.Visible = False
    End Sub

    I reset the above in Form_Current.

    In testing to see how my form will react, I opened the database twice and
    tried to edit the same record. I get the Locked Record Indicator, it beeps, it doesn't allow editing but Form_Dirty is activated if they attempt to make a change, so the Save and Undo buttons appear, but the user can't change anything. Access knows it is locked, so why does it think the record is dirty when they attempt to edit? What I really want is for the Save and Undo button not to appear for the locked record. That is their clue that they can do something.

    Is there a way to capture the Locked Record Indicator? Or what can I do so
    the Form_Dirty doesn't activate? I thought it would be a nice feature to
    use a msgbox to tell the 2nd user that someone else is editing the same
    record.

    I tried the following:

    Private Sub Form_Dirty(Cancel As Integer)
    If Me.Dirty Then
    Me.cmdSave.Visible = True
    Me.cmdUndo.Visible = True
    Me.cmdAdd.Visible = False
    Me.ComboStudent.Enabled = False
    Me.cmdDelete.Visible = False
    End If
    End Sub

    This works on the 2nd user but it doesn't fire Form_Dirty on the 1st user.

    Surely there is a way to accomplish this.

    I will have a lot of forms to change so the easiest way I can do this the
    better.

    Thanks in advance for any suggestions/help,
    Debbie

  2. #2
    Join Date
    Apr 2002
    Posts
    5

    Talking

    I finally found what I needed. I found it on the Microsoft Knowledge Base (article 122294 - How to automatically detect if a form is being edited). There were two methods suggested and I chose to use the Dirty Property in an Expression. In summary, here's what worked:

    I created the following function in the module:

    Code:
    Function EditModeChange (F as Form) as Variant
        If F.Dirty then
            F!cmdSave.Visible = True
            F!cmdUndo.Visible = True
            F!cmdAdd.Visible = False
            F!ComboStudent.Enabled = False
            F!cmdDelete.Visible = False
        else
            F!cmdSave.Visible = False
            F!cmdUndo.Visible = False
            F!cmdAdd.Visible = True
            F!ComboStudent.Enabled = True
            F!cmdDelete.Visible = True
        End If
    End Function
    I added a new textbox to my form:

    Name: txtEditModeChange
    Control Source: =[Form].[Dirty] & EditModeChange([Form])
    Visible: No

    In Form_AfterUpdate:

    Code:
    Sub Form_AfterUpdate()
        Me!txtEditModeChange.Requery
    End Sub
    Now, if a second user tries to edit the same record as someone else the Save and Undo buttons don't appear.

    Hope this helps someone else.

    Debbie

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •