Change Text of System Error Message

[PSSMargaret]

I would like to change the text of a what I think is a system error message.



I have a delete button on a form and when it's clicked for values that are in use in the database, the message
"The record cannot be deleted or changed because table ‘|’ includes related records." is displayed.

I think that it's error message 3200.

How can I bypass this text and insert something more meaningful to the user?

I am using Windows/Access 2019.

[orange]

Margaret,

Here's a link and example that you could adapt for your Custom Error(s).

[davegri]

You get this message bacause other tables (child tables) contain records with foreign keys pointing to this record (Parent). So if the Parent were deleted, the records in the Child tables would be "orphans". Access won't allow this.
There are a couple of approaches that you can take, depending on what outcome you wish. If it would be OK to delete the Child records when the Parent record is deleted, you can check "cascade deletes" in the relationship property.
If you simply want to avoid the error message event, then before executing the delete, DLookup the Parent record key in the Child table, and if it exists, don't attempt the delete.

[Micron]

It's not uncommon to see a reply wrt to deleting records that usually it's not a great idea and that archiving them is typically considered a better approach.
Consider it said.

Access won't allow this.

- only if there is a relationship established between the tables.

[PSSMargaret]

davegri,

Thanks for your reply. I do understand the message and don't want to avoid it. Just want to change the text of the message to something more meaningful to the end user, e.g. "This value cannot be deleted because it has been populated in existing records." Or something similar.

The end user won't understand parent/child tables.

[davegri]

ust want to change the text of the message to something more meaningful to the end user, e.g. "This value cannot be deleted because it has been populated in existing records." Or something similar.

Then use the DLookup to determine if a child exists. If so, don't try to delete the parent. Inform the user with whatever you wish.

Micon

- only if there is a relationship established between the tables.

Of course. The problem won't occur if no relationship exists.

[isladogs]

Margaret

In your error handling routine just do something like this

Err_Handler:
If err=3200 Then 
MsgBox "Your custom message here", vbExclamation, "Message title
Else
MsgBox "Error " & err & " in YourProcedureName: " & err.Description
End If
Resume Exit_Handler

[davegri]

Colin, I was going to suggest that originally (because that is what I would have done), but I have seen some posters criticizing the use of error handlers to code around preventable errors.
You gotta try and dodge bullets around here...

[Micron]

Colin, I was going to suggest that originally (because that is what I would have done), but I have seen some posters criticizing the use of error handlers to code around preventable errors.

Some get too anal about it I think. Is it loftier to write a dozen lines of code to check and therefore prevent something from happening than it is to anticipate an error and trap with one line? Especially if you should have an error handler anyway? I don't think so and think it's a judgement call that depends on the situation.
One can always make such suggestions and qualify them with comments like 'some say...' or whatever. That's how I try to cover my butt.

[davegri]

Micron, good thinking.

[orange]

Margaret,

Did you look at the Allen Browne link I suggested in post #2?

[davegri]

Orange, I've been using Browne's example for more than 20 years. Great routine and it logs errors to a table!

[isladogs]

Whilst I agree that its always better to avoid errors occurring, in my opinion its also appropriate to use error handling to cover known possible errors to ensure the application runs smoothly
For example error 2501 when opening a report with no data, error 5 when cancelling a file browse dialog
.
In this case, as both of you had already covered the error avoidance, I covered the other part.
Of course then there are the unknown errors .... those we don't know are likely to occur

[PSSMargaret]

Thank you. This was very helpful and what I was looking for.

[isladogs]

We were all pleased to assist.