How to avoid "Could not update: currently locked" when adding a new record

[prknsutton]

I have a continuous bound form on a FE stored on desktop of each user PC. The BE sits on a local network drive.
I have the default record locking to "No Locks" and Open Database Using Record Level locking.

All appears to work well, but periodically (several times a day) a user will get the message "Could not update; currently locked" (no error number).
This error seems to only occur as the first character of the new record is entered. There are several fields defaulted to equal to some unbound fields on the same form (see attached screen shot - though it is not clear due to screen resolution, this form is a tab control).

When the error is cleared (hit OK) the first character will appear in the appropriate field, but the Date field (supposed to be defaulted) is always blank - though other defaulted fields remain. Also, while the error message is display, the word "Calculating..." appears in the bottom left of screen. Am I interrupting the saving?

What could possibly be happening? How can I fix and/or capture and handle the error?

(I had some OnCurrent code to sync another subform on the same tab - error appears to continue even with code removed).

Thank you.

[nico5038]

Hmm, found http://bytes.com/topic/access/answer...rrently-locked indicating it might be the access page lock that's causing this trouble.

Personally I don't have this "trouble" as I never insert records using the "New" record row. I force an insert by code and the user has to press OK or Cancel to continue. Thus the [Cancel] is used to delete the inserted row and as an additional feature I create my own unique ID using the DMAX() function.

Is this a possible solution for you too ?

Nic;o)

[prknsutton]

I have researched a lot about page and record locking, and I think I have my setup correct. Rewritting the database to work in an entirely different fashion is a possibility, but I like how this database currently works (when it works - which is 99% of the time) - wondering if I can find a true solution.
Is there something to do with multiple default fields? - given the behaviour I described in the original post.
I have indexed a number of fields - could that be a problem? Can you confict with the database trying to still save the last record, and update indexes etc. while quickly entering a new record?

[TheSmileyCoder]

Goto the form. Add an event procedure to the OnError

Private Sub Form_Error(DataErr As Integer, Response As Integer)
    MsgBox "Error Occured, ErrNr:" & DataErr
End Sub

That will give you the error number. Why Access doesn't always spit out the error number is beyond me....

Lets presume the error is 3041, you can then handle it with code like:

Private Sub Form_Error(DataErr As Integer, Response As Integer)
    If DataErr = 3041 Then
        Response = acDataErrContinue
    End If
End Sub

The acDataErrContinue basicly means I dont care, lets proceed anyways (Probably not recomended). You can also try other methods to handle when this error occurs if you find the "real" problem. Note that all errors that are not 3041 will be shown normally (No worries, your not suppressing any other errors by adding this code.)

[nico5038]

The only other true solution I know is working with a form with only unbound fields and insert-update the data by code. This is however a lot more work than the INSERT before approach I described...

Nic;o)

[prknsutton]

Some good info guys. I will try capturing the error, and see what I can do with it.
I have begun building the completly upbound solution - but agree, it is a lot of work, and not as slick to the end user as my current solution. My current screen filters for date, shift and associate. How do I go about implementing your INSERT with code in this example, with a continuous form.

I would also like to try and understand what is going wrong here. Any one know an anwer to my other questions?
Is there something to do with multiple default fields? - given the behaviour I described in the original post.
I have indexed a number of fields - could that be a problem? Can you confict with the database trying to still save the last record, and update indexes etc. while quickly entering a new record?

[nico5038]

Basically you'll need to add an Insert button on the main form and set the "AllowAdditions" in the subform to "No".

I've attached a working sample based on this principle, but also based on the ObjectAction model. That implies that the datasheet subform is for filtering and sorting and that the New/Update/Delete/etc. actions are handled by a different form.

Notice that the table holds a number and no autonumber.

Nic;o)