Set Warnings problem

[Skip Bisconer]

I have this macro set to run from the Close Form button as the form closes but the warnings still occur.

Should I have this attached to a different event? Am I using the code incorrectly? In Excel I would use BeforeClose but it doesn't appear to be an option here.

Private Sub Form_Close()
On Error GoTo Error
DoCmd.SetWarnings False
DoCmd.RunMacro "mcrUpdatePolicyByEmpIDAfterNewAdd"
DoCmd.SetWarnings True

Error:
DoCmd.SetWarnings True
End Sub

 

[Mile-O]

Comment out this line (On Error GoTo Error) and see if there's an error occurring in the code.

 

[Skip Bisconer]

There are no errors or halts when I comment out the error trap. The Deletes and Append macro runs with all the warnings , which is what I am trying to avoid.

 

[gemma-the-husky]

Private Sub Form_Close()
On Error GoTo Error
DoCmd.SetWarnings False
DoCmd.RunMacro "mcrUpdatePolicyByEmpIDAfterNewAdd"
DoCmd.SetWarnings True

[COLOR="Red"]EXIT SUB[/COLOR]

Error:
DoCmd.SetWarnings True
End Sub

you needexit sub after the setwarnings true - your code is just dropping into the error handler

 

[Skip Bisconer]

Doesn't seem to work for me. I am wondering if it has something to do with Close event, maybe the form is closing before the code can run? Is there a BeforeClose optioned maybe named something else in the control events?

Here's my edited code

Private Sub Form_Close()
On Error GoTo Error
DoCmd.SetWarnings False
DoCmd.RunMacro "mcrUpdatePolicyByEmpIDAfterNewAdd"
DoCmd.SetWarnings True
Exit Sub
Error:
DoCmd.SetWarnings True
End Sub

 

[gemma-the-husky]

sorry, i looked quickly, and thought this was a error handler

im not sure now - perhaps macros ignore setwarnings

are you getting errors?, or just advisory messages?

can you set the warnings off in the macro?

 

[Skip Bisconer]

I am getting the normal stops 2 for a delete query and 2 for an append query.

 

[missinglinq]

You might try running the SetWarning commandsfrom within the macro.

 

[Skip Bisconer]

How might I do that? Run Code Action? I have no action option to select SetWarnings in my macro process.

 

[missinglinq]

I think there's a RunCommand option. I, like most people here, don't use Macros with the exception of sometimes using AutoExec or AutoKeys.

 

[unclejoe]

What is your macro doing? Just a guess….

Open up your macro "mcrUpdatePolicyByEmpIDAfterNewAdd" and check whether is there any SetWarning in your “Action” field and at the bottom of the “Action Arguments” is it set to “No” and at the end of the field set it to back to “Yes”.

If your macro running a "named" Action Query built by the Query Editor, go to Tools- Option - Edit/Find - Confirm - remove the checked boxes. (For A2K version). This will remove the warnings permanently.

How might I do that? Run Code Action? I have no action option to select SetWarnings in my macro process.

 

[Skip Bisconer]

I think I solved my problem by making two Public Functions one SetWarningsFalse and the other SetWaringsTrue in their own module and was able to use them at the beginning and the end of the macro. This worked in my process and I verified the warnings were indeed tuned back on.

I need some one to tell me if I am creating myself a problem by doing it the way it is now.

I don't know if it's my version 2007 or all versions of 2007, but I don't have a SetWarning action option to select.

 

[Mike375]

SetWarnings is a distinct macro action.

RunCommand produces a list of actions. Many of the actions are what is in the toolbar from Edit etc., such things as SaveRecord etc

RunCode is as the name suggests and is for running a module. Similar to Call in code.

It does appear that sometimes a macro can do it but code won't and likewise, code will do it but the macro won't.

When I converted from A95 to A2003 I had probems and had to get an Access/Dbase developer company to come to my aid Cost plenty. They were very tuned in with Microsoft as I got their name from Microsoft. At the time there was a thread on dbForums where a macro was working but code was not. From memory it was making visible/invisible on a subform. I asked the bloke from the developer company about that situation and his answer was along the lines of "sometimes you have a situation where the macro will work but code will not work and sometimes the code will work and the macro will not work". I asked him was there some technical reason and his answer was "who the f knows"

The fellow from the developer company said that most people tend to be either all macro or all code and then do their own work around as opposed to comparing macro and code actions.

But there are strange things that happen. In a couple on instances I have had to use code because the macro was not working. In this case the SaveRecord was like the action line was simply not there. This is opening a form based on the same table and getting record locking etc. But with code it was fine. Yet in 99.9999999999999999999% of cases the macro SaveRecord in a similar macro action works.

