DoCmd.SetWarnings not suppressing warnings.

[PuddinPie]

Hello all,

I'm having issues with suppressing warnings. As you can see from the quoted code below i am using DoCmd.SetWarnings (0) to supress them but I'm still getting the insert into warnings popping up instead of doing them silently or suppressed. Does anyone have any idea why this would be happening or how to correct it?

Dim db As DAO.Database
Dim bedb As DAO.Database
Dim strBELocation As String
Dim intResult As Integer
Dim strDelTbl1 As String
Dim strDelTbl2 As String
Dim strDelTbl3 As String
Dim strDelTbl4 As String
Dim strDelTbl5 As String
Dim strQryTbl1 As String
Dim strQryTbl2 As String
Dim strQryTbl3 As String
Dim strQryTbl4 As String
Dim lngRecCount As Long
Dim lngRec As Long
On Error GoTo Error_Handler

strBELocation = DLookup("BEHome", "tblHome")
Set db = CurrentDb
Set bedb = DBEngine.OpenDatabase(strBELocation)
lngRecCount = 11
lngRec = 0
intResult = MsgBox("The following action will refresh all data. Do you wish to proceed?", vbOKCancel, "COE Letter Database - Import Confirmation")

If intResult <> 2 Then
DoCmd.SetWarnings (0)

strDelTbl1 = "DELETE tblSonageTmp.* FROM tblSonageTmp;"
strDelTbl2 = "DELETE tblGWLCOE.* FROM tblGWLCOE;"
strDelTbl3 = "DELETE tblGWLClaimsPayee.* FROM tblGWLClaimsPayee;"
strDelTbl4 = "DELETE tblCOEwithRandomNumbers.* FROM tblCOEwithRandomNumbers;"
strDelTbl5 = "DELETE tblGWLCOE.* FROM tblGWLCOE IN '" & strBELocation & "'"

