Problem - Lock records in continuous form except new and edited record.

[Kaloyan Krumov]

I have the following setup:
Table containing record for transactions with products – in and out of a warehouse.
Continuous form for entering transaction information.
I have filtered the form to show only records from today and yesterday.
I locked old records in the current event of the form

 Private Sub Form_Current()
    Me.AllowEdits = Me.NewRecord
End Sub

Everything works fine until the current record loses focus (I made a check if the user clicks on locked record a message show “You Can’t Edit saved records” ). When the current record loses focus it is locked and the user can’t finish filling in all needed information.
I tried, after the message pops up to move the focus to the new record and it works, but the focus is moved to the current column new record and I have a series of checks that prevent the user to fill in a filed in the new record before filling in information in the previous field. This leads to two messages every time the user clicks on saved record – one “You Can’t Edit saved records” and one “You have to fill in the previous field”.

How can I stop the current record from locking after the “You Can’t Edit saved records” message pops up or set the focus to the first field in the new record?

[jimatqsi]

Try adding this code to the OnCurrent event for the form:

me!fieldname.setfocus

Or you could put that code right after the message
“You Can’t Edit saved records”

Jim

[Kaloyan Krumov]

I have tried this but it didn't work as i need it.
I have sorted this problem by adding additional field type Yes/No - hidden for the user. By default is set to No (false) after the save record button i pressed the current record is saved and the field is set to Yes (true). This way i can control when the message is displayed and when not.
Tx anyway for your help.

[Stewart Ross]

Hi. Users often make minor mistakes when entering data, and need to edit the record they just entered. They can't do this if they are prevented from doing so by having confirmed that they wanted to save the current record.

To allow for editing minor errors I add a date/time field called DateCreated to the table concerned, and set its DefaultValue property to Now() to record the current date and time automatically when the record is inserted by Access.

I then use the On Current event of the form to test whether the value of the current date and time is greater than the value of the DateCreated field by a particular threshold. I allow an hour in some cases (a value difference of 0.04 between the original date and time and the current one).

If the time difference is greater than the threshold of, say, an hour then AllowEdits is set to false. I also have visual confirmation of edit status on the form, to leave the user in no doubt that the record is read-only.

I also use a similar technique in a larger application where the edit threshold for records is expressed in days rather than hours or minutes. In this case the threshold is stored as a value in a system parameters table, so that administrators can change the 'keep editable' threshold if required (from 7 days to 1 day, for example).

-Stewart

[Kaloyan Krumov]

Tank you Stewart for your reply. I actually have such field in my table but didn’t think of using it this way.
Thank you i will include this mechanic in my form.

[jimatqsi]

Stewart, that's a nice solution, thanks.

Jim