Greg_Beam
asked on
MS Access VBA Sub with update query not presenting message
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
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
"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
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
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
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
ASKER
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
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
"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
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
ASKER
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_spre adsheet_Cl ick()
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
Option Compare Database
Private Sub Import_non_MAPS_based_spre
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Docmd.setwarnings true