THE SETUP: I have an Access form with several bound controls, a sub-form, a bound text box (txtRMANum) for an RMA#, and one unbound combo box (cboRMAnum) for record navigation. The combo box is populated with an index and RMA# from each record in the table. When the user edits or adds a new record the form needs to check (txtRMANum) and if it is empty I need to set a unique value. The combo box (cboRMAnum) has been on the form and used for a couple of years, I am now trying to add the new RMA# rule.
Option Compare Database
Private Sub cboRMANum_AfterUpdate()
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.findfirst “[TransactionID] = ” & Str(Nz(Me![cboRMANum], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub
Private Sub Form_BeforeUpdate(Cancel As Integer)
‘Set RMA number if it is empty
If Nz([txtTicketNum], 0) = 0 Then
[txtTicketNum] = [TransactionID]
End If
End Sub
THE ERROR: The error only occurs when the RMA# is left blank on an edit. When a user edits a record that already exists and attemps to navigate using the combo box I get an error message (Update or CancelUpdate without AddNew or Edit). This error does not occur when the user tabs to or clicks into the subform.
The goal is to set the RMA# if it is blank, whether it is a new record or an edit.