Set Warnings Off


Just upgraded to Access 2007. There used to be a feature in Macros to Set Warnings to off - so when your macro was running update or append queries you didn't get a message.

How to you turn those off in Access 2007?


Sponsored Links:



is there a Macro for docmd.setwarning off instead of writing code in module? I can't find the Macro docmd? I am using Access2010.

thanks.




I have a form with a button which calls a macro to open a query that performs a single record append into a table. The table has a field that is indexed and does not allow duplicates.
I want to add the following to the macro: Set warnings off to eliminate all the message boxes that pop up--1 to inform that an action is being taken, 1 to inform that 1 record is being appended and 1 with very confusing text to inform the action cannot be taken if the record is already in the table. TMI and potentially confusing.
After setting warnings off I want to provide a message box that informs 'Item is Already in table' if the record exists in the table or a message box that informs '1 record has been added' if a record does not exist in the table.
I know this can't be all that difficult. I just can't get the sequencing right.
Help, please




I am developing an Access database to keep track of recurring tasks. When a task is completed, a date is entered and a button clicked. The code then has the record copied to a history table, then resets the start and due dates based on the frequency. I am getting an error when I run the code. The message just says "Error", so I am not sure what's wrong. Below is the code for the button. I realize it's rather complex, so if anyone is willing to help and could PM me, I would be glad to provide a link where you can download my project.

I really appreciate the help. Below is the code:

Private Sub Button96_Click()
'1. The record is copied to the "History" table for historical records.
'2. The Current Due Date changes to the next due date based on the recurring requirement specified
' (i.e. weekly, monthly, etc.), or if it is "one time", it deletes the record.
'3. If recurring, the Completed Date field goes blank (because it is reset).

On Error GoTo Button69_Err

Dim MyStr As String
Dim intErrNum As Integer


' Save the record first, by going to the next record, and then back to the current one.
' This is OK even if there is no next record, since it will go to a new record

DoCmd.GoToRecord , , acNext
DoCmd.GoToRecord , , acPrevious

If IsNull([completion date].Value) = True Then
MsgBox "The completion date is empty", vbExclamation, "Need completion date"
[completion date].SetFocus
Exit Sub
End If

If IsNull(Combo116.Column(0)) = True Then
MsgBox "Don't have a frequency field", vbExclamation, "Need frequency info"
Combo116.Dropdown
Exit Sub
End If


MyStr = "INSERT INTO History ( [OLD_ID#], [SUPERINTENDENT], [TASK DESCRIPTION], [REQUESTED_BY], [DUE DATE], [START DATE], " & _
"[COMPLETE DATE], [STATUS_COMMENTS], [COMMITTMENT], [GROUP], [INDIVIDUAL], [FREQUENCY] ) " & _
"SELECT " & Str([TASK NUMBER].Value) & " AS Expr1, " & Str([SUPERINTENDENT].Value) & " AS Expr2, " & _
Chr(34) & [TASK DESCRIPTION].Value & Chr(34) & " AS Expr3, " & Chr(34) & [REQUESTED_BY].Value & Chr(34) & " AS Expr4, " & _
"#" & [Text128].Value & "#" & " AS Expr5, " & "#" & [START DATE].Value & "#" & " AS Expr6, " & _
"#" & [completion date].Value & "#" & " AS Expr7, " & Chr(34) & [STATUS_COMMENTS].Value & Chr(34) & " AS Expr8, " & _
Chr(34) & [COMMITTMENT].Value & Chr(34) & " AS Expr9, " & Chr(34) & Combo116.Column(0) & Chr(34) & " AS Expr10, " & _
Chr(34) & GROUP_COMBO.Column(0) & Chr(34) & " AS Expr11, " & Chr(34) & [INDIVIDUAL].Value & Chr(34) & " AS Expr12;"


DoCmd.SetWarnings False ' Set warning off
DoCmd.RunSQL MyStr ' Add current record to History table


If Combo116.Column(2) = 0 Then ' If the frequency is "One Time" or "Other", delete current record
'MsgBox "This record will be deleted"
MyStr = "Delete Task.[ID#] FROM Task WHERE (((Task.[ID#])=" & Str([TASK NUMBER].Value) & "));"
DoCmd.GoToRecord , , acNext ' Go to a new record
DoCmd.RunSQL MyStr ' Delete the record that was active before
Me.Requery ' Requery the form after a record is deleted
DoCmd.GoToRecord , , acFirst ' Go to the first record

Else ' Otherwise, it's a recurring task, so set its properties

'MsgBox "This record will be incremented with " & Str(Combo116.Column(2)) & " " & Combo116.Column(1)
Text128.Value = DateAdd(Combo116.Column(1), Combo116.Column(2), Text128.Value) ' increment the date, if recurring
[START DATE] = DateAdd(Combo116.Column(1), Combo116.Column(2), [START DATE])
[completion date].Value = Null ' Completion date goes blank
DoCmd.GoToRecord , , acNext ' save the record
DoCmd.GoToRecord , , acPrevious
End If



