What "On Event" do I use?

  • Thread starter Thread starter Dazza
  • Start date Start date
D

Dazza

I have a database that sits on SQL Server 2000 and users are restricted on
what actions they can perform on the objects. Via ODBC I have an Access
database for the forms, reports etc.

One of the forms uses a subform and here is the problem. The underlying
table of the subform can only be viewed by certain users thus if they tried
to change, delete or update a record, SQL Server would say "denied". Inside
the subform I have used an error trap that will convert the SQL Server
message into something more understandable to the end-user. If they click
on a save button within the subform the message displays as intended,
however, if they change something then click on a field in the main form,
they get the obscure SQL Server error instead.

My question is, how can I get the same user-friendly message if they either
click on save in the subform or move the focus to a field in the main form?
I have tried adding the message code to the sub-form's "On Lost Focus" event
but this does not work. I also added the same code to the "On Error" event
for both forms but again no joy. After the message is displayed using the
msgbox command I also have DoCmd.RunCommand acCmdUndo so that any changes
they tried to make is removed. If I did not do this, the user would be
trapped on the form and could only exit by closing Access.

Any advice would be greatfully received.

Regards
Dazza
 
I would think that the ON ERROR event in the subform would be the place to
trap that error, but I have to admit that I haven't tried it with an SQL db
being accessed through ODBC.

How are you trapping the error when the users hit the save button? Are you
trapping it with an On Error Go To Statement?

Try the On Error Event in the subform. Trap it and display the error code
(if any) with a MsgBox.

When using the On Error Event, don't forget that you have to tell it to not
display the normal error message, otherwise it will do whatever code you
have in the On Error Event and then display the normal error message.
 
I have put the code in the OnError event in both the subform and the main
form but the problem remains.
Regards
Dazza
 
Back
Top