Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

check for error on query on form load in access

Posted on 2013-05-13
12
Medium Priority
?
377 Views
Last Modified: 2013-05-17
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
Comment
Question by:Sivasan
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
12 Comments
 
LVL 61

Expert Comment

by:mbizup
ID: 39163172
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
 
LVL 75
ID: 39163220
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
 

Author Comment

by:Sivasan
ID: 39163268
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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 75
ID: 39163275
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
 

Author Comment

by:Sivasan
ID: 39163290
Execute Method? can you please explain?
thx
0
 
LVL 75
ID: 39163304
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
 
LVL 75
ID: 39163311
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
 

Author Comment

by:Sivasan
ID: 39163418
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
 
LVL 75

Accepted Solution

by:
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 2000 total points
ID: 39163616
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
 

Author Comment

by:Sivasan
ID: 39175325
Figured out. Thanks a lot MX
0
 

Author Closing Comment

by:Sivasan
ID: 39175327
Thanks a million for your help
0
 
LVL 75
ID: 39175392
What did you find ?
0

Featured Post

Does Your Cloud Backup Use Blockchain Technology?

Blockchain technology has already revolutionized finance thanks to Bitcoin. Now it's disrupting other areas, including the realm of data protection. Learn how blockchain is now being used to authenticate backup files and keep them safe from hackers.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

721 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question