If insert query fails, then

[redbull]

Hello. I have a BE and FE database, whenever someone clicks a button on the front end... an insert query updates the back end... so we can track usage.. the back end is one table.. With this many people using the system, at an average of 1000 clicks daily... I am worried that if the table is being used by one user, then another user will not be able to make the update.. basically I want to add redundancy to this process... so this is what I have...



-Original code, with zero redundancy...

Private Sub cmdAuthDatabase_Click()
Call AuthData
DoCmd.SetWarnings False
DoCmd.OpenQuery "qryFuncLog"
DoCmd.SetWarnings True

so this on click, runs the script, then does an insert query to the database....
---------------
-What I want to do is.......

Private Sub cmdAuthDatabase_Click()
     Call AuthData
     DoCmd.SetWarnings False
On Error GoTo Err:
     DoCmd.OpenQuery "qryFuncLog"
Err:
  DoCmd.OpenQuery "qryFuncLog1"
     DoCmd.SetWarnings True

SO if an error occours, it will point to another table and leave the insert there... HOWEVER while testing .... I hit a button on the front end... and the insert shows up in both tables.... as if there is always an error, or I did the Err: labeling wrong... any help is appreciated.

Actually while writing this I think I will try this as well...

Private Sub cmdAuthDatabase_Click()
  On Error GoTo Err:
 Call AuthData
 DoCmd.SetWarnings False
 DoCmd.OpenQuery "qryFuncLog"
 DoCmd.SetWarnings True
Err:
  DoCmd.SetWarnings False
  DoCmd.OpenQuery "qryFuncLog1"
  DoCmd.SetWarnings True
End Sub

Meh that did the same thing... made an entry into both tables... any idea?

[redbull]

qryfunclog code....

INSERT INTO tblFuncLog ( chrUser, chrFunction, dtmTimeStamp )
SELECT Environ("username") AS Expr1, [screen].[activecontrol].[name] AS Expr3, Now() AS Expr2;

qryfunclog1 code

INSERT INTO tblFuncLog1 ( chrUser, chrFunction, dtmTimeStamp )
SELECT Environ("username") AS Expr1, [screen].[activecontrol].[name] AS Expr3, Now() AS Expr2;

[redbull]

Just thought about it... is there a way in the SQL statement to see if that table is busy... searching now, but I am so soft handed in SQL I know this is a long shot

[pbaldy]

Code goes exactly where you tell it to go. In your case, you never told it to stop, so it just keeps executing lines until it gets to the end. You'd want to add

Exit Sub

in the appropriate spot. Here's how error handling is normally structured:

http://www.baldyweb.com/ErrorTrap.htm

Note the Exit Sub in the exit handler, to stop code from continuing into the error handler.