Solved

MS Access VBA Sub with update query not presenting message

Posted on 2010-09-09
7
405 Views
Last Modified: 2013-11-29
Hi,
My MS Access app has a VBA sub that has runs an update query.  The Sub is run as a result of clicking on a button on a form.  When the query is run directly, it presents the message, if applicable, "Microsoft Office Access can't update all of the records in the update query".  But when the query is run via clicking on the button, the message is not displayed.

I added On Error GoTo Err_PresentMessage at the top of the code and Err_PresentMessage:      
    MsgBox Err.Number & Err.Description
    Exit Sub
at the bottom, but the message still does not appear.

I would like to present all messages to the user and eventually intercept them with more detail about how to correct, etc.  How can I get the messages to appear?
Thanks,
Greg



0
Comment
Question by:Greg_Beam
  • 4
  • 2
7 Comments
 
LVL 4

Expert Comment

by:gozoliet
ID: 33642734
You might have warnings turned off. In vba just before query do:

Docmd.setwarnings true
0
 
LVL 75
ID: 33642757
"But when the query is run via clicking on the button, the message is not displayed."

That would normally only happen if ... you have a DoCmd.SetWarnings False command.

You will need this approach:

Public Function SomeFunction()

On Error Goto SomeFunction_Error

  '  whatever code
   
     CurrentDb.Execute "YourActionQueryName", dbFailOnError   ' ****

  '  more code  

SomeFunction_Exit:
Err.Clear
Exit Function

SomeFunction_Error:
    Select Case Err.Number
         Case <SomeErrorYouExpect1>   ' actual error number
                Mgsbox "SomeMessage1"
         Case <SomeErrorYouExpect2>  ' actual error number
                Mgsbox "SomeMessage2"      
           ' and so on for each expect error where you want a nice messge
         Case Else
                Msgbox "Unexpected Error occurred ...."
    End Select
    Goto SomeFunction_Exit

End Function    
0
 
LVL 75
ID: 33642770
Unfortunately, Docmd.setwarnings true (false)  is really not a good idea because it will often mask errors that you WANT to know about ... and may give the false illusion that the action query worked correctly.  

Everyone should get away from use Docmd.setwarnings.  The Execute Method of the CurrentDb object is much safer and more flexible, yet avoids the usually annoying 'warning' messages like "You are about to append N records ...", etc.

mx
0
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 

Author Comment

by:Greg_Beam
ID: 33642885
It seems there are errors and warnings, which are handled differently, it that true?

So the On Error would trap errors, but since the message I am trying to present is a warning, it is not trapped by On Error?

I explicity set Docmd.setwarnings true just before the On Error, and still it does not work.

I am setting a QueryDef to the query name and Opening the RecordSet.  Does this prevent the warnings, annoying and otherwise?  If I continue to use it, can I turn warnings on?

Greg
0
 
LVL 75
ID: 33642921
"it that true?"
Yes.

"So the On Error would trap errors, but since the message I am trying to present is a warning, it is not trapped by On Error?"

It's a warning of sorts in the DoCmd.SetWarnings paragigm.  It's an Error in the Execute method ... which is what you what, because you want to trap that ... and display a nice 'what to do' message.

As I suggested, completely forget about using DoCmd ... and start using the Execute method for these operations ... and you will be way ahead of the game.

Check the VBA Help file for all the info on the Execute method ... which can be used with QueryDefs (and more) also ... and you will see all the other options.

mx
0
 

Author Comment

by:Greg_Beam
ID: 33647236
Here is the code.  I am doing all of the suggestions but do not get the warnings on the "Import update 00 Consolidated Mapping" update query, which uses Execute.

Option Compare Database

Private Sub Import_non_MAPS_based_spreadsheet_Click()

DoCmd.SetWarnings True

On Error GoTo Err_PresentMessage

Dim Db As DAO.Database
Dim qdf As DAO.queryDef
Dim rs As Recordset

Set Db = CurrentDb

Me.[Edit Description] = "check Batch Name"
Set qdf = Db.QueryDefs("Import check 01 Batch Name")
Set rs = qdf.OpenRecordset
If Not rs.EOF Then
    Me.[Spreadsheet Errors].SourceObject = "Query.Import check 01 Batch Name"
    Exit Sub
End If

Set qdf = Db.QueryDefs("Import update 00 Consolidated Mapping")
qdf.Execute

Set qdf = Nothing
Set Db = Nothing
                   
Exit Sub

Err_PresentMessage:                    
    MsgBox Err.Number & Err.Description
    Exit Sub
   
End Sub
0
 
LVL 75

Accepted Solution

by:
DatabaseMX (Joe Anderson - Access MVP) earned 500 total points
ID: 33648572
You did not add the dbFailOnError option as I showed

qdf.Execute  

s/b

qdf.Execute, dbFailOnError  '  *** You need this

mx
0

Featured Post

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Access 2016 5 53
Ms Access VBA Variables 6 26
Modal form 11 29
How do I assign a function with a parameter as the RowSource of a combo box? 9 42
QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

816 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now