By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,649 Members | 1,808 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,649 IT Pros & Developers. It's quick & easy.

Record Locking Issue

P: n/a
I'm having issues with updates being blocked due to some sort of
record locking issue. The error does not occur consistently, so I've
had a hard time nailing it down. It does happen enough to cause major
problems for my users though as they are working in this all day. I've
been pounding my head against the wall for the last week trying to get
this figured out.

BACKGROUND
I have an Access 03 DB that is split with the BE on a network drive
and the FE distributed out amongst client machines. I'm working with a
continuous form that I have set up to work like a questionnaire with
'test questions' on the left, and the 'test results' fields on the
right side. All forms are set up as Dynaset.

I have a parent form called TestHeader and a subform in it called
ValForm

The user selects a 'Validation Form' they wish to use in the parent
form, which then populates the sub form with all the test scenarios
they need to run along with blank fields for them to type in the
results of those tests. If the particular Test Case had been partially
or fully tested before, the previously completed test results will be
populated for them with the uncompleted portion left blank. This all
works just fine.

My problem is that I need to record the user who wrote or modified
each and every one of those test scenarios (about 200 per Validation
Form) as one user may start a Validation, and another finish it. I had
a method in place that worked well at first, but has started
encountering problems.

Right now, I have a small module that runs on the AFTER UPDATE event
of each of the 3 different test results fields (tier, network, and
results). During the after update event, the name of the user and the
date are captured and written to two separate fields (txtModBy, and
txtModDate) in the same record (overwriting if necessary).

This works fine about 49 out of 50 times. However, every once in
awhile users get a message indicating that the record is locked. It's
hard to reproduce it, and it never happens when stepping through the
code, so I'm thinking this has to be an issue with timing.

I tried putting in a 'me.dirty = false' statement into the field after
update to see if that would help. Again, the code works properly while
stepping through it, but during runtime it now will always produce an
error indicating that I 'cannot assign a value to this object'.

One thing to note that may be of importance is that I manually handle
tabbing through keydown/mousedown. I have some unique conditional
formatting that I use that prevents the normal tab function from
working adequately.

So, the sequence of events is as follows:

1) Field Keydown (DoCmd.GoToRecord , , acNext if its tab key)
2) Field After Update (ModBy field is updated)
3) Form Before Update event (Foreign Keys are manually inserted)
4) Resume rest of Field Keydown module.

The 'record locked' bug occurs on step 2. It won't allow me to update
the ModBy fields because the record is locked. If I step through it,
it works fine. Why is this presenting errors during runtime? Any of
you have a better idea on how to get that ModBy field to not lock up
on me?

Thanks in advance for any advice any of you guys may be able to offer.

Code is as follows:

***********KEYDOWN EVENT**************
Private Sub txtTestResults_KeyDown(KeyCode As Integer, Shift As
Integer)
On Error GoTo ErrorH

'determine if tab or shift tab is being pressed. Move
'the record marker appropriately if it is
If Shift = 1 Then
If KeyCode = 9 Then
DoCmd.GoToRecord , , acPrevious
If [IsLabel] = False Then
Me.txtTestResults.SetFocus
Else
Me.cmbNtwk.SetFocus
End If
End If
Else
If KeyCode = 9 Then
DoCmd.GoToRecord , , acNext
If [IsLabel] = False Then
Me.txtTestResults.SetFocus
Else
Me.cmbNtwk.SetFocus
End If
End If
End If
ErrorExit:
Exit Sub
ErrorH:
Beep
MsgBox Err.Number & " In Keydown event: " & Err.Description
Resume ErrorExit
End Sub

*****FIELD AFTER UPDATE EVENT*****
Private Sub txtTestResults_AfterUpdate()
'pull username from main menu and insert it into ModBy field
Me.ModBy = Forms!frmMainMenu!UserName
Me.ModDate = Now()
End Sub

*****FORM BEFORE UPDATE************
Private Sub Form_BeforeUpdate(Cancel As Integer)
On Error GoTo ErrorH
'If the fields containing Foreign keys are blank, fill them in.
'This is inserted manually because the fields default value
'does not populate when null given the query structure
If IsNull(Me.txtTestCaseID) Or Me.txtTestCaseID = 0 Then
Me.txtTestCaseID = Forms!frmtestheader!txtTestCaseID
Me.txtValFormID = Forms!frmtestheader!cmbVersion
End If

ErrorExit:
Exit Sub
ErrorH:
Beep
MsgBox Err.Num & ": " & err.description
Resume ErrorExit
End Sub
***********END OF CODE******************

Jan 16 '08 #1
Share this question for a faster answer!
Share on Google+

This discussion thread is closed

Replies have been disabled for this discussion.