Link to home
Start Free TrialLog in
Avatar of Seamus2626
Seamus2626Flag for Ireland

asked on

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
ASKER CERTIFIED SOLUTION
Avatar of mbizup
mbizup
Flag of Kazakhstan image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Just say No to DoCmd.SetWarnings :-)
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.
Avatar of Jack Leach
Jack Leach

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
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
Avatar of Seamus2626

ASKER

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
"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.
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
Not sure what code you are referring to?
The code above has nothing to do with SetWarnings ... ?
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)