Something's really troubling me.

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Here is something that is really bothering me to the point I cannot sleep.

I have a form that on one of the command buttons the code is basically:

DoCmd.RunSQL "UPDATE table01...........;"
DoCmd.RunSQL "UPDATE table02...........;"
DoCmd.RunSQL "Delete * FROM table01 WHERE...........;"
DoCmd.RunSQL "UPDATE table04...........;"
DoCmd.RunSQL "UPDATE table05...........;"

This takes about 45 seconds to complete. To help protect things ; I think I
turned off the "Escape" key by catching it in the KeyDown: If KeyCode = 27
then KeyCode = 0. I think this is working. I have tried to jam it in a
practice mdb and it keeps processing to the end.

But what it one of my users who likes to make trouble hits the Ctrl-Break.
Then it will break the code and that user says "Wow, this is not a very good
system that cant control that" ; ie making more trouble. It is an .mde file
so they cannot get into the code but it still breaks and causes inconsistent
data.

It is really bothering me. In Excel all you have to say in the Sub is:

Application.EnableCancelKey = xlDisabled
Application.OnKey "^{Break}", ""

and you are protected throughout that Sub.

How is this handled in Access?

Thank you for your help.

Sleepless in the Access World
 
Hi Steven,

Tools, Startup, --> uncheck Use Access Special Keys

Warm Regards,
Crystal
*
(: have an awesome day :)
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*
 
Crystal,

Thank you for your response. That does not do what I need. Even if I
uncheck the special keys it will still break the code on Ctrl-Break. Someone
told me the program has to be completely developed with Visual Studio Tools
2005 Developers Extention to stop the Ctrl-Break from breaking the code.

Sound right?

Steven
 
Hi Steven,

To be honest, I have never tried to disable the users... just remembered
seeing that on the menu and thought it might be what you were looking for

If you are right about Visual Studio Tools (it certainly sounds
reasonable since Vis Stud owns VBA), then if there is a vba way to do it
without Visual Studio, it may involve remapping the keyboard ... or, if
possible, trapping and testing the key presses...

I will say that you should use

~~~~~~~~~~
dim strSQL as string
strSQL = "UPDATE table01...........;"
debug.print strSQL
Currentdb.Execute strSQL
~~~~~~~~~~~~~~~~~~~~~

instead of DoCmd.RunSQL

then you don't have to SetWarnings=False and Echo=False :)

~~~~~~~~~~~~~~~~~~~~~

** debug.print ***

debug.print strSQL

--> this prints a copy of the SQL statement to the debug window (CTRL-G)

After you execute your code, open the Debug window
CTRL-G to Goto the debuG window -- look at the SQL statement

If the SQL statement has an error

1. Make a new query (design view)

2. choose View, SQL from the menu
(or SQL from the toolbar, first icon)

3. cut the SQL statement from the debug window
(select, CTRL-X)

4. paste into the SQL window of the Query
(CTRL-V)

5. run ! from the SQL window
-- Access will tell you where the problem is in the SQL

'~~~~~~~~~~~~~~

the debug window, also called the immediate window, is another good
resource. When you are executing code, you can query the value of any
variable, field, control, ...

~~~

it is also a good idea to refresh tables (and possibly DoEvents) after
each action query

here is an expanded example for the currentdb.execute code I gave you
above...

'~~~~~~~~~~~~~~~~~~~~~
Dim strSql As String

'create SQL to append records
strSql = "INSERT INTO Tablename (numField, strField, dateField) " _
& " SELECT " & Me.SomeD _
& ", '" & me.someText & "'" _
& ", #" & me.someTDate & "#" _
& ";"

'comment this out after debugged
'Debug.Print strSql

'run the SQL statement
CurrentDb.Execute strSql

'refresh the table definitions with records
'added/changed by other persons/processes
CurrentDb.TableDefs.Refresh

'wake-up call for Access
DoEvents

'------------------ whatever applies
'requery the form
Me.Requery

'requery the subform
Me.subform_controlname.Requery
'------------------

'~~~~~~~~~~~~~~~~~~~

Warm Regards,
Crystal
*
(: have an awesome day :)
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*
 
Steven said:
Crystal,

Thank you for your response. That does not do what I need. Even if
I uncheck the special keys it will still break the code on
Ctrl-Break. Someone told me the program has to be completely
developed with Visual Studio Tools 2005 Developers Extention to stop
the Ctrl-Break from breaking the code.

Sound right?

Steven
Click to expand...

I *thought* the AllowBreakIntoCode "kind of" defaulted to false, and
that when toggling the AllowSpecialKeys (to false), it would disallow
Ctrl+Break to break into code.

You could of course try something like the following air code

dim db as dao.database
dim prp as dao.property

set db = dbengine(0)(0)
with db
on error resume next
.properties("AllowBreakIntoCode").value = false
if err.number = 3270 then
err.clear
set prp = .createproperty( _
"AllowBreakIntoCode", dbboolean, false)
db.properties.append prp
end if
end with

Note - AllowSpecialKeys must be set to false too, and you probably
need to close and reopen the db for it to have an effect. Would that
make any difference?

Or could it be that the Ctrl+Break you're doing, affects the running
of the queries? If so (or rather, anyway), use the .execute method of
either the DAO database object, or an/the ado connection

dim db as dao.database
set db = dbengine(0)(0)
db.Execute <yourSQL>, dbFailOnError
db.Execute <moreSQL>, dbFailOnError
....

Another thing, I will sometimes open a form with the acDialog option
when executing something that might take a bit of time. This will
inform the user someting is going on, perhaps have a bit of progress
bar etc. The users are unable to close this form the UI, it only
closes when when the routine opening the form, exits.
 
Back
Top