VBA code to detect field(s) causing the key violation?

[mdlueck]

I have VBA code in my field validation which turns the error field(s) to red background for the fields which have data errors.

I am dealing with testing key violation conditions currently. The Err.Description value does not state which field(s) is/are in error.

Is there a way to programmatically determine this so that the error field(s) may be set to red background?

Thanks!

 

[spikepl]

Your concept seems a bit odd. You do not normally store invalid data in the DB, but expend ALL efforts to prevent that. What are you trying to do here?

 

[mdlueck]

If someone imputs invalid data into a field, I stop, turn the field red, and leave them on the edit sceen they are on. Checking as far as datatype is concerned. Once the data is correct as best as the VBA code can tell, THEN it tries to update the DB.

Key violation errors are still possible at this point, and I would like to be able to find out what field(s) caused the error to turn them red, and leave the app on the edit screen.

More understandable?

 

[spikepl]

Sorry no. Keys are an internal database concept, normally not shown to the user at all, so the user is not, and should not be, in the business of inputting keys. Can you give an example?

 

[mdlueck]

Serial number / MAC Address... fields which are input by people at run-time and MUST be unique.

On various screens I have many fields which uniqueness is enforced. The only error I get back currently is that there is a key violation "somewhere". Gee, that is very helpful! I would like to be able to determine which field value is not unique to aid in reducing the time it takes to play Cat and Mouse.

 

[spikepl]

So why not check the data in the forms BeforeUpdate, and canceling the update if data is not ok?

 

[mdlueck]

Example please... What I have are API's which issue INSERT / UPDATE SQL and are not successful due to "ESP"... "Error Some Place".

I have not found a way to receive which field(s) is/are causing the error... which I would like to turn those fields red.

Thanks!

 

[spikepl]

Eg, see #5 in http://www.access-programmers.co.uk/forums/showthread.php?t=183470

 

[mdlueck]

Thanks for the URL link. So the DCount API suggested... is that going to go do a SQL Select and check for the existence of a matching record? If so, then possibly in a multi-user environment the key violation will not exist when the DCount is run, someone else INSERTs, and the second INSERT will fail... which causes that Access FE to become very confused to the point that the ill FE / Access instance must be closed / restarted.

Rather than merely look before I insert, I would like to gracefully handle the key violation, hopefully flag in red the field which caused the error, and most of all not have to exit the database to be able to modify additional records.

 

[spikepl]

Ok, so you check before the insert.

I think the form's OnError event will fire if you get your key violation - try. If you get such an error then check on its code if it is the feared key violation, and then you could check which field it was by the same precedure as in BeforeUpdate.

 

[mdlueck]

All righ, that sounds reasonable enough.

What about recovering from the key violation without requring exiting of the Access app? I mean, the key violation will have still happened, as-is that seems to knock my entire Access FA database. I mean the -2147217887 "The changes you requested in the table were not successful because they would create duplicate values in the index, primary key, or relationship. Change the data in the field or fields that contain duplicate data, remove the index, or refine the index to allow duplicate entities and try again."

For all DB actions after this, I get a 3705 "Operation is not allowed when the object is open"

So it would appear to me that this error causes the FE connection to self-distruct... which I would like to prevent somehow.

 

[spikepl]

Did you try the OnError where you specify what is to happen?

 

[mdlueck]

I have On Error in all of my code. Usually I pop a message box with the error and then exit. That is how I know what errors occurr. The nasty -2147217887 does not sound as though it is going to "disconnect" the FE from the BE database.

 

[spikepl]

Dod you code a handler and put it in the Forms OnError property?

 

[mdlueck]

No, only setting On Error handling in each function/sub for the events I wire code to. And my custom module / class code the same.

I am puzzled at what you are envisioning...???

 

[spikepl]

A Form has among many other things an OnError event. You can trap this particular error there, if you get a key violation

 

[mdlueck]

Aaahh, I am reading up on ADO error handling... seems when an ADO error happens it causes the "ADO collection to be cleared"... which I only ADO attach to the BE database at the beginning of the program.

In this light, suggestions how to handle the ADO connection gracefully?

 

[spikepl]

I have no idea, other than if an error while you are trying to update it from a form disconnects it then to reconnect and check which field value already exists.

DID you try the Form error handler? Because a disconnection by an error sounds a bit weird- perhaps it happens because it wasnt trapped?

 

[spikepl]

oops , the form event "This includes Microsoft Access database engine errors, but not run-time errors in Visual Basic or errors from ADO."

in other words, i have no idea how to do what you want

update: but I would still try the form's onerror event and see what turns up

 

[mdlueck]

As followup to others which may encounter this thread...

According to the MS Access 2007 bible page 811 concerning ADO error handling: I have added my RecordSet object to watches. Under ActiveConnection \ Errors \ Count = 0

Meanwhile a watch for Err.Description \ Err.Number have the "-2147217887" I reported earlier in this thread.

That page in the book was specifically dealing with an ADODB.Connection object where I have an ADODB.RecordSet... however I suspect if it were a Connection object it probably still would not have an error recorded.

Puzzling... onwards in search of the ADODB / Key violation graceful solution.