Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 387
  • Last Modified:

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
0
Sivasan
Asked:
Sivasan
  • 6
  • 5
1 Solution
 
mbizupCommented:
Try this:

On Error Got to EH
docmd.openquery "ST"
'  
' The Rest of your COde goes here

Exit Sub

EH:
     if Err.number = {place the number of the error you are describing here} Then
             Resume Next
    else
          msgbox "Error " & err.number & ": " & err.description
end sub

Open in new window

0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
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
0
 
SivasanAuthor Commented:
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
0
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
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
0
 
SivasanAuthor Commented:
Execute Method? can you please explain?
thx
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
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.
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
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
0
 
SivasanAuthor Commented:
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
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Not sure what is happening. First, let's see what the error is you are getting:

ErrTrap:
   MsgBox "Error: & " Err. Number & "  " & Err.Description

Resume Done
0
 
SivasanAuthor Commented:
Figured out. Thanks a lot MX
0
 
SivasanAuthor Commented:
Thanks a million for your help
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
What did you find ?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

  • 6
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now