Replacing Run time error 3022 (1 Viewer)

[Drand]

Hi

I have an unbound form which adds records to a table with a unique index based on the Month and Year (expmonth and expyear respectively)

When I attempt to add a record that violates the index rules, I receive error 3022 which is exactly what it should do.

What I am trying to do is replace the system with a more user friendly custom message.

I have tried placing error trapping in the on_error properties of the from but it does not replace the system error.

I have also tried to place it within the code for the command button which updates the table but again, the system error persists.

Is this because the form is unbound and if so, is there a way to achieve this?

Many thanks

 

[theDBguy]

Since the form is unbound, can you show us the code you're using to add the records to the table?

 

[Drand]

Thanks

It is

Set DB = CurrentDb
Set rst = DB.OpenRecordset("tblExpensesTesting")

With rst
    .AddNew
    !DateAdded = Now()
    !ExpMonth = ExpMonth
    !ExpYear = MyYear
    ![Bank Fees] = Me.txtBankFees
    !Cleaning = Me.txtCleaning
    ![Consulting and Accounting] = Me.txtConsulting
    ![Eftpos Fees] = Me.txtEftpos
    !Electricity = Me.txtElectricity
    ![Event Fees] = Me.txtEventFees
    !Fuel = Me.txtFuel
    ![General Expenses] = Me.txtGeneral
    !Generator = Me.txtGenerator
    !Insurance = Me.txtInsurance
    ![Interest Expense] = Me.txtInterest
    ![Leased Plant and Equipment] = Me.txtLeasedPlant
    ![Marketing Fees] = Me.txtMarketing
    ![Motor Vehicle Expenses] = Me.txtMotorVehicle
    ![Other Selling Expenses] = Me.txtOtherSelling
    ![Printing and Stationary] = Me.txtPrinting
    ![Repairs and Maintenance] = Me.txtRepandMain
    ![Telephone and Internet] = Me.txtTelandInternet
    !Tolls! = Me.txtTolls
    ![Van lease] = Me.txtVanLease
    ![Marketing Fees] = Me.txtMarketing
    ![Franchise Fees] = Me.txtFranchiseFees
    
    
    .Update
 

    End With

 

[The_Doc_Man]

You need to have an error trap in the same routine that performs your recordset.Update operation. Before you open the recordset,

    On Error GoTo MyErrorTrap

Somewhere after the END WITH statement but before your End Sub

EndPoint:
    Exit Sub

MyErrorTrap:
    If Err.Number = 3022 Then
        MsgBox "custom error message goes here", vbOKOnly, "custom header goes here"
    Else
        MsgBox "Error " & CStr( Err.Number ) & ", " & Err.Description, vbOKOnly, "Error detected during update attempt"
    End If
    Resume EndPoint

The reason you see the standard error message is because you are not intercepting the 3022 error when it occurs. This code I showed you is a narrowly constructed example that only intercepts 3022 errors and puts up a custom message. All other errors will give the standard error description. If you have other errors you would want to intercept for a special error message, a SELECT CASE would perhaps be preferable.

 

[Drand]

Thanks The_Doc_man

I inserted your code after the end with statement and it is still not trapping the error. The system error still appears.

 

[Gasman]

Did you also insert this at the top area of the code, before any error can occur?

On Error GoTo MyErrorTrap

This is not going to fix your error, merely allow you to give a more appropriate message.

 

[ebs17]

Program error-free:

Set rst = DB.OpenRecordset( _
       "SELECT * FROM tblExpensesTesting WHERE ExpMonth = " & Me.ExpMonth & " AND ExpYear = " & Me.MyYear, _
       dbOpenDynset)
With rst
   If .EOF Then
      .AddNew
      !ExpMonth = Me.ExpMonth
      ' ...
      .Update
   Else
      MsgBox "not so"
   End If
   .Clode
End With

Error-free: First check, then act.
Some do it while driving.

 

[Pat Hartman]

I agree with ebs, I think you need to check before you do the addnew. However, that leads us to why are you not using a bound form. The whole point of using a RAD tool like Access is to use Bound Forms and reports.

 

[ebs17]

However, one must be aware that a bound form does not prevent index errors either. You have to respond to the passed error in Form.Error event, or if you want to work error-free, check in the table in Form_BeforeUpdate, for example with

If DCount("*", "tblExpensesTesting", "ExpMonth = " & Me.ExpMonth & " AND ExpYear = " & Me.MyYear) > 0 Then ...

 

[gemma-the-husky]

I think for error 3022 you may well need to use the form error event, and intercept the accesserror, because an automation error (I like to call it) is not a coding or logic error.

It's just an error that happens when you try to save a record.

If you try to write a sql insert statement (as you would have to with an unbound form) then it may well be a normal run time error.

@ebs17 just mentioned the form error as well.

 

[Solo712]

I agree with ebs, I think you need to check before you do the addnew. However, that leads us to why are you not using a bound form. The whole point of using a RAD tool like Access is to use Bound Forms and reports.

