Combobox record lookup and .oldvalue compatibility issue.

[MatthewGrace]

I have attached a super-small file to demonstrate: 1 table and 1 form. Form has a combobox that has VBA code synching the combobox selection with the forms current record like you've seen a great many times:

Private Sub Combo8_AfterUpdate()
' Find the record that matches the control.
 With Me.Recordset.Clone
 .FindFirst "[ID] = " & Nz(Me![Combo8], 0)
 If Not .EOF Then Me.Bookmark = .Bookmark
 End With
End Sub

That portion works fine, but things get weird when I subsequently change the text value in a field and then use the aforementioned combobox again to jump to another record... Specifically because I setup this code to determine if commit that text change:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If MsgBox("You have made changes.  Commit?", vbQuestion + vbYesNo) = vbNo Then
txtStuffName.Value = txtStuffName.OldValue
txtStuffBrand.Value = txtStuffBrand.OldValue
txtOrderCode.Value = txtOrderCode.OldValue
End If
End Sub

If you choose "vbNo" to execute the .oldvalue stuff, error messages involving CancelUpdate, Update, AddNew, and Edit methods are brought up. I've used them before in different databases, but I can't figure out why Access thinks they should be invoked by the code above. Try running it and see.

Thank you to anyone who considers helping me.

Matt

[June7]

I am not getting error when I select No from the confirmation popup. Works just fine for me.

Wait, the error happens if make selection in combobox while record is still in edit. Instead of OldValue, try Me.Undo.

[John_G]

Hi -

If you answer "No", then you need to cancel the update with Cancel = -1 before the endif.

John

[MatthewGrace]

Thank you June, the code works.
Maybe this kind of error is very common (no clue). I'll post in case it helps someone. Here's my understanding of it... this code causes a failure later on:

Private Sub Combo8_AfterUpdate()
' Find the record that matches the control.
 With Me.Recordset.Clone
 .FindFirst "[ID] = " & Nz(Me![Combo8], 0)
 If Not .EOF Then Me.Bookmark = .Bookmark
 End With
End Sub

Sure, it works immediately, but while there's nothing wrong with it, I believe "me.bookmark = .bookmark" programmatically changes the record and fires the BeforeUpdate event (bear in mind, the form is still in .edit mode from typing in textboxes for the original, pre-bookmark-changing record):

Private Sub Form_BeforeUpdate(Cancel As Integer)
If MsgBox("You have made changes.  Commit?", vbQuestion + vbYesNo) = vbNo Then
txtStuffName.Value = txtStuffName.OldValue ' The error will fire here, because this line not only makes the visible text undo, 
but also attempts to change the value of the record it's linked to... in this case the record you just bookmarked to, which is 
not currently in .edit mode.  It's exactly the same error (#3020) as if you tried to say recordset![fieldname] = "text" without
 first doing a recordset.edit
End If
End Sub

In the context of the problem I'm having, you pointed out that the error only occurs IF the forms record selector has a pencil icon indicating you're in .edit mode already for the original row. Someway somehow this jumbling of both the original row being edited AND programmatic changing of the .bookmark before getting a chance to update the changes causes discord.

You suggested me.undo as an alternative, and this seems to work.

Thank you,
Matt