Update a field

[tanya74]

Hi... I am a little confused and hoped that someone here might be able to help me?

I am creating am Access 2010 database to record case notes for patients. Only one user can read the patients details or add case notes to the patients record at any one time. I have created an extra field called LockedBy which will record the user who currently is viewing details or adding case notes for that patient on opening the form. It seem update the LockedBy field for the patients record on the form_load but the form_close sub will not allow me to set the LcokedBy field at all. The error is "You can't assign a value to this object".

The form has the following values set:
Data Entry = No
Allow Additions = No
Allow Deletions = No
Allow Edits = Yes
Allow Filters = Yes
Record Locks = No Lock

Private Sub Form_Close()

        'Set the LockedBy field for Patient's record to be unlocked
        Me.LockedBy = ""
        'Me.Update
                
        'Open the All Patients form
        DoCmd.OpenForm "AllPatientsForm", acNormal
            
End Sub

Private Sub Form_Load()

    If (LockedBy = GetCurrentUserName()) Or (IsNull(LockedBy)) Then
        Me.LockedBy = GetCurrentUserName()
        'Me.Update
        
        SetFormTitles
        
        'Close the All Patients form
        DoCmd.Close acForm, "AllPatientsForm", acSaveNo
        
    Else
        MsgBox "Patient is locked by " + LockedBy, vbExclamation, "Patient Locked"
    End If
    
End Sub

Thanks in advance
Tanya

 

[vbaInet]

You can't set a bound field's value when closing the form object.

Use DoCmd.RunSQL with an UPDATE statement instead.

 

[gemma-the-husky]

the trouble with setting a field, is that you get a program problem - you may find the field doesn't get cleared down - and you need a way of managing that situation.

far better to accept that access can manage itself, and not try and use artificial methods, in my opinion.

i still haven't needed to use anything other than standard no locks (ie optimistic locks)

 

[tanya74]

I didnt think Access would provide the locking functionality that my client requires.

I have a two tables. Patients and Case Notes. One patient can have many Case Notes. The Patients table can not be edited by the users at all. All Patient records are imported. The users can only add Case Notes to a patient's record.

If the user views a patient's record, this record must be locked so no one else can view the patients record or any case notes about that patient.

I assumed that Access would only lock record if updated or created. Is this wrong?

 

[vbaInet]

You can look into the form's RecordLocks property.