DoCmd.SetWarnings True ' Set warnings on




Button69_Exit:

Exit Sub




Button69_Err:

intErrNum = Err
Select Case intErrNum
Case 2105
MsgBox "This is a new task. Need more data to complete it", vbExclamation, "Need more data"
Case Else
MsgBox "Error!"

End Select
Resume Button69_Exit
End Sub




Whenever I make changes to objects in my Access 2000 database, I no longer receive the prompt to confirm changes (or deletions).

I have seen other threads addressing this issue and followed the advice posted. I do have macros that set warning off, but I always turn them on again at the end of the macro. Also, in my main form that gets loaded when
the database is opened, I have the following code in the form_load event:

DoCmd.SetWarnings (WarningsOn)

Also, in the Edit/Find tab under Tools, Options, the confirm boxes are all checked.

I must be missing something. On a related note, is it possible to display what the current Warnings setting is?

Thank you.




I am able to run an access “program” which prints a report using a stored procedure, this works fine. (the "program" is just a .bat file which calls an access database that has an AutoExec macro which sets warnings off, opens a form. On the open form event, it prints a report, closes the form (no save), set warnings on, and closes the access application (as exit, no saves again).

I am able to link sql tables into that report and pull specific data into the report and print that just fine.

When I add another linked table (could be the 2nd, 4th, 10th, or first, doesn’t’ matter), which is linked to access and not to sql, the “program” craps out. What happens is msaccess.exe is loaded in the task manager, an .ldb file is created, but nothing happens. As soon as it gets to the part where it would print the report, it fails. I know this also because I have done a kill(“c:test.doc”) before the printing and that part would work (just as a test to see where it was failing). However, running the .bat file (which only calls the database to run) works fine…only when you try to run the database as a stored procedure is when it fails.

So, one solution is to import everything into sql, which should be done anyways, but could cause multiple problems down the road. Have you ever heard / ran into this problem? Could this be some sort of weird permissions issue?

Do you think setting up a trigger would resolve this issue? (run executable program upon insert into sql table).

I am not sure where to post this as it is specifically a problem with access linked table, but only doesn't work from a sql stored procedure.

I am running access 2003 and am thinking about upgrading to 2007 to see if that works. The linked tables will be coming from an access 97 database.




After user enter the begining date and ending date, how to tell the report print out those date had enter? And how to set a warning if the query doesn't have any record that user want to see? Please help. Thank you!




I have a simple Output macro that exports a table to Excel. My warnings are off, but I still get asked if I want to replace the existing file. How do I eliminate this?




Hi everybody,

I have a database which opens a separate Excel file and modifies the contents such that it can be imported using the DoCmd.TransferSpreadsheet command.

Because that modification requires transposition, I have to use copy & paste, which means data is copied to the clipboard at various stages.

Unfortunately, this means when I then close the Excel workbook, I get a prompt asking if I want the contents of the clipboard to be retained.

Obviously I don't, but I want to skip / bypass the warning altogether.

If I was writing VBA directly in Excel, I would just use :


	Code:
	Application.CutCopyMode = False

but this is not recognised by Access.

I've tried defining Excel as an application object and passing the command through that, but also to no avail :


	Code:
	Dim objExcel As Object
Set objExcel = CreateObject("Excel.Application")
objExcel.CutCopyMode = False

I've even tried turning the warnings off temporarily within Access, no joy :


	Code:
	DoCmd.SetWarnings False
wbkMyWorkbook.Close savechanges:=True
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, strMyTable, strMyFile, True, strMySheet & "!"
DoCmd.SetWarnings True

Does anybody know how I can avoid this warning message?

Thanks

AOB




Hello,

I have some VBA code that is starting an instance of Excel, and running an excel macro against a txt file. It works fine, except as part of the macro I'm merging multiple cells in the same row together. Each time I attempt to do this I get an Excel warning message stating that only the data in the leftmost cell will be kept.

I've disabled warnings in my code after starting excel as follows:

' Start an Excel session
Set objxl = New Excel.Application
objxl.Visible = False
Workbooks.Open filename:="c:stock_tracker.xls"

' Turn warnings off
DoCmd.SetWarnings False

' Excel Macro Code
With objxlwrksht
Rows("1:1").Select
Selection.Insert Shift:=xlDown

etc....


Is there anything else I need to code to disable all excel warnings?

Thanks.




Hi, Big Jim here:

Most bizarre problem. I open Access from VB using the following code:

Public gAccess As New Access.Application

gstrDatabaseFullPath = [Whatever database I am opening.mdb]
gAccess.OpenCurrentDatabase gstrDatabaseFullPath
gAccess.DoCmd.SetWarnings True
gAccess.Visible = True

(References = DAO 3.51 Object Library and Access 10.0 Object Library)

Now when I do this, the application opens up just fine and I can see the database. However, the warnings are off, because I am forced to save dummy queries I just want to close out of and no warning messages come up when I attempt to modify data (DDL).

Any thoughts?

Thanks in advance,

Big Jim




Hello all,

Finally moved to 2010 from 2007 and I have one big problem - how can I turn off all security for my simple, quick-and-dirty application that is used strictly in house. The front-end on the PC, back-end on server.

When users open the database, they get two security warnings.

1) Application add-ins have been disabled.
2) Some active content has been disabled. The AutoExec macro is stopped here with error number 2001

