Problem cropped up out of the blue

[Dave14867]

Hello all,



I have a form that has been working great for over 2 years. Now all of a sudden it has an issue. The intent is that the form will not allow the user to enter a Lot Number that has already been used, and will set the focus back to the "DHRNumber" field and allow the user to make the correction and move on to the next field.

The problem is that focus is set back to the "DHRNumber" field but then you cannot move on to the next field. The only way to get around it is to close the form and reopen it, then you can proceed as normal.

Like I said, this just all of a sudden started happening, what am I missing?

The code is shown below for the On Lost Focus event.

Thanks in advance.

Private Sub DHRNumber_LostFocus()

On Error GoTo DuplicateLotNumber


DoCmd.RunCommand acCmdSaveRecord


Dim Num As Integer
Num = Err.Number


DuplicateLotNumber:
    If Err.Number = 3022 Then


        MsgBox "You have entered a Lot Number which already exists, Please verify you have the correct Lot Number", , "Duplicate Lot Number:"
    End If


    Me.Rev.SetFocus
    Me.DHRNumber.SetFocus
End Sub

[Minty]

You would normally check values in the before update event, which would allow you to undo the entry.
You are using an error to trap that in the lost focus event, which is not a recommended way of validating things.

You can check in the before update event using (assuming your lot number is text) using something like

If Dcount("DHRNumber","YourtableName","[DHRNumber] = '" &  Me.DHRNumber & "'") > 0 Then 
    Me.Undo
    Cancel = True
End IF

[Dave14867]

Thanks Minty that seemed to do the trick.

I just wonder why the method I used worked for so long.

[Minty]

That is a very good question.
I can think if a few suspects ( I mostly avoid on focus / lost focus events as they can have some interesting side effects), but tbh, I think it's better to just think you previously "got away with it"

[moke123]

Was it ever triggered before? Maybe no one ever entered a duplicate number.

[Welshgasman]

I am missing the plot here?

The last thing that code does is set focus to DHRNumber, regardless of any error or not?
How on earth did it ever work?

[Minty]

I am missing the plot here?

The last thing that code does is set focus to DHRNumber, regardless of any error or not?
How on earth did it ever work?

Well spotted - I suspect an Exit Sub has been deleted somewhere.

[moke123]

Well spotted - I suspect an Exit Sub has been deleted somewhere.

Or the End If got moved