I realize that when I use the delete key then odbc error occurs before keyDown event. Is there any way the key down event to occur before odbc error? I have bound an sql server view in a form that is not updateable and I want to bypass the odbc error message when I press delete to make my own code to delete toward to sql server
ODBC error before delete keyDown event (1 Viewer)
- Thread starter nonlinear
- Start date
I found it The odbc error grabbed in the form's OnError event. So the manipulation can be there (using acDataErrContinue to ignore error message) and onDelete event that triggered after OnError event set Cancel=true to issue my sql delete toward to sql server
Last edited:
However, it strikes me that the KeyDown event of the form is not triggered when I select the entire record and press the delete key so I am forced to use the delete event
- Local time
- Today, 08:29
- Joined
- Sep 12, 2006
- Messages
- 15,956
Can't you set the form to not allow deletes, and add a command button to manage the delete? I often do that to avoid the problem that there is no after delete event.
Sorry but Access sometimes has strange and unpredictable behavior.
I make a review and at the end I will describe the final solution which is quite interesting:
I have a form bound with a not updateable sql server linked view. It is updateable only for edit (I create primary key using DDL when I link this view with dnsLess code) but not for deletions because it has join so the odbc throws an error that there are multiple base tables. OK. The error appears when delete event ends and you don't set Cancel=True. So it does not move to the next event BeforeDelConfin in which the deletion message appears. If you set Cancel=True in the deletion event then the odbc error doesn't occur.
According to the previous description οne solution would be to set Cancel=True in deletion event and issue a sql delete toward to sql server since it has of course been preceded by a custom deletion message. This approach works but has a drawback. Without Access removing the line of the deleted record In all the fields of the record the #Deleted# will appear!!! This is not intuitive for the end user and the form has to be requeried and the record selector will move to the first record after the requery! Not good!
So the end solution.
In the delete event show a custom delete message. If the user response = no then Set cancel=True. If user response = yes then issue an sql delete toward directly to sql server then set Cancel= false (or not set it after this is the default behavior) so to be able to continue to the next event. In the next event BeforeDelConfirm set Cancel = False and Response = acDataErrContinue. With these there will not appear the extra Access delete confirmation message and Access will smoothly remove the row without requery and record selector will move to the neighboring record!!!
With this approach we delete record directly to sql server while at the same time the user experience is Access!!!
I make a review and at the end I will describe the final solution which is quite interesting:
I have a form bound with a not updateable sql server linked view. It is updateable only for edit (I create primary key using DDL when I link this view with dnsLess code) but not for deletions because it has join so the odbc throws an error that there are multiple base tables. OK. The error appears when delete event ends and you don't set Cancel=True. So it does not move to the next event BeforeDelConfin in which the deletion message appears. If you set Cancel=True in the deletion event then the odbc error doesn't occur.
According to the previous description οne solution would be to set Cancel=True in deletion event and issue a sql delete toward to sql server since it has of course been preceded by a custom deletion message. This approach works but has a drawback. Without Access removing the line of the deleted record In all the fields of the record the #Deleted# will appear!!! This is not intuitive for the end user and the form has to be requeried and the record selector will move to the first record after the requery! Not good!
So the end solution.
In the delete event show a custom delete message. If the user response = no then Set cancel=True. If user response = yes then issue an sql delete toward directly to sql server then set Cancel= false (or not set it after this is the default behavior) so to be able to continue to the next event. In the next event BeforeDelConfirm set Cancel = False and Response = acDataErrContinue. With these there will not appear the extra Access delete confirmation message and Access will smoothly remove the row without requery and record selector will move to the neighboring record!!!
With this approach we delete record directly to sql server while at the same time the user experience is Access!!!
Last edited: