[Last Call] Learn how to a build a cloud-first strategyRegister Now

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

An alternative to DoCmd.Setwarnings FALSE

Hi,

im looking for an alternative to using the line of code

DoCmd.SetWarnings False

If my code does not execute, the warnings stay off, and when i try and run the code

DoCmd.SetWarnings True by itself, theyre not turning back on

Thanks
0
Seamus2626
Asked:
Seamus2626
  • 4
  • 3
  • 2
  • +2
3 Solutions
 
mbizupCommented:
IF you are using action queries written as VBA strings, use CurrentDB.Execute instead of setWarnings:

Dim strSQL as string
strSQL = "DELETE * FROM YourTable WHERE Something = 'something else'"
Currentdb.Execute strSQL, dbfailonerror

Open in new window


The advantages are:

1.  No 'warnings'
2.  Meaningful error messages informing you if your action query failed (if you use the dbFailOnError option)
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
To expand on what mbizup said (which is definitely the preferred method) ... :


Another reason to avoid  SetWarnings is because If your forget to execute a SetWarnings True ... and/or you code errors out ... leaving a False condition in effect ... you will run into a load of other undesirable issues ... such as ...

You open a form in Design view .... you make some changes ... you decide NOT to keep those changes ... you click the Close button ... expecting to see a WARNING message asking Do you want to ....
You WILL NOT see that message and the changes will be saved ... if a prior SetWarnings False is still in effect.  There are many other situations.

As suggested by mbizup, the 'best practice' approach is using the Exectute method of CurrentDB. This avoids the annoying warnings prompt altogether, and further allows you to trap unexpected errors, via the dbFailOnError option.

Further, you can optionally use the RecordsAffected property to confirm exactly how many records were successfully processed.

Example:

Function mCurrentDbExecuteSeveralQueries()

' some code here

Dim X1 As Long, X2 As Long, X3 As Long
On Error GoTo mCurrentDbExecuteSeveralQueries_Error

With CurrentDb
      .Execute "YourActionQuery1", dbFailOnError
       X1 = .RecordsAffected ' tells you how many records were processed
      .Execute "YourActionQuery2", dbFailOnError
       X2 = .RecordsAffected ' tells you how many records were processed
      .Execute "YourActionQuery3", dbFailOnError
       X3 = .RecordsAffected ' tells you how many records were processed
    ' and so on ....
End With

mCurrentDbExecuteSeveralQueries_Exit:
    Exit Function

mCurrentDbExecuteSeveralQueries_Error:
   ' Your error handling code here
Resume mCurrentDbExecuteSeveralQueries_Exit

End Function
0
 
PatHartmanCommented:
I agree with Miriam and Joe that the .execute method is better but on the occasions when I decide to use one of the built in DoCmd options, I keep two macros in my database (the only macros I use).  the first macro sets warnings off and the hourglass on.  And the second sets warnings on and the hourglass off.  I NEVER directly set the warnings off.  I ALWAYs use the macro.  That leaves me a clue (the hourglass) that warnings are still off.  Then I can run the other macro to turn them back on.  I have been burned by this so I am extremely careful but having the hourglass in my face is a good reminder.
0
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
Just say No to DoCmd.SetWarnings :-)
0
 
PatHartmanCommented:
I did.  My suggestion is to use a macro so you always remember to turn on the hourglass and that will annoy you enough to trigger the impulse to turn it off with the other macro.  Leaving warnings off is such a painful experience that people tend to not do it more than a couple of times.  The only reason it happens more than once is that most people don't recognize what they did to cause all their work to be lost the first time.   I know I didn't.  It had to happen to me a couple of times before I figured out the cause.  That was back in the early 90's but I still feel the pain.  

