Custom Error Message (1 Viewer)

[lamiller099]

Hello. I am very much a novice at SQL, but was wondering if it would be possible to customize the error message when someone runs an append query and it finds a problem with the primary key. For my users I'd rather it say something like "Employee is already listed in the Employee Log table."

Can that be done and is it easy enough for someone to guide me through the code?

The table is named EmployeeLog and the field they'll be using for the append query is EmpID.

Thanks in advance!

 

[theDBguy]

Hi. Welcome to AWF!

I don't think that's possible. You may have to create your own append process, so you can control the messages better.

 

[The_Doc_Man]

The problem is that when SQL reports this error, it is in the context of either JET or ACE (one of the two depending on Access version), and you have zero control over intercepting that error until it gets back to the Access side of the process. If you are executing an INSERT or UPDATE query that would create the key violation, you have to have an error handler to intercept that violation. Offhand I don't recall the error number, but if you have a handler that at least tells you the Error.Number value at the time of that error, you could trap it and issue your own error message instead of the default message.

The OTHER method, however, is before you issue the INSERT or UPDATE, do a DCount of the table to see if that entry already exists (count > 0) and rather than attempt the SQL operation, just issue the message based on your DCount parameters.

 

[plog]

Why are users using an APPEND query and not a form to add data? Why are users allowed to assign primary keys instead of allowing Access to assign an autonumber as the primary key?

The issue you are trying to address isn't the big picture problem you should be focusing on. The workflow you have is the issue, not this error message.

 

[lamiller099]

Why are users using an APPEND query and not a form to add data? Why are users allowed to assign primary keys instead of allowing Access to assign an autonumber as the primary key?

The issue you are trying to address isn't the big picture problem you should be focusing on. The workflow you have is the issue, not this error message.

The users needs a lot of data for each of the employees being added, but instead of them having to enter it all, it will come from a table that I will have imported from another system monthly. They'll then just run the append query to add each employee as needed and it will pull the employee data from the imported data into the Employee Log table.

Each employee already has an Emp ID #, so don't want to use an AutoNumber field in the Employee Log table as I don't want the same employee added mutiple times or the relationship to a Case table I have won't work.

Sorry if that's not a clear explanation, but it sounds like I'll just need to train the users to understand what that message means.

Thanks.

 

[lamiller099]

Hi. Welcome to AWF!

I don't think that's possible. You may have to create your own append process, so you can control the messages better.

Thank you for your reply. I was afraid that might be the answer as I could never find anything when I Googled my question.

 

[lamiller099]

The problem is that when SQL reports this error, it is in the context of either JET or ACE (one of the two depending on Access version), and you have zero control over intercepting that error until it gets back to the Access side of the process. If you are executing an INSERT or UPDATE query that would create the key violation, you have to have an error handler to intercept that violation. Offhand I don't recall the error number, but if you have a handler that at least tells you the Error.Number value at the time of that error, you could trap it and issue your own error message instead of the default message.

The OTHER method, however, is before you issue the INSERT or UPDATE, do a DCount of the table to see if that entry already exists (count > 0) and rather than attempt the SQL operation, just issue the message based on your DCount parameters.

Thank you so much for your reply, but I think this may be beyond my capabilities; luckily my organization didn't hire me to be a databsase designer, so they know what they are getting, or not getting is probably a better way to say it. ;-)

 

[ebs17]

If you don't want to create duplicates, then just don't do it. Then there is no error message because there is no cause for the error. As part of an append query, it is very easy to directly integrate the check for existing keys and thus only append new records.
Sample query:

INSERT INTO
   TableA(
      YourID,
      [FieldList]
   )
SELECT
   B.YourID,
   [FieldList]
FROM
   TableB AS B
      LEFT JOIN TableA AS A
      ON B.YourID = A.YourID
WHERE
   A.YourID IS NULL

A database developer should be able to do something like this on the spur of the moment, since SQL is the native language in a database and should therefore be the developer's everyday tool.
You can be sure that there are many traces of this on the Internet and in better technical articles.

For query execution: use something like this

CurrentDb.Execute "NameAppendQuery", dbFailOnError