Best solution for preventing unintended edit to a record?

[What]

I have a form that has two combo boxes that display a persons first and last name. Those combo boxes are pulled from the names table. Based on what value the user chooses, the appropriate name ID is stored in the application table.

That is fine and dandy for creating a new record. However, if a user is viewing an existing record, choosing a different name in the combo box changes the name ID. Instead of editing the existing record I want the user to go to a record of the newly selected ID.

The solution that I came up with seems like it might be overkill. I thought I would see what everyone thinks before I go to the trouble of coding it all.

On entry on either of the combo boxes, the name id is stored in a variable.

On losing focus the code compares the current name ID to the stored name ID. If they are different, the change is reverted to the original name ID(undo). After that the code will change the record being viewed to one that is associated with the 'new' name ID.

I'm still pretty new to Access so I might be missing something obvious. Any advice/suggestions would be most welcome!

 

[pbaldy]

Actually it should be fairly simple. Try setting the Allow Edits property of the form to No (take a look at the other similar properties as well). That should allow the creation of new entries but prevent edits to existing records.

 

[What]

Thanks for the suggestion pbaldy! Changing the form properties to not allow editing does prevent the user from editing the record. However, it makes the combo boxes I have on the form pretty much useless. You can scroll through the list, but can't select a different name.

My desired behavior is to not allow edits, but allow the user to select a different name in the combo box. The catch is that making that change in the combo box selects a different record to view instead of altering the currently-being-viewed record. I know, I just have to make things difficult...

 

[missinglinq]

You have to mark the controls (comboboxes, in this case) you want to use, so that Access can recognize them, then loop thru all other controls and Lock/Disable them.

In Design View, select the comboboxes you want "active" and goto Properties - Other and in the Tag Property box enter DoNotLock, just like that, no quotes.

Then place this code in the code window
Code:

Private Sub Form_Current()
Dim ctrl As Control

If Not Me.NewRecord Then
  For Each ctrl In Me.Controls
    If (TypeOf ctrl Is TextBox) Or (TypeOf ctrl Is CheckBox) Or (TypeOf ctrl Is ComboBox) Then
     If ctrl.Tag <> "DoNotLock" Then
       ctrl.Enabled = False
       ctrl.Locked = True
     Else
       ctrl.Enabled = True
       ctrl.Locked = False
     End If
    End If
  Next
Else
For Each ctrl In Me.Controls
 If (TypeOf ctrl Is TextBox) Or (TypeOf ctrl Is CheckBox) Or (TypeOf ctrl Is ComboBox) Then
  ctrl.Enabled = True
  ctrl.Locked = False
 End If
Next
End If

End Sub

Now change AllowEdits back to Yes. You should be set now. On new records, all controls will be usable. On an existing record (one that has been saved to the table) only the controls with the Tag Property set to DoNotLock will be usable.

 

[What]

Awesome code missinglinq! That's a great help. I used the wizard to re-create my combo box so that it goes to view the selected record in the combo box. Which created this code.

Private Sub Combo43_AfterUpdate()
    ' Find the record that matches the control.
    Dim rs As Object

    Set rs = Me.Recordset.Clone
    rs.FindFirst "[app_nmID] = " & Str(Me![Combo43])
    Me.Bookmark = rs.Bookmark
End Sub

It all looks great when interfacing with it. Luckily I was using a test version of the database though! It now takes you to the record you choose in the combo box, but it also changes the app_nmID of the originally-being-viewed-record to the value you choose in the combo box. Which is not at all what I want. This is essentially tying application records to the wrong name every time you choose to 'navigate' to another record.

I modified the behavior with this code, and now seems to have corrected the issue.

Private Sub Combo43_BeforeUpdate(Cancel As Integer)
' Find the record that matches the control.
    Dim rs As Object
    Dim nameID As String
    
If Not Me.NewRecord Then
    Set rs = Me.Recordset.Clone
    nameID = Me![Combo43]
    Me.Undo
    rs.FindFirst "[app_nmID] = " & nameID
    Me.Bookmark = rs.Bookmark
End If
End Sub

I feel like I must be missing something huge, because it doesnt make any sense to have the default be after instead of before. I'm having trouble envisioning a situation where you would want to find a record based upon the selection, and also save that value to the current record. Why is the default set to afterupdate?

 

[pbaldy]

A "find a record" combo should be unbound (no control source), so changing it has no effect on the underlying records.

 

[What]

Oh I see...That makes sense.

Since my combo box is bound, do you see a problem with the way my code handles preventing an alteration to underlying records?

 

[pbaldy]

I suppose not, though I would use Cancel = True plus the Undo. But I would probably have a dedicate, unbound combo so I didn't have to worry about unintended consequences.