This is one of those situations where MS could have saved us a lot of grief by separating the messages about saving objects from the ones caused by queries updating data.  I have the same complaint about Honda.  I had a warning light come on recently and I had never seen it before so I checked in my manual and the essence of the description was - do not pass go, take the car directly to the dealer or it will blow up.  Turns out that I didn't tighten the gas cap enough when I filled up.  Do you think they could have assigned that particular warning message to a different, less urgent light?   They had at least three to choose from.   Software developers as well as engineers have choices and they don't always choose the most customer friendly solution.
0
 
Jack LeachCommented:
Just out of curiousity Pat, under what occasion might you decide to use DoCmd, and thus SetWarnings?  Personal preference, or some advantage that I can't think of?

In general, I tend to view almost all DoCmd methods as subpar shortcuts to better ways of doing things - OpenForm, OpenReport and Close are the only ones I can think of offhand that I use, and none of them throw warnings.  I haven't used DoCmd.RunSQL since I learned about Execute many years ago.

The Transfer*** methods can be helpful sometimes, but if I'm using those, I'm scrubbing the data "manually" first to make sure I've only got good imports - if I'm getting a warning there I've got a much bigger problem of Garbage In.

Cheers
0
 
PatHartmanCommented:
I wasn't recommending the use of the DoCmd option.  I was suggesting a method to make it safer.

When a query takes a lot of parameters that come from form fields, it can be a pain to set up using DAO and many newbees can't figure it out at all.  Typically, when I know I'm going to use DAO to run such a query, I don't reference the form fields but use nicer names instead.  I just changed one today.
 
Original version that references a form:
INSERT INTO tbl837P ( BillDays, RangeFromDT, RangeThruDT, BatchID, BillAmt, RecNum, TransmittalNum )
SELECT q837P_SourceFieldsBase.*, DateDiff("d",[CalcRangeFromDT],[CalcRangeThruDT])+1 AS BillDays, IIf([StartDT]<= [forms]![frmBatchCounts]![txtMinSelDT],[forms]![frmBatchCounts]![txtMinSelDT],[StartDT]) AS CalcRangeFromDT, IIf([BillThruDT]<=[forms]![frmBatchCounts]![txtMaxSelDT],[BillThruDT],[forms]![frmBatchCounts]![txtMaxSelDT]) AS CalcRangeThruDT, [forms]![frmBatchCounts]![BatchID] AS CalcBatchID, IIf([Abrv]="CAMFEE",[BillDays]*[PricePerUnit],[PricePerUnit]) AS CalcBillAmt, DCount("*","tbl837P","BatchID = " & [forms]![frmBatchCounts]![txtBatchID] & " AND CPID <= " & [CPID]) AS CalcRecNum, [forms]![frmBatchCounts]![txtBatchYY] & Format([forms]![frmBatchCounts]![txtBatchMM],"00") & Format([forms]![frmBatchCounts]![txtBatchSeq],"00") AS CalcTransmittalNum
FROM q837P_SourceFieldsBase
WHERE (q837P_SourceFieldsBase.StartDT <= [forms]![frmBatchCounts]![txtMaxSelDT] AND q837P_SourceFieldsBase.BillThruDT >= [forms]![frmBatchCounts]![txtMinSelDT])
AND (q837P_SourceFieldsBase.ClientID = [forms]![frmBatchCounts]![cboClientID] or [forms]![frmBatchCounts]![cboClientID] Is Null)
AND (q837P_SourceFieldsBase.ProcedureID = [forms]![frmBatchCounts]![cboProcedureID] OR [forms]![frmBatchCounts]![cboProcedureID] Is Null);