The other thing that can happen, but not real often, is a macro or code does not read conditions correctly. Can be as simple as "do this if Null etc". and it acts as if it is always null or has an entry. The only solution is a delete and start again.

Individual computers also seem to come into the equation at odd times. Last year one of the insurance company premium quote systems was not handling premiums based on discounts Vs benefit amount. It was missing stuff like >=250,000 and <=$500,000 etc. Like it was not there. The quote system in question is on Access tables and only some agents had the problem.

As the bloke from the developer company said "who the f knows"

 

[gemma-the-husky]

i cannot envisage anything you can do in a macro that you cant do in vba, other than autokeys macro, and autoexec macro

this must surely be so, as you can convert macros to code, with nice error-handling built in

 

[boblarson]

SetWarnings is a distinct macro action.

Sorry, wrong about that one. Set Warnings is available in macros AND in VBA.

 

[Mike375]

i cannot envisage anything you can do in a macro that you cant do in vba, other than autokeys macro, and autoexec macro

this must surely be so, as you can convert macros to code, with nice error-handling built in

Agree. But I am not referring to what you can do or can't do with either but rather the odd time where one works and the other does not work and for something that can be done by either.

As a side note some big macros won't work when converted to code, that is, using Access converter. Also, converting all the macros on a form in one go with Access converter can be a failure.

Don't blame me, call Bill Gates

 

[Mike375]

Sorry, wrong about that one. Set Warnings is available in macros AND in VBA.

By that I meant it is a separate macro action as opposed to being on the RunCommand list.

 

[gemma-the-husky]

i would be most surprised to see any macro steps that cannot be reproduced by vba - do you have an example.

 

[Mike375]

i would be most surprised to see any macro steps that cannot be reproduced by vba - do you have an example.

No and because again, I am not referring to tasks that can be done with a macro and not code, such as autoexec. I am referring where the code is not working (but it should) and also where a macro has not worked but should work. I had a macro failing on SaveRecord and I have both macro and code fail to read whether an unbound textbox was null or had an X in there.

I suspect (wild guess) that it maybe some type of corruption. A few years ago I had macor fail to read an unbound text box for null or an X. The macro was part of a very large macro and was in the middle of a lot form opening and closing with SetValue actions running. It was working fine on the other computers. I made a copy of the macro and renamed it back to original and still if falied to read the textbox. I then printed out the macro and made a new macro and typed in the entries from the printout and it worked.

Here is a module that has failed twice. Both failures were one after the after and for the same record. It worked on the third attempt. The macro in the code macro11timersingle is a Setvalue action for [Forms]![PrintandClose].[TimerInterval] is 0. The code below is preceded by the copying of a Word.doc and the following pastes the doc into a new record. The two failures were simply nothing happened. No error or msg. The same applies to the macro I mentioned above. The earlier part of the code (the public function is called)all worked. That involves opening specific Word.doc, inserting Access data into Bookmarks and printing.

Public Sub StoreDocumentInDB()

DoCmd.OpenForm "LetterStoreRecord", acNormal, "", "", acEdit, acNormal
DoCmd.GoToRecord , "", acNewRec

DoCmd.SelectObject acForm, "LetterStoreRecord", False
DoCmd.GoToControl "Letter"
DoCmd.DoMenuItem 0, 1, 3, 0, acMenuVer70 ' Form, Edit, Paste

DoCmd.DoMenuItem 0, 5, 4, 0, acMenuVer70 ' Form, Records, Save Record
DoCmd.OpenForm "PrintandClose", acNormal, "", "", acEdit, acNormal
Forms!PrintAndClose.RecordSource = "GridJoinNamesSingle"
DoCmd.RunMacro "Macro11TimerSingle", , ""

Forms!LetterStoreRecord!Name = Forms![12ProspectT]![CL Surname]
Forms!LetterStoreRecord!NameNumber = Forms![12ProspectT]!NameNumber
Forms!LetterStoreRecord!Done = Now()

DoCmd.Close acForm, "LetterStoreRecord"

End Sub

 

[gemma-the-husky]

i must confess i dont understand exactly what you are doing - for instance, i believe the style docmd.domenuitem is now outmoded, although i am sure it still works.

however, i would expect that if you are following the sequence

a) load a word doc
b) process the word doc

then the access code is proceeding to process statements b) before the doc has been properly loaded. I am not sure of the exact technique to use to ensure completion of such asynchronous processes, but perhaps there are some suitable API calls.

or perhaps this can be remedied by a loop that waits for the load process to complete, eg (ensuring that the text to be copied to be non null)

if the code falls through yo process statements too quickly, it won't produce a run-time error, but it will give unexpected and spurious results.