I would like to restrict changes to the displayed data in a form from updating the underlying record unless a control is clicked. This would protect against unintended changes to the table.

I don't know how to do this.

The easiest way that I have found of doing this is to set the Locked property of each control to True. Then if you have an Edit button or something, on the Click event for that button unlock each control.

txtTextBox.Locked = False

The other way is to do this is to make the enabled property false, and make it true when the edit button is clicked.

The difference is that when a control is disabled, it will be grayed out, whereas when it is locked the only this different is that you can't change the data.

Hope that helps,

Mike

Mike
EchoVue.com

Thanks for your reply, Mike.
I moving from being a casual Access user to having responsibility for creating an application from scratch. I am very experienced in system design and databases, but new to VBA and the technical aspects of Access.

The form is a personnel update. It contains several combo boxes to select, for example, the person's manager. I would like it to work like Quicken, where you can change a record, but must click a control to perform the update. If I understand your notes, it would work differently.

 I could have all the controls and text boxes locked, and use a control click to unlock them. Then, any changes would update the record. I would lock the controls again when the user moves to a different record. Also, all would be unlocked when adding a new record.

I have a problem interpreting the events, and selecting the right one for the action

To do all of them at once instead of one field at a time, you can set the AllowEdits, AllowAdditions, and AllowDeletions properties of the form to False. Then you can have a button that toggles them from True to False and back. A label then could be changed to let the user know if, say, you're in "EDIT" or "VIEW" mode.


Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division

Great:D
They click the control, I change AllowEdits to True, and display a label that tells them they are in edit mode. When they move to another record, I want to change AllowEdits back to false. What is the appropriate form event?

And there you have a better answer!!

Thanks Greg - Why didn't I ask this question 6 months ago?

Mike
EchoVue.com

Change the three properties to False on the form's ON CURRENT event.


Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division

This is a very easy way for me to get smarter.

I hope that in time, I will know enough to contribute!

I implemented your solution, but created a problem. I use a combo box to select the person's record, and allowedits = false prevents the combo box from working. I added a control cmdFindName to set allowedits to true. This works, but seems awkward. Here's my code. Better suggestions?


Private Sub cmdFindName_Click()
' enable cboFindName
On Error GoTo Err_cmdFindName_Click
    Me.AllowEdits = True
    lblFindName.Visible = True
Exit_cmdFindName_Click:
    Exit Sub

Err_cmdFindName_Click:
    MsgBox (Err.Number & " " & Err.Description)
    Resume Exit_cmdFindName_Click

End Sub
Private Sub cboFindName_AfterUpdate()
    ' Find the record that matches the control.
    On Error GoTo Err_cboFindName_After
    Dim rs As Object
    Set rs = Me.Recordset.Clone
    rs.FindFirst "[Email] = '" & Me![cboFindName] & "'"
    Me.Bookmark = rs.Bookmark
    Me.AllowEdits = False
Exit_cboFindName_After
    Exit Sub

Err_cboFindName_After:

    Resume Exit_cboFindName_After
End Sub

Is your combo-box bound to the email field? I think if you remove the bound control from this control, but keep the datasource, you should be able to use it even when Editing is turned off.

Of course I could be way out in left field too.

Mike

Mike
EchoVue.com