Modified version with variable names:
INSERT INTO tbl837P ( BillDays, RangeFromDT, RangeThruDT, BatchID, BillAmt, TransmittalNum )
SELECT q837P_SourceFieldsBase.*, DateDiff("d",[CalcRangeFromDT],[CalcRangeThruDT])+1 AS BillDays, IIf([StartDT]<=[EnterMinSelDT],[EnterMinSelDT],[StartDT]) AS CalcRangeFromDT, IIf([BillThruDT]<=[EnterMaxSelDT],[BillThruDT],[EnterMaxSelDT]) AS CalcRangeThruDT, [EnterBatchID] AS CalcBatchID, IIf([Abrv]="CAMFEE",[BillDays]*[PricePerUnit],[PricePerUnit]) AS CalcBillAmt, [EnterBatchYY] & Format([EnterBatchMM],"00") & Format([EnterBatchSeq],"00") AS CalcTransmittalNum
FROM q837P_SourceFieldsBase
WHERE (q837P_SourceFieldsBase.StartDT <= [EnterMaxSelDT] AND q837P_SourceFieldsBase.BillThruDT >= [EnterMinSelDT])
AND (q837P_SourceFieldsBase.ClientID = [EnterClientID] or [EnterClientID] Is Null)
AND (q837P_SourceFieldsBase.ProcedureID = [EnterProcedureID] OR [EnterProcedureID] Is Null);

So the code went from:
    DoCmd.RunMacro "mWarningsOff"
    DoCmd.OpenQuery "q837P_AppendBatch"
    DoCmd.OpenQuery "q837P_UpdateRecNum"
    DoCmd.RunMacro "mWarningsOn"

Open in new window

To:
    Set db = CurrentDb()
    Set qd = db.QueryDefs!q837P_AppendBatch
        qd.Parameters!EnterMinSelDT = Me.txtMinSelDT
        qd.Parameters!EnterMaxSelDT = Me.txtMaxSelDT
        qd.Parameters!EnterBatchID = Me.txtBatchID
        qd.Parameters!EnterBatchYY = Me.txtBatchYY
        qd.Parameters!EnterBatchMM = Me.txtBatchMM
        qd.Parameters!EnterBatchSeq = Me.txtBatchSeq
        qd.Parameters!EnterClientID = Me.cboClientID
        qd.Parameters!EnterProcedureID = Me.cboProcedureID
            
    qd.Execute
        
    Set qd = db.QueryDefs!q837P_UpdateRecNum
        qd.Parameters!EnterBatchID = Me.txtBatchID
    qd.Execute
    
    qd.Close
    
    Set qd = Nothing
    Set db = Nothing

Open in new window

If I hadn't changed the query, the parameters would have looked like:
qd.Parameters("[forms]![frmBatchCounts]![txtMinSelDT]") = Me.txtMinSelDT  'I think - I can't remember the syntax you have to use to include all those special characters
0
 
Seamus2626Author Commented:
Thanks for all the input guys and sorry for the late reply, can someone confirm this is the way to go and then il divvy up the points, thanks


Private Sub Import_Files_Click()

Dim strSQL As String


DoCmd.OpenQuery ("Append_LinkedTable_To_StaticTable")


CurrentDb.Execute strSQL, dbFailOnError



MsgBox ("Files transferred to static tables")


End Sub
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
"can someone confirm "
Well, not exactly ...
You would not use OpenQuery at all.

All you need is:

CurrentDb.Execute "Append_LinkedTable_To_StaticTable", dbFailOnError

BUT ... you also need error trapping in case the action query fails.  See my example I posted above.
0
 
Seamus2626Author Commented:
Hi all,

The code is turning off my warnings and not turning them back on,  can someone see what im doing wrong here




Private Sub Import_Files_Click()



Dim strSQL As String

strSQL = "INSERT INTO All_Data_Static SELECT [Combined Data].* FROM [Combined Data];"


CurrentDb.Execute strSQL, dbFailOnError

MsgBox ("Files transferred to static tables")


End Sub
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
Not sure what code you are referring to?
The code above has nothing to do with SetWarnings ... ?
0
 
mbizupCommented:
Yup... if your warnings are off, it is because of some other code somewhere else in your database.

If you need to toggle the warnings back on, just type into the immediate window in the VBA editor:

DoCmd.SetWarnings True

Open in new window


(CurrentDB.Execute does not affect warnings at all, which is one of the advantages we noted earlier)
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 4
  • 3
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now