I don't think I would agree with such a broad statement, Pat. While I am not sure about the merits of an unbound form in this case, I have found unbound forms the better (,and sometimes the only, ) solution in instances where you need programmatic control over the data presentation. At any rate, the issue has nothing to do with the subject matter here. Here the question is, more or less, is it better to eliminate the error before it happens or trap it later and I think we would all be in agreement that it is the former.

BTW, I recommend creating a function equivalent to data checking in Before Update event for unbound forms. A Boolean function say "DataOk" goes through all the text fields and combos for data requirements, and redirects the user back to fix a problem if one is found. For example:

Function DataOK() as Boolean
   .......
   If Nz(Me!txtLastName) = "" Then
      MsgBox "Last Name missing!"
      Me.txtLastName.SetFocus
      Exit Function
   End If
   .......
   DataOk = True
 End Function

Best,
Jiri

 

[Pat Hartman]

Pat. While I am not sure about the merits of an unbound form in this case, I have found unbound forms the better (,and sometimes the only, ) solution in instances where you need programmatic control over the data presentation.

Possibly, f you don't normalize your schema and you don't know how to use the form level events.

 

[amorosik]

Thanks The_Doc_man

I inserted your code after the end with statement and it is still not trapping the error. The system error still appears.

""..and it is still not trapping the error..."

It is highly advisable, from my point of view I would say mandatory, to use a procedure capable of 'catching' the errors that may occur in the code
It could not be 3022 but errors of different types, and it is useful that they are intercepted, and if necessary managed in the way that the programmer deems appropriate (display, subsequent writing attempts, various checks ...)
I would advise you to insist on running the code that currently seems not to 'see' the error
If the error handling routine does not start there must be an error in the code contained in the procedure
First get the error handling routine to work properly
Then, if you wish to do so, you will use some code (test duplicate key and use another one) to handle the specific error as you want

 

[amorosik]

Thanks The_Doc_man
I inserted your code after the end with statement and it is still not trapping the error. The system error still appears.

Before you open the recordset,
On Error GoTo MyErrorTrap


Have you insert also the code row above?

 

[ebs17]

Jiri, now you're talking about fields with the required property. That would be a different requirement than observing unique indices, but it is certainly a necessary one. Compliance with validation rules would be another issue.

You should already know the definition of your table before you edit it.

Whether I use a bound form or an unbound form depends on the needs, not on my knowledge of handling a form. There are also cases where entries are to be made in a second table from a bound form.

Irrespective of this, editing in tables is not only carried out using forms. It should be part of the mastered tools of the trade to work with action queries and recordsets and to immediately incorporate checks to avoid errors of any kind.

In my mind, error handling has the task of catching unexpected errors. An index error is not an unexpected error (or I don't know anything about my database and should look for another job). I consider program control over errors and error handling to be mostly bad form.
I am a German. Germans love their cars. So not only do they drive by ear (bumper crunch), but they also open their eyes to see where they're going and avoid harmful touches.

 

[Pat Hartman]

You might want to read the help entry if you don't understand the suggestions.

Error Object - Data Access Objects (DAO)

Office developer client VBA reference documentation

learn.microsoft.com

 

[Solo712]

Possibly, f you don't normalize your schema and you don't know how to use the form level events.

Yeah, sure Pat.

 

[Solo712]

Jiri, now you're talking about fields with the required property. That would be a different requirement than observing unique indices, but it is certainly a necessary one. Compliance with validation rules would be another issue.

For my purposes, I see no value in distinguishing between assuring unique index, non-Null, or a required value for a field. It's all part of a data validation process.

You should already know the definition of your table before you edit it.

??

Whether I use a bound form or an unbound form depends on the needs, not on my knowledge of handling a form. There are also cases where entries are to be made in a second table from a bound form.

I am not sure why you are telling me this. I use all the Access tools available to me, forms bound to tables, queries, unbound forms (either partially or fully unbound), temporary tables.

Irrespective of this, editing in tables is not only carried out using forms. It should be part of the mastered tools of the trade to work with action queries and recordsets and to immediately incorporate checks to avoid errors of any kind.

Again not sure why you are telling me this. FYI, I have been working with Access off and on for twelve years.

In my mind, error handling has the task of catching unexpected errors. An index error is not an unexpected error (or I don't know anything about my database and should look for another job). I consider program control over errors and error handling to be mostly bad form.

Thanks for sharing.

I am a German. Germans love their cars. So not only do they drive by ear (bumper crunch), but they also open their eyes to see where they're going and avoid harmful touches.

Again, I am baffled, but thank you for your thoughts.

Best,
Jiri

 

[Isaac]

You will need to use CODE to determine if the index would be violated, rather than trying to trap this table level error

 

[Drand]

Thank you everyone for your insights! I did have the OnError code in the incorrect place, that is after the error occurred.

When I corrected this it worked perfectly!

In regard to the unbound form, I use this as some of the data is used to update other tables as well.

Many thanks again