strQryTbl1 = "INSERT INTO tblGWLCoe ( [Pay Status], [Prof Code], [Group Code], Edition, " & _
"[Pay Freq], [Contract Type], [Effective Date], [Commence Date], [Expiry Date], " & _
"Lang, Payee, Name, [Annuitant1 Last], [Annuitant1 First], [Annt1 Birth], " & _
"[Annuitant2 Last], [Annuitant2 First], [Annt2 Birth], Address1, Address2, Address3, Address4 )" & _
"SELECT tblSonageTmp.[Pay Status], tblSonageTmp.[Prof Code], " & _
"tblSonageTmp.[Group Code], tblSonageTmp.Edition, tblSonageTmp.[Pay Freq], " & _
"tblSonageTmp.[Contract Type], tblSonageTmp.[Effective Date], " & _
"tblSonageTmp.[Commence Date], tblSonageTmp.[Expiry Date], " & _
"tblSonageTmp.Lang, tblSonageTmp.Payee, tblSonageTmp.Annuitant1 AS Name, " & _
"AnntName1([Annuitant1],2) AS [Annuitant1 Last], " & _
"AnntName1([Annuitant1],1) AS [Annuitant1 First], tblSonageTmp.[Annt1 Birth], " & _
"AnntName2([Annuitant2],2) AS [Annuitant2 Last], " & _
"AnntName2([Annuitant2],1) AS [Annuitant2 First], tblSonageTmp.[Annt2 Birth], " & _
"tblSonageTmp.Address1, tblSonageTmp.Address2, tblSonageTmp.Address3, " & _
"tblSonageTmp.Address4 FROM tblSonageTmp;"
strQryTbl2 = "INSERT INTO tblGWLClaimsPayee ( [Payee number] )" & _
"SELECT tblGWL_Contract.ContractNum " & _
"FROM tblGWL_Contract;"
strQryTbl3 = "INSERT INTO tblCOEwithRandomNumbers ( [Pay Status], [Prof Code], [Group Code], " & _
"[Tax Stutus], Edition, [Pay Freq], [Contract Type], [Effective Date], " & _
"[Commence Date], [Expiry Date], Lang, Payee, Name, [Annuitant1 Last], " & _
"[Annuitant1 First], [Annt1 Birth], [Annuitant2 Last], [Annuitant2 First], " & _
"[Annt2 Birth], Address1, Address2, Address3, Address4, Quarter, Status, " & _
"[Death Claim Indicator] )" & _
"SELECT tblGWLCoe.[Pay Status], tblGWLCoe.[Prof Code], tblGWLCoe.[Group Code], " & _
"tblGWLCoe.[Tax Stutus], tblGWLCoe.Edition, tblGWLCoe.[Pay Freq], " & _
"tblGWLCoe.[Contract Type], tblGWLCoe.[Effective Date], tblGWLCoe.[Commence Date], " & _
"tblGWLCoe.[Expiry Date], tblGWLCoe.Lang, tblGWLCoe.Payee, tblGWLCoe.Name, " & _
"tblGWLCoe.[Annuitant1 Last], tblGWLCoe.[Annuitant1 First], tblGWLCoe.[Annt1 Birth], " & _
"tblGWLCoe.[Annuitant2 Last], tblGWLCoe.[Annuitant2 First], tblGWLCoe.[Annt2 Birth], " & _
"tblGWLCoe.Address1, tblGWLCoe.Address2, tblGWLCoe.Address3, tblGWLCoe.Address4, " & _
"tblGWLCoe.Quarter, tblGWLCoe.Status, tblGWLCoe.[Death Claim Indicator] " & _
"FROM tblGWLCoe;"
strQryTbl4 = "UPDATE tblGWLCoe INNER JOIN tblGWLClaimsPayee ON tblGWLCoe.Payee = " & _
"tblGWLClaimsPayee.[Payee number] SET tblGWLCoe.[Death Claim Indicator] = 'Y';"
Call pcProgress(0, lngRecCount, "Deleting Current Data")
DoCmd.RunSQL strDelTbl1
Call pcProgress(1, lngRecCount, "Deleting Current Data")
DoCmd.RunSQL strDelTbl2
Call pcProgress(2, lngRecCount, "Deleting Current Data")
DoCmd.RunSQL strDelTbl3
Call pcProgress(3, lngRecCount, "Deleting Current Data")
DoCmd.RunSQL strDelTbl4
Call pcProgress(4, lngRecCount, "Deleting Current Data")
DoCmd.RunSQL strDelTbl5
Call pcProgress(5, lngRecCount, "Copying Mainframe Information")
DoCmd.TransferText acImportDelim, "Sonage Import Specification", "tblSonageTmp", DLookup("RefListDesc", "tblRefList", "[RefListTitle] = 'COE_GWL_File'")
Call pcProgress(6, lngRecCount, "Configuring Tables")
DoCmd.RunSQL strQryTbl1
Call pcProgress(7, lngRecCount, "Configuring Tables")
DoCmd.RunSQL strQryTbl2
Call pcProgress(8, lngRecCount, "Configuring Tables")
DoCmd.RunSQL strQryTbl3
Call pcProgress(9, lngRecCount, "Configuring Tables")
DoCmd.RunSQL strQryTbl4
Call pcProgress(10, lngRecCount, "Configuring Tables")
DoCmd.RunSQL "INSERT INTO tblGWLCOE IN '" & strBELocation & "' " & _
"SELECT tblGWLCOE.* FROM tblGWLCOE"
Call pcProgress(11, lngRecCount, "GWL Import Process Complete")
DoCmd.SetWarnings (-1)
MsgBox "All processes have completed successfully.", , "COE Letter Database - Import Process"
Call pcProgress(0, 0, "")
Else
Exit Function
End If

db.Close
Set db = Nothing
bedb.Close
Set bedb = Nothing

Exit Function

Error_Handler:
Call pcProgress(0, 0, "")
MsgBox "An error has occured. Please record the following error number and message " & _
"so that it can be provided to technical support." & vbNewLine & vbNewLine & _
"Error # " & Err.Number & vbNewLine & _
"Error Message: " & Err.Description

 

[azfen]

Try: DoCmd.SetWarnings False

 

[PuddinPie]

I've tryed that as well and still have the same issue.

 

[Frothingslosh]

Edit: I'm an idiot. That's what I get for trying to answer and run a report at the same time.

 

[Catalina]

And next time please apply the code tags [#] to make the code easier to read.

Thanks.

 

[Simon_MT]

After you have declared:

DoCmd.SetWarnings False

You should also declare

DoCmd.SetWarnings True

At the end

Simon

 

[Isskint]

You are making calls to a sub procedure pcProgress. Does this change the state of the setwarnings? I am guessing this sub controls status bar text or bar and you possibly have a DoCmd.Setwarning False in there?

 

[pbaldy]

I had the same thought as Isskint. Further, to clarify apparent confusion, it's False to turn the warnings off (don't show them), True to turn them on (show them). From help:

Use True (–1) to turn on the display of system messages and False (0) to turn it off.

 

[PuddinPie]

I've figured it out. Access 2007 has issues running DoCmd.SetWarnings commands. For some reason it works sometimes but not others. I just used db.excuite instead it correctes everything. Thanks for you help.