Sivasan
asked on
check for error on query on form load in access
Hi,
I have a form in access, On Open and On Load, I have an event that updates a table.
docmd.openquery "ST"
occasionally due a date field ST query may not update an throw an error.
I want to check if there is an error with the query, if error display a message and continue with loading the form. Right now, it just displays the message and doesn't open form in the event there is an error with the ST query.
thanks
S
I have a form in access, On Open and On Load, I have an event that updates a table.
docmd.openquery "ST"
occasionally due a date field ST query may not update an throw an error.
I want to check if there is an error with the query, if error display a message and continue with loading the form. Right now, it just displays the message and doesn't open form in the event there is an error with the ST query.
thanks
S
On Error Resume Next
CurrentDB.Execute "ST", dbFailOnError
If Err.Number>0 then
MsgBox "Error: & " Err. Number & " " & Err.Description
'Do whatever because of error
End If
mx
CurrentDB.Execute "ST", dbFailOnError
If Err.Number>0 then
MsgBox "Error: & " Err. Number & " " & Err.Description
'Do whatever because of error
End If
mx
ASKER
Hi Mx,
Sorry , can you please explain further.When I try it, the error message pops even if no error with "ST" I may be placing the code in the wrong spot.
Please see my event below. Basically, if error with DoCmd.OpenQuery "ST"
I just want it not to run DoCmd.OpenQuery "ST"
and just run the following
DoCmd.OpenQuery "U"
DoCmd.OpenQuery "T"
msgbox " Incorrect date report"
and then Open form
in case no error with DoCmd.OpenQuery "ST" then execute DoCmd.OpenQuery "ST"
then execute
DoCmd.OpenQuery "U"
DoCmd.OpenQuery "T"
msgbox " Success"
then Open form
Please see below let me know the right way to place code.
DoCmd.OpenForm "All", acNormal
DoCmd.SetWarnings False
DoCmd.OpenQuery "ST"
On Error Resume Next
CurrentDb.Execute "ST2Input", dbFailOnError
If Err.Number > 0 Then
DoCmd.OpenQuery "U"
DoCmd.OpenQuery "T"
MsgBox " my custom message here report wrong date"
else
DoCmd.OpenQuery "U"
DoCmd.OpenQuery "T"
MsgBox "My message Update Success"
End If
end sub
Sorry , can you please explain further.When I try it, the error message pops even if no error with "ST" I may be placing the code in the wrong spot.
Please see my event below. Basically, if error with DoCmd.OpenQuery "ST"
I just want it not to run DoCmd.OpenQuery "ST"
and just run the following
DoCmd.OpenQuery "U"
DoCmd.OpenQuery "T"
msgbox " Incorrect date report"
and then Open form
in case no error with DoCmd.OpenQuery "ST" then execute DoCmd.OpenQuery "ST"
then execute
DoCmd.OpenQuery "U"
DoCmd.OpenQuery "T"
msgbox " Success"
then Open form
Please see below let me know the right way to place code.
DoCmd.OpenForm "All", acNormal
DoCmd.SetWarnings False
DoCmd.OpenQuery "ST"
On Error Resume Next
CurrentDb.Execute "ST2Input", dbFailOnError
If Err.Number > 0 Then
DoCmd.OpenQuery "U"
DoCmd.OpenQuery "T"
MsgBox " my custom message here report wrong date"
else
DoCmd.OpenQuery "U"
DoCmd.OpenQuery "T"
MsgBox "My message Update Success"
End If
end sub
Get rid of ALL DoCmd.SetWarnings False / True ... bad idea as they mask out errors you WANT to know about.
They try what I posted , using the Execute Method,
mx
They try what I posted , using the Execute Method,
mx
ASKER
Execute Method? can you please explain?
thx
thx
The Execute Method runs an Action query. And with the dbFailOnError parameter, it will raise any error. Further, you do not get any of the annoying user prompts like you do with OpenQuery - which is why you had the DoCmd.SetWarnings False/True.
This is a **much safer** approach. You can check VBA Help for full details, however it's pretty much just this simple.
This is a **much safer** approach. You can check VBA Help for full details, however it's pretty much just this simple.
And you can do this also:
On Error Goto ErrTrap
With CurrentDB
.Execute "ST", dbFailOnError
.Execute "T", dbFailOnError
.Execute "U", dbFailOnError
' and all your queries .....
End With
' more code
Done:
Exit Sub
ErrTrap:
MsgBox "Error: & " Err. Number & " " & Err.Description
'Do whatever because of error
Resume Done
End Sub
On Error Goto ErrTrap
With CurrentDB
.Execute "ST", dbFailOnError
.Execute "T", dbFailOnError
.Execute "U", dbFailOnError
' and all your queries .....
End With
' more code
Done:
Exit Sub
ErrTrap:
MsgBox "Error: & " Err. Number & " " & Err.Description
'Do whatever because of error
Resume Done
End Sub
ASKER
Hi Mx,
Thank you for the detail explaination, but when I try your code above, EVEN when ST is good, it goes to ErrTrap and execute the code below it.
Basically if ST works then execute ST, U, T, say message " success" open form.
if ST has error, then only execute U, T say message " Problem with date" open form.
I did my code based on your post and even when ST is good, it still only executes U and T and Message " Problem with date" and open form
not sure why.
On Error GoTo ErrTrap
With CurrentDb
.Execute "ST", dbFailOnError
.Execute "U", dbFailOnError
.Execute "T", dbFailOnError
.End With
MsgBox "Success"
Done:
Exit Sub
ErrTrap:
MsgBox "Please inform dept of Date not in "
'DoCmd.OpenQuery "U"
'DoCmd.OpenQuery "T"
Resume Done
Thank you for the detail explaination, but when I try your code above, EVEN when ST is good, it goes to ErrTrap and execute the code below it.
Basically if ST works then execute ST, U, T, say message " success" open form.
if ST has error, then only execute U, T say message " Problem with date" open form.
I did my code based on your post and even when ST is good, it still only executes U and T and Message " Problem with date" and open form
not sure why.
On Error GoTo ErrTrap
With CurrentDb
.Execute "ST", dbFailOnError
.Execute "U", dbFailOnError
.Execute "T", dbFailOnError
.End With
MsgBox "Success"
Done:
Exit Sub
ErrTrap:
MsgBox "Please inform dept of Date not in "
'DoCmd.OpenQuery "U"
'DoCmd.OpenQuery "T"
Resume Done
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Figured out. Thanks a lot MX
ASKER
Thanks a million for your help
What did you find ?
Open in new window