2

I'm trying to set up error handling in an Access app that connects to a SQL Server backend to prevent Access passing a null primary key to the server.

I'm using BeforeUpdate and code based on Properly Handling Errors in VBA (Excel)

My code is supposed to run the ErrHandler code if the user sets S_ID to null (or leaves it as null when creating a new record). But instead it's always running the ErrHandler code.

Private Sub S_ID_BeforeUpdate(Cancel As Integer)

Dim trigger As Integer

On Error GoTo ErrHandler

If Me.S_ID Is Null Then
    trigger = 1 / 0
End If

Exit Sub

ErrHandler:
    MsgBox ("Key was null, fix it")
Resume Resume_spot

Resume_spot:

End Sub

I've yet to write the code for what I want Access to do when the error occurs, hence the useless MsgBox and lack of anything after Resume_spot. Before I get that far I want to get the error handling to trigger properly.

Community
  • 1
  • 1
Rominus
  • 1,181
  • 2
  • 14
  • 29
  • 2
    Obviously not an answer, but shouldn't the ***backend*** be responsible for maintaining its own identity column integrity? Why isn't it just defined as `NOT NULL` in SQL Server? – Comintern Sep 14 '16 at 21:56
  • 1
    BTW, if your `MsgBox` includes `Err.Number` or `Err.Description`, it makes this pretty easy to debug. – Comintern Sep 14 '16 at 22:03
  • 1
    *"(or leaves it as null when creating a new record)"* - this will **not** trigger the control's `BeforeUpdate` event. – Andre Sep 14 '16 at 22:13
  • @Comintern It is defined as `NOT NULL` but I don't want my users to have to parse SQL server ODBC failed messages – Rominus Sep 15 '16 at 14:46

3 Answers3

5

You need to use IsNull

Change:

If Me.S_ID Is Null Then

To:

If IsNull(Me.S_ID) Then
ThunderFrame
  • 9,352
  • 2
  • 29
  • 60
3

The other answers explain the solution, so I'll explain the problem. Your error handler is catching a Run-time error 424: "Object required" on this line:

If Me.S_ID Is Null Then

The Is comparison operator requires that both sides of the test evaluate to objects. Null is not an object, thus the error. Your "trigger code" of trigger = 1 / 0 that is intended to raise a division by zero error is never fired.

That said, if you need to test an error handler or create a specific error, it is much easier to just use Err.Raise instead.

Comintern
  • 21,855
  • 5
  • 33
  • 80
0

1- Why the strange construction with trigger?

Just do

If IsNull(Me.S_ID) Then
    MsgBox "Key was null, fix it"
    Cancel = True
End If

2- For a simple check like this, you don't need any code. Use a Validation rule for the form control.

3- All this doesn't cover the case where the user never starts entering anything in the control. This can be handled in Form_BeforeUpdate() by setting Cancel = True there.

Andre
  • 26,751
  • 7
  • 36
  • 80
  • I was using a validation rule, but Access flips its shit and doesn't allow you to exit the field until you enter something, so if the user opened a new record in error I'm going to get invalid data. – Rominus Sep 15 '16 at 14:31