Before update cancel=true

[mafhobb]

Hi

I have a bound continuous form that is used to add text messages to a particular sku number so that the users can find those messages elsewhere in the database by typing the SKUnumber. This means that the SKUmessage is unique and it is used as the key in the underlying table.

This works fine as long as the admin does not try add (by mistake) a duplicate SKUnumber in the continuous form. To deal with this I use code in the beforeupdate for the SKUnumber field. Please see below:

Private Sub txtSKU_BeforeUpdate(Cancel As Integer)
   On Error GoTo txtSKU_BeforeUpdate_Error
    Dim ErrorForm As String
    Dim ErrorControl As String
    ErrorForm = "frmSKUMessage"
    ErrorControl = "txtSKUBeforeUpdate"

Dim UserCount As Long
Dim TestedSKU As String

' Make sure that there is something in the box
    If Me.txtSKU = "" Or IsNull(Me.txtSKU) Then
        MsgBox "Bitte Artikelnummer eingeben"
        Exit Sub
    End If

'if text was entered, assign the SKU value to a variable
TestedSKU = Me.txtSKU.Value

'Check to see if the SKU# already exists
UserCount = DCount("[SKU#]", "[TblSKUMessage]", "[SKU#] = '" & TestedSKU & "'")
    If UserCount > 0 Then
        MsgBox "Der Artikel ist bereits vorhanden. Es kann keine zweite Nachricht erstellt werden.  Bitte ändern Sie die Nachricht im vorhandenen Artikel"
        Cancel = True
        Exit Sub
    End If
    Exit Sub
    
txtSKU_BeforeUpdate_Error:

    Dim ErrorCode As String
    Dim ErrorNumber As String
    ErrorNumber = Err.Number
    ErrorCode = Err.Description
    Call SendError(ErrorCode, ErrorNumber, ErrorControl, ErrorForm)
    Exit Sub
End Sub

However this code does not work as I expected. The sub detects that a duplicate SKU has been entered and it exits the sub right after "Cancel=true", however the new record fields remain active and the duplicate SKU remains in the field. If the user tries to close the form then he gets the same "duplicate value" error as he goes through the same beforeupdate event but if he deletes the SKU, he gets the "index or primary key cannot contain a null value"

I understand what is happening, but I expected the "cancel=true" to get me out of the "enter new record" mode in the continuous form.

How can I do that?

Thanks

mafhobb

 

[mafhobb]

Solved!

just added "Me.Undo" right after "Cancel=True"