CODEWITHSUNDEEP
ms-accessms-access-2007vba
Matt Hall
Matt Hall

Reputation: 2412

Switching a record to edit/read-only between users

I'm in the designing phase of a new database and want to try doing something a little more advanced (for me at least).

I want to share a table called "Jobs". This will essentially be a list of Jobs and the status of those jobs.

The way our organisation is structured means that 2 different departments will essentially share this job, so I'm going to set this table up so that editing each record on the front end is shared between the 2 departments (each department will complete certain fields applicable to them in the record).

If "Department A" edit the record and finish their part, I'd like them to basically "pass" the record to Department B, i.e. they click a button and the record becomes read-only for Department A and editable only for Department B. Department B can then do their part with the record. I'd also like the option for Department A to "pass" the record back to Department B for more editing (Department B validate Department A's work, so need to do this when there's an issue).

What's a fairly straight-forward method of achieving this? I've not ever done record-level read/write access before.

My guess would be to create a column called "Editable" and have this button put say "1" in this field against the record when it's needing to be locked for a particular user. Then I'm guessing some VBA on the form that looks at this field for the record and changes the form's textbox editing properties accordingly?

Upvotes: 1

Views: 730

Answers (1)

Renaud Bompuis
Renaud Bompuis

Reputation: 16796

From what I understand, there are basically 3 stages to editing a record:

  • 1: In Progress
    Department A is currently editing the record. They are not finished with it and it's not ready to be passed to someone else.

  • 2: To be completed
    Department A entered what they could. The record is not completed and needs to be completed and reviewed by Department B.

  • 3: Locked
    Department A has completed the edit and all data should be locked.

Logic table for enabling/disabling editing of data on the form could look something like:

+----------+---------------+---------------+---------------+               
| User     | 1:In Progress |    2:TBC      |   3:Locked    |
+----------+---------------+---------------+---------------+
| Dept A   |    Enable     |    Disable    |    Disable    |
+----------+---------------+---------------+---------------+
| Dept B   |    Disable    |    Enable     |    Disable    |
+----------+---------------+---------------+---------------+

From there, we see that the controls should always be disabled except for Dept A when the status is In Progress or Dept B when the status is To Be Completed.

To implement something like this, you could simply add an EditStatus field to your data table with these statuses, then in the OnCurrent event of your form, lock/unlock the controls as required.

For instance:

Private Sub Form_Current()
    Dim controlLock As Boolean
    controlLock = ((currentUserDepartment = "A") And (EditStatus = 1)) _
                  Or _
                  ((currentUserDepartment = "B") And (EditStatus = 2))

    ' Now we can enable/disable çontrols '
    ' Alternatively, use the Locked property instead of Enabled '
    txtEditJobName.Enabled = controlLock
    txtEditJobDate.Enabled = controlLock
    '... etc ... '
End Sub

If the whole form needs to be locked instead of individual controls, it's even simpler:

Private Sub Form_Current()

    Me.AllowEdits = ((currentUserDepartment = "A") And (EditStatus = 1)) _
                    Or _
                    ((currentUserDepartment = "B") And (EditStatus = 2))
End Sub

I'll let you figure out the details, and adapt it to your real situation, but you should get the jest of it from all that.

Upvotes: 1

Related Questions