Both have enable contect buttons that seem to do nothing. I have set everything off that I can in the Trust Center and it works fine on my PC. I've also done the same for a second PC. But the issue is that this will be going out to about 30 people and I shouldn't have to change them all.

Is there a simple way to shut down everything so this could run and give me some breathing room to set it up properly?

Thanks much to all who respond.




In my visual basic application I am accessing my Access macro. I set the Warnings Off. But, there is this parameter that needs be to be passed. So when I run the macro, the parameter does not come up and errors occur. How do I over come this?


John-




The confirm actions is set to off while running access to prevent the popup of windows with macro actions.
After creation of a runtime version of the application this settings is not valid in the runtime version. Where should I set this confirmations off for runtime ?




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




I would like to suppress the following warning:

"The text you entered is an item in the list
Select an item from the list, or enter text that matches one of the items in the list"

this comes up when i am pasting data from an excel spreadsheet into a data sheet in ms access.

The field in MS access is set to "limit to list" so that no garbage data can be stored.

In my code below i have created my own message box for the user to note the details of the failed paste, i only want my message to show.

I also seem to get my message pop up twice, once with the ATMID and then immediatly afterwards with no ATMID in the message for each failed paste.

my code is : Private Sub ATMID_BeforeUpdate(Cancel As Integer)
DoCmd.SetWarnings False
On Error GoTo AtmID_BeforeUpdate_Err


Me.KnownGLCode = DLookup("BankGlCode", "qry_Atms_Extended_Full", "[id] = " & Me.ATMID.Value)
Me.Van = DLookup("CITVanName", "qry_Atms_Extended_Full", "[id] = " & Me.ATMID.Value)

AtmID_BeforeUpdate_Exit:
Exit Sub

AtmID_BeforeUpdate_Err:
MsgBox "Atm : " & Me.ATMID & " : Does not exist", vbOKOnly, "Error is processing..."
Me.Undo
Resume Next


End Sub I also seem to get my message pop up twice, once with the ATMID and then immediatly afterwards with no ATMID in the message for each failed paste.




Hi

I have a macro that runs a make table query, once the table is created it then runs an update query on the table just created. It appears that every now and then the update fails to occur for no apparent reason (no notification as set warning is turned off). Do macros wait until each line is complete before moving on to the next? If not how can I create a delay until the make table is complete.

Many Thanks




This should be an interesting question which if answered should help people out in future. I've just about finished my first A2007 project which I'm deploying with the runtime licence.

In full access its easy to switch those warnings off but that cant be done, to my knowledge, in runtime version.

Equally, cant use setwarnings = false because its not an option in this case.

Any ideas out there?




Hi All

I would really appreciate any help you can give - I am not very good with the modules side of things in Access so laymans terms would be useful

I have multiple make table queries that are being linked to excel. The only problem is that the macro that intiates these queries has 3 message box/warnings per report (and Set Warnings can't be turned off in the macro in MSA2007).

I was told that you can create a module to run on "click" on a form say, and turn off warnings and turn them back on after the module has executed. My problem is I don't know how to do this!

For example my Make Table query names are:

XLAttendancePathways
XLAttendanceCasework
XLAttendees

Could anyone share the required code and explain how I would link this to a button control?

Any help would be so gratefully received.

Many thanks

Paul




Hi. I've got a Word document which has an embedded Excel object. I am changing values of the word document and its excel object from VBA in Access.

So far I have:

	Code:
	For Each oIShape In .ActiveDocument.InlineShapes                 
    If InStr(1, oIShape.OLEFormat.ProgID, "Excel") Then 
          oIShape.OLEFormat.Activate                 
          Set oWB = oIShape.OLEFormat.Object     
          'Replace tHE Values                     
          oWB.Sheets(1).Range("B3").Value = "Test"                             
    End If             
Next oIShape

This works fine, but the excel object remains activated, which means I can't use the "savetopdf" command. All i want to do is close the object and go back to word (just like clicking away from the excel object if I was doing it manually).

Anyone have an idea of how to do this?? it took me ages to get this far and thisis the last hurdle :-D