Preventing updates to tables not working

  • Thread starter Thread starter Ken
  • Start date Start date
K

Ken

I have a database that has a table, tblDBUpdateInfo, that stores
details about when the database was last updated. One of the fields in
that table is AdminLocked, a yes/no field, used to indicate if someone
else is updating the database.

This table is tied to a form, frmAdminFunctions, that users access to
update the database. By pushing a command button, code is executed to
determine if another user is currently updating the database. This is
done by evaluating the field AdminLocked in the aforementioned table.
If the value is -1, then a message says basically "someone else is
updating...", and the code ends. If not, then the code proceeds to
upload the required data.

Here's the rub... I found that when one person is updating the
database, the value of Adminlocked does not change in the underlying
table, which means anybody else could begin updating the tables while
the process is already underway (causing a big mess).

Below is the code I'm using to change the value of AdminLocked. Is
there something else I should be doing in code to lock tblDBUpdateInfo?

Any help is appreciated!
Thanks, Ken

Private Sub btnUpdate_Click()
'Sets AdminLocked value to -1, preventing database use while tables are
updated.
'Dim all necessary variables

On Error GoTo btnUpdate_Click_Err

If Me.AdminLocked.Value = -1 Then
MsgBox "The database is currently being updated by another user.
Please check back shortly to begin using the database.", vbOKOnly,
"Update In Progress..."
Exit Sub
Else
Me.AdminLocked.Value = -1
End If
'Clears values in all process update flags
'Updates.....
'Updates complete....
Me.AdminLocked.value = 0 'Sets AdminLocked value back to false
End Sub
 
Ok, so I just figured out how to make it work and will share for those
who may find it useful (or amusing).

Even though I was changing the value of the field AdminLocked to -1 in
code, the table itself was never refreshed because I never moved to
another record (it's a one record table), and I never refreshed the
table.

Simple solution.... add Me.Refresh right after I change the value of
AdminLocked. Everything works fine now.
 
Back
Top