SetWarnings Not Working

R

Rodger

Hello All,

I have a form that I use to print all my reports. The reports are actually
in Word and use different queries as the datasource. The queries create
temp table. When the code runs it gives me a warning about deleting the
table even though I have Warnings set to False. Here is the code that I
have.

myView is used to either open in Word (1) or Print to the Printer (2)

Private Sub myReports(myView)

On Error GoTo Err_myReports

Dim ReportPath
Dim oApp As Object, DocName, x, myDB
Dim dbsCurrent As Database, dbsPath As String
Dim myMessage, myStyle, myTitle, myResponse
Dim myQRY7, myQRYRealEstateAppraisal, myQRY1244_Step1, myQRY1244_Step2,
myQRY1244B_Step1
Dim myQRYTrackingSheet, myQRY_TEMP_OWNER_GUARANTOR
Dim myID

Set myDB = CurrentDb()

myID = [Forms]![frm_Reports]![cmbLoan]


If IsNull(Me.cmbLoan) Then
myMessage = "Please select a loan"
myTitle = "No Selection"
myStyle = vbCritical + vbOKOnly
myResponse = MsgBox(myMessage, myStyle, myTitle)
Exit Sub
Else
'DoCmd.SetWarnings False
Call updateTEMP_OWNERS

If DoesObjectExist("Tables", "tmp_Exhibit7") = 0 Then
Else
DoCmd.DeleteObject acTable, "tmp_Exhibit7"
End If

If DoesObjectExist("Tables", "tmp_RealEstateAppraisal") = 0 Then
Else
DoCmd.DeleteObject acTable, "tmp_RealEstateAppraisal"
End If

If DoesObjectExist("Tables", "tmp_Submission") = 0 Then
Else
DoCmd.DeleteObject acTable, "tmp_Submission"
End If

If DoesObjectExist("Tables", "tmp_CheckLists") = 0 Then
Else
DoCmd.DeleteObject acTable, "tmp_CheckLists"
End If

If DoesObjectExist("Tables", "tmp_Environmental") = 0 Then
Else
DoCmd.DeleteObject acTable, "tmp_Environmental"
End If


'qry_Exhibit7
myQRY7 = "SELECT [EXHIBIT 7].EX7_ID, [EXHIBIT 7].EX7_IMP_ID,
[EXHIBIT 7].EX7_AR_UNDER_30, [EXHIBIT 7].EX7_AR_30_59, [EXHIBIT
7].EX7_AR_60_89, [EXHIBIT 7].EX7_AR_OVER_90, [EXHIBIT 7].EX7_AP_UNDER_30,
[EXHIBIT 7].EX7_AP_30_59, [EXHIBIT 7].EX7_AP_60_89, [EXHIBIT
7].EX7_AP_OVER_90, [EXHIBIT 7].EX7_AR_CONCENTRATION, [EXHIBIT
7].EX7_AP_CONCENTRATION,
[EX7_AR_UNDER_30]+[EX7_AR_30_59]+[EX7_AR_60_89]+[EX7_AR_OVER_90] AS
AR_TOTAL, [EX7_AP_UNDER_30]+[EX7_AP_30_59]+[EX7_AP_60_89]+[EX7_AP_OVER_90]
AS AP_TOTAL, Format([AR_TOTAL]/[EX7_AR_OVER_90],'#%') AS AR_PERCENT,
Format([AP_TOTAL]/[EX7_AP_OVER_90],'#%') AS AP_PERCENT INTO tmp_Exhibit7 " &
_
"FROM [EXHIBIT 7] " & _
"WHERE [EXHIBIT 7].EX7_IMP_ID = " & myID
myDB.Execute (myQRY7)

'qry_RealEstateAppraisal
myQRYRealEstateAppraisal = "SELECT IMPORT.IMP_ID,
IMPORT.IMP_BOR_NAME, IMPORT.IMP_BOR_PROJECT_ADDRESS,
IMPORT.IMP_BOR_PROJECT_ADDRESS2, IMPORT.IMP_BOR_PROJECT_CITY,
IMPORT.IMP_BOR_PROJECT_STATE, IMPORT.IMP_BOR_PROJECT_ZIP,
IMPORT.IMP_BOR_PRINCIPAL, IMPORT.IMP_BOR_SBC, IMPORT.IMP_BOR_DBA,
IMPORT.IMP_BOR_EMAIL, SETUP.SET_COMPANY_NAME, SETUP.SET_COMPANY_OWNER,
SETUP.SET_ADDRESS, SETUP.SET_CITY, SETUP.SET_STATE, SETUP.SET_ZIP,
SETUP.SET_PHONE, SETUP.SET_FAX, SETUP.SET_EMAIL1 INTO
tmp_RealEstateAppraisal " & _
"FROM IMPORT, SETUP " & _
"WHERE IMPORT.IMP_ID= " & myID
myDB.Execute (myQRYRealEstateAppraisal)

'qry_myQRY1244_Step1
myQRY1244_Step1 = "SELECT LOAN_OFFICER.LASTNAME & ', ' &
LOAN_OFFICER.FIRSTNAME AS LOAN_OFFICER_NAME, UNDERWRITER.LASTNAME & ', ' &
UNDERWRITER.FIRSTNAME AS UNDERWRITER_NAME, IIf([IMP_BOR_EPC]=-1,'YES X
NO','YES NO X') AS EPC, [1244 A].[1244A_OMB_APPROVAL_NUMBER], [1244
A].[1244A_EXPIRATION_DATE], [1244 A].[1244A_Q1] AS Q1, [1244 A].[1244A_Q2]
AS Q2, [1244 A].[1244A_Q3] AS Q3, [1244 A].[1244A_Q4] AS Q4, [1244
A].[1244A_Q5] AS Q5, [1244 A].[1244A_Q6] AS Q6, [1244 A].[1244A_Q7] AS Q7,
[1244 A].[1244A_Q8] AS Q8, [1244 A].[1244A_Q9] AS Q9, [1244 A].[1244A_Q10]
AS Q10, [1244 A].[1244A_Q11] AS Q11, [1244 A].[1244A_Q12] AS Q12, [1244
A].[1244A_Q13] AS Q13, [1244 A].[1244A_Q14] AS Q14, [1244 A].[1244A_Q15] AS
Q15, [1244 A].[1244A_Q16] AS Q16, [1244 A].[1244A_Q17] AS Q17, [1244
A].[1244A_Q18] AS Q18, [1244 A].[1244A_Q19] AS Q19, [1244 A].[1244A_Q20] AS
Q20, [1244 A].[1244A_Q21] AS Q21, [1244 A].[1244A_Q22] AS Q22, [1244
A].[1244A_Q23] AS Q23, [1244 A].[1244A_CLOSING_COST], " & _
"MINORITY.MIN_CODE, MINORITY.MIN_DESCRIPTION,
YES_NO.YES_DESCRIPTION, IIf([IMP_BOR_RELOCATION]=-1,'YES X NO','YES
NO X') AS REL, IIf([IMP_BOR_EXISTING_BUSINESS]=-1,'X Existing ' & Chr(13)
& Chr(10) & ' New Business',' Existing ' & Chr(13) & Chr(10) & 'X New
Business') AS EXISTING, IMPORT.*,
[IMP_BOR_JOBS_TWO_YEARS]+[IMP_BOR_JOBS_RETAINED] AS JOB_TOTAL,
[IMP_BOR_PURCHASE_LAND_BUILDING]+[IMP_BOR_CONSTRUCTION_REMODELING]+[IMP_BOR_PURCHASE_EQUIPMENT]+[IMP_BOR_PROFESSIOAL_FEES]+[IMP_BOR_OTHER_EXPENSES]
AS TOTAL_PROJECT_COST, Format([IMP_BOR_SBA_PORTION_AMOUNT]*0.005,'$#,##0')
AS RESERVE, Format([IMP_BOR_SBA_PORTION_AMOUNT]*0.0025,'$#,##0') AS
FUNDING_FEE, Format([IMP_BOR_SBA_PORTION_AMOUNT]*0.015,'$#,##0') AS CDC_FEE,
Format([1244A_CLOSING_COST],'$#,##0') AS CLOSING_COST,
Format(([IMP_BOR_SBA_PORTION_AMOUNT]*0.005)+([IMP_BOR_SBA_PORTION_AMOUNT]*0.0025)+([IMP_BOR_SBA_PORTION_AMOUNT]*0.015)+[1244A_CLOSING_COST],'$#,##0')
AS TOTAL_FEES, " & _
"Format(CEILING(([IMP_BOR_SBA_PORTION_AMOUNT]+[TOTAL_FEES])/0.996,1000)*0.004,'$#,##0')
AS UNDERWRITER_FEE,
Format(([IMP_BOR_SBA_PORTION_AMOUNT]*0.005)+([IMP_BOR_SBA_PORTION_AMOUNT]*0.0025)+([IMP_BOR_SBA_PORTION_AMOUNT]*0.015)+[1244A_CLOSING_COST]+(CEILING(([IMP_BOR_SBA_PORTION_AMOUNT]+[TOTAL_FEES])/0.996,1000)*0.004),'$#,##0')
AS TOTAL_FEE2,
Format(CEILING([IMP_BOR_SBA_PORTION_AMOUNT]+[TOTAL_FEE2],1000),'Currency')
AS TOTAL_DEBENTURE, Format([IMP_BOR_SBA_PORTION_AMOUNT],'$#,##0') AS
SBA_AMOUNT, Format([IMP_BOR_INJECTION],'$#,##0') AS BOR_INJECTION,
Format([IMP_BOR_PARTICIPATING_AMOUNT],'$#,##0') AS PARTICIPATING_AMOUNT,
Format(CEILING([IMP_BOR_SBA_PORTION_AMOUNT]+[TOTAL_FEE2],1000)-([IMP_BOR_SBA_PORTION_AMOUNT])+([IMP_BOR_SBA_PORTION_AMOUNT]*0.005)+([IMP_BOR_SBA_PORTION_AMOUNT]*0.0025)+([IMP_BOR_SBA_PORTION_AMOUNT]*0.015)+[1244A_CLOSING_COST]+(CEILING(([IMP_BOR_SBA_PORTION_AMOUNT]+[TOTAL_FEES])/0.996,1000)*0.004),'$#,##0')
AS BALANCE_TO_BORROWER, " & _
"[PURPOSE ECONOMIC].PUR_DESCRIPTION,
Format([IMP_BOR_SBA_PORTION_AMOUNT]/([IMP_BOR_SBA_PORTION_AMOUNT]+[IMP_BOR_INJECTION]+[IMP_BOR_PARTICIPATING_AMOUNT]),'#%')
AS SBA_PERCENT,
Format([IMP_BOR_INJECTION]/([IMP_BOR_SBA_PORTION_AMOUNT]+[IMP_BOR_INJECTION]+[IMP_BOR_PARTICIPATING_AMOUNT]),'#%')
AS INJECTION_PERCENT,
Format([IMP_BOR_PARTICIPATING_AMOUNT]/([IMP_BOR_SBA_PORTION_AMOUNT]+[IMP_BOR_INJECTION]+[IMP_BOR_PARTICIPATING_AMOUNT]),'#%')
AS PARTICIPATING_PERCENT,
Format([IMP_BOR_SBA_PORTION_AMOUNT]+[IMP_BOR_INJECTION]+[IMP_BOR_PARTICIPATING_AMOUNT],'$#,##0')
AS TOTAL_AMOUNT, Format([IMP_BOR_PARTICIPATING_RATE]/100,'#.##%') AS
PARTICIPATING_RATE, IIf([IMP_BOR_SBA_AMORTIZATION]=10,'2nd Lien','2nd RE')
AS LIEN_SBA, IIf([IMP_BOR_SBA_AMORTIZATION]=10,'1st Lien','1st RE') AS
LIEN_PARTICIPATING FROM [PURPOSE ECONOMIC] RIGHT JOIN ((((([1244 A] RIGHT
JOIN IMPORT ON [1244 A].[1244A_IMP_ID] = IMPORT.IMP_ID) " & _
"LEFT JOIN EMPLOYEES AS LOAN_OFFICER ON
IMPORT.IMP_LOAN_OFFICER_ID = LOAN_OFFICER.EMPID) LEFT JOIN EMPLOYEES AS
UNDERWRITER ON IMPORT.IMP_UNDERWRITER_ID = UNDERWRITER.EMPID) LEFT JOIN
MINORITY ON IMPORT.IMP_BOR_MIN_ID = MINORITY.MIN_ID) LEFT JOIN YES_NO ON
IMPORT.IMP_BOR_MTY_ID = YES_NO.YES_SHORT_DESCRIPTION) ON [PURPOSE
ECONOMIC].PUR_ID = IMPORT.IMP_BOR_IMPACT " & _
"WHERE [1244A].[1244A_IMP_ID]=" & myID

'myDB.Execute (myQRY1244_Step1)



DoCmd.OpenQuery "qry_1244_Step2"
DoCmd.OpenQuery "qry_TrackingSheet"
DoCmd.OpenQuery "qry_TEMP_OWNER_GUARANTOR"

DoCmd.OpenQuery "qry_1244B_Step1"
DoCmd.OpenQuery "qry_1244_4_Step2"
DoCmd.OpenQuery "qry_Submission_Step2"
DoCmd.OpenQuery "qry_CheckLists_Step2"
DoCmd.OpenQuery "qry_Environmental_Step2"

DoCmd.OpenQuery "qry_Supplement_Step2"

DoCmd.SetWarnings True
End If

If IsLoaded("frm_setup") Then
Else
DoCmd.OpenForm "frm_setup", acNormal, , , , acHidden
End If

ReportPath = Forms![frm_Setup]![SET_CURRENT_DATABASE]

Set oApp = CreateObject("Word.Application")

'PRINT REPORTS
'EXHIBIT 1
If Me.ckbExhibit1 = -1 Then
Call updateTEMP_OWNERS

oApp.Documents.Open FileName:="" & Chr(34) & ReportPath &
"\Exhibit 1.doc" & Chr(34)
If myView = 1 Then
oApp.PrintOut
Else
oApp.Visible = True
End If
End If

I have more but the errors come before this so I did not add as it is just
more of the same from the lines above.

TIA,
Rodger
 
D

Douglas J. Steele

If that code is copied-and-pasted, the DoCmd.SetWarnings False statement is
commented out.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Rodger said:
Hello All,

I have a form that I use to print all my reports. The reports are
actually in Word and use different queries as the datasource. The queries
create temp table. When the code runs it gives me a warning about
deleting the table even though I have Warnings set to False. Here is the
code that I have.

myView is used to either open in Word (1) or Print to the Printer (2)

Private Sub myReports(myView)

On Error GoTo Err_myReports

Dim ReportPath
Dim oApp As Object, DocName, x, myDB
Dim dbsCurrent As Database, dbsPath As String
Dim myMessage, myStyle, myTitle, myResponse
Dim myQRY7, myQRYRealEstateAppraisal, myQRY1244_Step1, myQRY1244_Step2,
myQRY1244B_Step1
Dim myQRYTrackingSheet, myQRY_TEMP_OWNER_GUARANTOR
Dim myID

Set myDB = CurrentDb()

myID = [Forms]![frm_Reports]![cmbLoan]


If IsNull(Me.cmbLoan) Then
myMessage = "Please select a loan"
myTitle = "No Selection"
myStyle = vbCritical + vbOKOnly
myResponse = MsgBox(myMessage, myStyle, myTitle)
Exit Sub
Else
'DoCmd.SetWarnings False
Call updateTEMP_OWNERS

If DoesObjectExist("Tables", "tmp_Exhibit7") = 0 Then
Else
DoCmd.DeleteObject acTable, "tmp_Exhibit7"
End If

If DoesObjectExist("Tables", "tmp_RealEstateAppraisal") = 0
Then
Else
DoCmd.DeleteObject acTable, "tmp_RealEstateAppraisal"
End If

If DoesObjectExist("Tables", "tmp_Submission") = 0 Then
Else
DoCmd.DeleteObject acTable, "tmp_Submission"
End If

If DoesObjectExist("Tables", "tmp_CheckLists") = 0 Then
Else
DoCmd.DeleteObject acTable, "tmp_CheckLists"
End If

If DoesObjectExist("Tables", "tmp_Environmental") = 0 Then
Else
DoCmd.DeleteObject acTable, "tmp_Environmental"
End If


'qry_Exhibit7
myQRY7 = "SELECT [EXHIBIT 7].EX7_ID, [EXHIBIT 7].EX7_IMP_ID,
[EXHIBIT 7].EX7_AR_UNDER_30, [EXHIBIT 7].EX7_AR_30_59, [EXHIBIT
7].EX7_AR_60_89, [EXHIBIT 7].EX7_AR_OVER_90, [EXHIBIT 7].EX7_AP_UNDER_30,
[EXHIBIT 7].EX7_AP_30_59, [EXHIBIT 7].EX7_AP_60_89, [EXHIBIT
7].EX7_AP_OVER_90, [EXHIBIT 7].EX7_AR_CONCENTRATION, [EXHIBIT
7].EX7_AP_CONCENTRATION,
[EX7_AR_UNDER_30]+[EX7_AR_30_59]+[EX7_AR_60_89]+[EX7_AR_OVER_90] AS
AR_TOTAL, [EX7_AP_UNDER_30]+[EX7_AP_30_59]+[EX7_AP_60_89]+[EX7_AP_OVER_90]
AS AP_TOTAL, Format([AR_TOTAL]/[EX7_AR_OVER_90],'#%') AS AR_PERCENT,
Format([AP_TOTAL]/[EX7_AP_OVER_90],'#%') AS AP_PERCENT INTO tmp_Exhibit7 "
& _
"FROM [EXHIBIT 7] " & _
"WHERE [EXHIBIT 7].EX7_IMP_ID = " & myID
myDB.Execute (myQRY7)

'qry_RealEstateAppraisal
myQRYRealEstateAppraisal = "SELECT IMPORT.IMP_ID,
IMPORT.IMP_BOR_NAME, IMPORT.IMP_BOR_PROJECT_ADDRESS,
IMPORT.IMP_BOR_PROJECT_ADDRESS2, IMPORT.IMP_BOR_PROJECT_CITY,
IMPORT.IMP_BOR_PROJECT_STATE, IMPORT.IMP_BOR_PROJECT_ZIP,
IMPORT.IMP_BOR_PRINCIPAL, IMPORT.IMP_BOR_SBC, IMPORT.IMP_BOR_DBA,
IMPORT.IMP_BOR_EMAIL, SETUP.SET_COMPANY_NAME, SETUP.SET_COMPANY_OWNER,
SETUP.SET_ADDRESS, SETUP.SET_CITY, SETUP.SET_STATE, SETUP.SET_ZIP,
SETUP.SET_PHONE, SETUP.SET_FAX, SETUP.SET_EMAIL1 INTO
tmp_RealEstateAppraisal " & _
"FROM IMPORT, SETUP " & _
"WHERE IMPORT.IMP_ID= " & myID
myDB.Execute (myQRYRealEstateAppraisal)

'qry_myQRY1244_Step1
myQRY1244_Step1 = "SELECT LOAN_OFFICER.LASTNAME & ', ' &
LOAN_OFFICER.FIRSTNAME AS LOAN_OFFICER_NAME, UNDERWRITER.LASTNAME & ', ' &
UNDERWRITER.FIRSTNAME AS UNDERWRITER_NAME, IIf([IMP_BOR_EPC]=-1,'YES X
NO','YES NO X') AS EPC, [1244 A].[1244A_OMB_APPROVAL_NUMBER], [1244
A].[1244A_EXPIRATION_DATE], [1244 A].[1244A_Q1] AS Q1, [1244 A].[1244A_Q2]
AS Q2, [1244 A].[1244A_Q3] AS Q3, [1244 A].[1244A_Q4] AS Q4, [1244
A].[1244A_Q5] AS Q5, [1244 A].[1244A_Q6] AS Q6, [1244 A].[1244A_Q7] AS Q7,
[1244 A].[1244A_Q8] AS Q8, [1244 A].[1244A_Q9] AS Q9, [1244 A].[1244A_Q10]
AS Q10, [1244 A].[1244A_Q11] AS Q11, [1244 A].[1244A_Q12] AS Q12, [1244
A].[1244A_Q13] AS Q13, [1244 A].[1244A_Q14] AS Q14, [1244 A].[1244A_Q15]
AS Q15, [1244 A].[1244A_Q16] AS Q16, [1244 A].[1244A_Q17] AS Q17, [1244
A].[1244A_Q18] AS Q18, [1244 A].[1244A_Q19] AS Q19, [1244 A].[1244A_Q20]
AS Q20, [1244 A].[1244A_Q21] AS Q21, [1244 A].[1244A_Q22] AS Q22, [1244
A].[1244A_Q23] AS Q23, [1244 A].[1244A_CLOSING_COST], " & _
"MINORITY.MIN_CODE, MINORITY.MIN_DESCRIPTION,
YES_NO.YES_DESCRIPTION, IIf([IMP_BOR_RELOCATION]=-1,'YES X NO','YES
NO X') AS REL, IIf([IMP_BOR_EXISTING_BUSINESS]=-1,'X Existing ' &
Chr(13) & Chr(10) & ' New Business',' Existing ' & Chr(13) & Chr(10)
& 'X New Business') AS EXISTING, IMPORT.*,
[IMP_BOR_JOBS_TWO_YEARS]+[IMP_BOR_JOBS_RETAINED] AS JOB_TOTAL,
[IMP_BOR_PURCHASE_LAND_BUILDING]+[IMP_BOR_CONSTRUCTION_REMODELING]+[IMP_BOR_PURCHASE_EQUIPMENT]+[IMP_BOR_PROFESSIOAL_FEES]+[IMP_BOR_OTHER_EXPENSES]
AS TOTAL_PROJECT_COST, Format([IMP_BOR_SBA_PORTION_AMOUNT]*0.005,'$#,##0')
AS RESERVE, Format([IMP_BOR_SBA_PORTION_AMOUNT]*0.0025,'$#,##0') AS
FUNDING_FEE, Format([IMP_BOR_SBA_PORTION_AMOUNT]*0.015,'$#,##0') AS
CDC_FEE, Format([1244A_CLOSING_COST],'$#,##0') AS CLOSING_COST,
Format(([IMP_BOR_SBA_PORTION_AMOUNT]*0.005)+([IMP_BOR_SBA_PORTION_AMOUNT]*0.0025)+([IMP_BOR_SBA_PORTION_AMOUNT]*0.015)+[1244A_CLOSING_COST],'$#,##0')
AS TOTAL_FEES, " & _

"Format(CEILING(([IMP_BOR_SBA_PORTION_AMOUNT]+[TOTAL_FEES])/0.996,1000)*0.004,'$#,##0')
AS UNDERWRITER_FEE,
Format(([IMP_BOR_SBA_PORTION_AMOUNT]*0.005)+([IMP_BOR_SBA_PORTION_AMOUNT]*0.0025)+([IMP_BOR_SBA_PORTION_AMOUNT]*0.015)+[1244A_CLOSING_COST]+(CEILING(([IMP_BOR_SBA_PORTION_AMOUNT]+[TOTAL_FEES])/0.996,1000)*0.004),'$#,##0')
AS TOTAL_FEE2,
Format(CEILING([IMP_BOR_SBA_PORTION_AMOUNT]+[TOTAL_FEE2],1000),'Currency')
AS TOTAL_DEBENTURE, Format([IMP_BOR_SBA_PORTION_AMOUNT],'$#,##0') AS
SBA_AMOUNT, Format([IMP_BOR_INJECTION],'$#,##0') AS BOR_INJECTION,
Format([IMP_BOR_PARTICIPATING_AMOUNT],'$#,##0') AS PARTICIPATING_AMOUNT,
Format(CEILING([IMP_BOR_SBA_PORTION_AMOUNT]+[TOTAL_FEE2],1000)-([IMP_BOR_SBA_PORTION_AMOUNT])+([IMP_BOR_SBA_PORTION_AMOUNT]*0.005)+([IMP_BOR_SBA_PORTION_AMOUNT]*0.0025)+([IMP_BOR_SBA_PORTION_AMOUNT]*0.015)+[1244A_CLOSING_COST]+(CEILING(([IMP_BOR_SBA_PORTION_AMOUNT]+[TOTAL_FEES])/0.996,1000)*0.004),'$#,##0')
AS BALANCE_TO_BORROWER, " & _
"[PURPOSE ECONOMIC].PUR_DESCRIPTION,
Format([IMP_BOR_SBA_PORTION_AMOUNT]/([IMP_BOR_SBA_PORTION_AMOUNT]+[IMP_BOR_INJECTION]+[IMP_BOR_PARTICIPATING_AMOUNT]),'#%')
AS SBA_PERCENT,
Format([IMP_BOR_INJECTION]/([IMP_BOR_SBA_PORTION_AMOUNT]+[IMP_BOR_INJECTION]+[IMP_BOR_PARTICIPATING_AMOUNT]),'#%')
AS INJECTION_PERCENT,
Format([IMP_BOR_PARTICIPATING_AMOUNT]/([IMP_BOR_SBA_PORTION_AMOUNT]+[IMP_BOR_INJECTION]+[IMP_BOR_PARTICIPATING_AMOUNT]),'#%')
AS PARTICIPATING_PERCENT,
Format([IMP_BOR_SBA_PORTION_AMOUNT]+[IMP_BOR_INJECTION]+[IMP_BOR_PARTICIPATING_AMOUNT],'$#,##0')
AS TOTAL_AMOUNT, Format([IMP_BOR_PARTICIPATING_RATE]/100,'#.##%') AS
PARTICIPATING_RATE, IIf([IMP_BOR_SBA_AMORTIZATION]=10,'2nd Lien','2nd RE')
AS LIEN_SBA, IIf([IMP_BOR_SBA_AMORTIZATION]=10,'1st Lien','1st RE') AS
LIEN_PARTICIPATING FROM [PURPOSE ECONOMIC] RIGHT JOIN ((((([1244 A] RIGHT
JOIN IMPORT ON [1244 A].[1244A_IMP_ID] = IMPORT.IMP_ID) " & _
"LEFT JOIN EMPLOYEES AS LOAN_OFFICER ON
IMPORT.IMP_LOAN_OFFICER_ID = LOAN_OFFICER.EMPID) LEFT JOIN EMPLOYEES AS
UNDERWRITER ON IMPORT.IMP_UNDERWRITER_ID = UNDERWRITER.EMPID) LEFT JOIN
MINORITY ON IMPORT.IMP_BOR_MIN_ID = MINORITY.MIN_ID) LEFT JOIN YES_NO ON
IMPORT.IMP_BOR_MTY_ID = YES_NO.YES_SHORT_DESCRIPTION) ON [PURPOSE
ECONOMIC].PUR_ID = IMPORT.IMP_BOR_IMPACT " & _
"WHERE [1244A].[1244A_IMP_ID]=" & myID

'myDB.Execute (myQRY1244_Step1)



DoCmd.OpenQuery "qry_1244_Step2"
DoCmd.OpenQuery "qry_TrackingSheet"
DoCmd.OpenQuery "qry_TEMP_OWNER_GUARANTOR"

DoCmd.OpenQuery "qry_1244B_Step1"
DoCmd.OpenQuery "qry_1244_4_Step2"
DoCmd.OpenQuery "qry_Submission_Step2"
DoCmd.OpenQuery "qry_CheckLists_Step2"
DoCmd.OpenQuery "qry_Environmental_Step2"

DoCmd.OpenQuery "qry_Supplement_Step2"

DoCmd.SetWarnings True
End If

If IsLoaded("frm_setup") Then
Else
DoCmd.OpenForm "frm_setup", acNormal, , , , acHidden
End If

ReportPath = Forms![frm_Setup]![SET_CURRENT_DATABASE]

Set oApp = CreateObject("Word.Application")

'PRINT REPORTS
'EXHIBIT 1
If Me.ckbExhibit1 = -1 Then
Call updateTEMP_OWNERS

oApp.Documents.Open FileName:="" & Chr(34) & ReportPath &
"\Exhibit 1.doc" & Chr(34)
If myView = 1 Then
oApp.PrintOut
Else
oApp.Visible = True
End If
End If

I have more but the errors come before this so I did not add as it is just
more of the same from the lines above.

TIA,
Rodger
Click to expand...
 
R

Rodger

Sorry, I was testing and I guess I forgot to take it out. I just removed
and tried again. Still having the issue.


Douglas J. Steele said:
If that code is copied-and-pasted, the DoCmd.SetWarnings False statement
is commented out.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Rodger said:
Hello All,

I have a form that I use to print all my reports. The reports are
actually in Word and use different queries as the datasource. The
queries create temp table. When the code runs it gives me a warning
about deleting the table even though I have Warnings set to False. Here
is the code that I have.

myView is used to either open in Word (1) or Print to the Printer (2)

Private Sub myReports(myView)

On Error GoTo Err_myReports

Dim ReportPath
Dim oApp As Object, DocName, x, myDB
Dim dbsCurrent As Database, dbsPath As String
Dim myMessage, myStyle, myTitle, myResponse
Dim myQRY7, myQRYRealEstateAppraisal, myQRY1244_Step1, myQRY1244_Step2,
myQRY1244B_Step1
Dim myQRYTrackingSheet, myQRY_TEMP_OWNER_GUARANTOR
Dim myID

Set myDB = CurrentDb()

myID = [Forms]![frm_Reports]![cmbLoan]


If IsNull(Me.cmbLoan) Then
myMessage = "Please select a loan"
myTitle = "No Selection"
myStyle = vbCritical + vbOKOnly
myResponse = MsgBox(myMessage, myStyle, myTitle)
Exit Sub
Else
'DoCmd.SetWarnings False
Call updateTEMP_OWNERS

If DoesObjectExist("Tables", "tmp_Exhibit7") = 0 Then
Else
DoCmd.DeleteObject acTable, "tmp_Exhibit7"
End If

If DoesObjectExist("Tables", "tmp_RealEstateAppraisal") = 0
Then
Else
DoCmd.DeleteObject acTable, "tmp_RealEstateAppraisal"
End If

If DoesObjectExist("Tables", "tmp_Submission") = 0 Then
Else
DoCmd.DeleteObject acTable, "tmp_Submission"
End If

If DoesObjectExist("Tables", "tmp_CheckLists") = 0 Then
Else
DoCmd.DeleteObject acTable, "tmp_CheckLists"
End If

If DoesObjectExist("Tables", "tmp_Environmental") = 0 Then
Else
DoCmd.DeleteObject acTable, "tmp_Environmental"
End If


'qry_Exhibit7
myQRY7 = "SELECT [EXHIBIT 7].EX7_ID, [EXHIBIT 7].EX7_IMP_ID,
[EXHIBIT 7].EX7_AR_UNDER_30, [EXHIBIT 7].EX7_AR_30_59, [EXHIBIT
7].EX7_AR_60_89, [EXHIBIT 7].EX7_AR_OVER_90, [EXHIBIT 7].EX7_AP_UNDER_30,
[EXHIBIT 7].EX7_AP_30_59, [EXHIBIT 7].EX7_AP_60_89, [EXHIBIT
7].EX7_AP_OVER_90, [EXHIBIT 7].EX7_AR_CONCENTRATION, [EXHIBIT
7].EX7_AP_CONCENTRATION,
[EX7_AR_UNDER_30]+[EX7_AR_30_59]+[EX7_AR_60_89]+[EX7_AR_OVER_90] AS
AR_TOTAL,
[EX7_AP_UNDER_30]+[EX7_AP_30_59]+[EX7_AP_60_89]+[EX7_AP_OVER_90] AS
AP_TOTAL, Format([AR_TOTAL]/[EX7_AR_OVER_90],'#%') AS AR_PERCENT,
Format([AP_TOTAL]/[EX7_AP_OVER_90],'#%') AS AP_PERCENT INTO tmp_Exhibit7
" & _
"FROM [EXHIBIT 7] " & _
"WHERE [EXHIBIT 7].EX7_IMP_ID = " & myID
myDB.Execute (myQRY7)

'qry_RealEstateAppraisal
myQRYRealEstateAppraisal = "SELECT IMPORT.IMP_ID,
IMPORT.IMP_BOR_NAME, IMPORT.IMP_BOR_PROJECT_ADDRESS,
IMPORT.IMP_BOR_PROJECT_ADDRESS2, IMPORT.IMP_BOR_PROJECT_CITY,
IMPORT.IMP_BOR_PROJECT_STATE, IMPORT.IMP_BOR_PROJECT_ZIP,
IMPORT.IMP_BOR_PRINCIPAL, IMPORT.IMP_BOR_SBC, IMPORT.IMP_BOR_DBA,
IMPORT.IMP_BOR_EMAIL, SETUP.SET_COMPANY_NAME, SETUP.SET_COMPANY_OWNER,
SETUP.SET_ADDRESS, SETUP.SET_CITY, SETUP.SET_STATE, SETUP.SET_ZIP,
SETUP.SET_PHONE, SETUP.SET_FAX, SETUP.SET_EMAIL1 INTO
tmp_RealEstateAppraisal " & _
"FROM IMPORT, SETUP " & _
"WHERE IMPORT.IMP_ID= " & myID
myDB.Execute (myQRYRealEstateAppraisal)

'qry_myQRY1244_Step1
myQRY1244_Step1 = "SELECT LOAN_OFFICER.LASTNAME & ', ' &
LOAN_OFFICER.FIRSTNAME AS LOAN_OFFICER_NAME, UNDERWRITER.LASTNAME & ', '
& UNDERWRITER.FIRSTNAME AS UNDERWRITER_NAME, IIf([IMP_BOR_EPC]=-1,'YES X
NO','YES NO X') AS EPC, [1244 A].[1244A_OMB_APPROVAL_NUMBER], [1244
A].[1244A_EXPIRATION_DATE], [1244 A].[1244A_Q1] AS Q1, [1244
A].[1244A_Q2] AS Q2, [1244 A].[1244A_Q3] AS Q3, [1244 A].[1244A_Q4] AS
Q4, [1244 A].[1244A_Q5] AS Q5, [1244 A].[1244A_Q6] AS Q6, [1244
A].[1244A_Q7] AS Q7, [1244 A].[1244A_Q8] AS Q8, [1244 A].[1244A_Q9] AS
Q9, [1244 A].[1244A_Q10] AS Q10, [1244 A].[1244A_Q11] AS Q11, [1244
A].[1244A_Q12] AS Q12, [1244 A].[1244A_Q13] AS Q13, [1244 A].[1244A_Q14]
AS Q14, [1244 A].[1244A_Q15] AS Q15, [1244 A].[1244A_Q16] AS Q16, [1244
A].[1244A_Q17] AS Q17, [1244 A].[1244A_Q18] AS Q18, [1244 A].[1244A_Q19]
AS Q19, [1244 A].[1244A_Q20] AS Q20, [1244 A].[1244A_Q21] AS Q21, [1244
A].[1244A_Q22] AS Q22, [1244 A].[1244A_Q23] AS Q23, [1244
A].[1244A_CLOSING_COST], " & _
"MINORITY.MIN_CODE,
MINORITY.MIN_DESCRIPTION, YES_NO.YES_DESCRIPTION,
IIf([IMP_BOR_RELOCATION]=-1,'YES X NO','YES NO X') AS REL,
IIf([IMP_BOR_EXISTING_BUSINESS]=-1,'X Existing ' & Chr(13) & Chr(10) & '
New Business',' Existing ' & Chr(13) & Chr(10) & 'X New Business') AS
EXISTING, IMPORT.*, [IMP_BOR_JOBS_TWO_YEARS]+[IMP_BOR_JOBS_RETAINED] AS
JOB_TOTAL,
[IMP_BOR_PURCHASE_LAND_BUILDING]+[IMP_BOR_CONSTRUCTION_REMODELING]+[IMP_BOR_PURCHASE_EQUIPMENT]+[IMP_BOR_PROFESSIOAL_FEES]+[IMP_BOR_OTHER_EXPENSES]
AS TOTAL_PROJECT_COST,
Format([IMP_BOR_SBA_PORTION_AMOUNT]*0.005,'$#,##0') AS RESERVE,
Format([IMP_BOR_SBA_PORTION_AMOUNT]*0.0025,'$#,##0') AS FUNDING_FEE,
Format([IMP_BOR_SBA_PORTION_AMOUNT]*0.015,'$#,##0') AS CDC_FEE,
Format([1244A_CLOSING_COST],'$#,##0') AS CLOSING_COST,
Format(([IMP_BOR_SBA_PORTION_AMOUNT]*0.005)+([IMP_BOR_SBA_PORTION_AMOUNT]*0.0025)+([IMP_BOR_SBA_PORTION_AMOUNT]*0.015)+[1244A_CLOSING_COST],'$#,##0')
AS TOTAL_FEES, " & _

"Format(CEILING(([IMP_BOR_SBA_PORTION_AMOUNT]+[TOTAL_FEES])/0.996,1000)*0.004,'$#,##0')
AS UNDERWRITER_FEE,
Format(([IMP_BOR_SBA_PORTION_AMOUNT]*0.005)+([IMP_BOR_SBA_PORTION_AMOUNT]*0.0025)+([IMP_BOR_SBA_PORTION_AMOUNT]*0.015)+[1244A_CLOSING_COST]+(CEILING(([IMP_BOR_SBA_PORTION_AMOUNT]+[TOTAL_FEES])/0.996,1000)*0.004),'$#,##0')
AS TOTAL_FEE2,
Format(CEILING([IMP_BOR_SBA_PORTION_AMOUNT]+[TOTAL_FEE2],1000),'Currency')
AS TOTAL_DEBENTURE, Format([IMP_BOR_SBA_PORTION_AMOUNT],'$#,##0') AS
SBA_AMOUNT, Format([IMP_BOR_INJECTION],'$#,##0') AS BOR_INJECTION,
Format([IMP_BOR_PARTICIPATING_AMOUNT],'$#,##0') AS PARTICIPATING_AMOUNT,
Format(CEILING([IMP_BOR_SBA_PORTION_AMOUNT]+[TOTAL_FEE2],1000)-([IMP_BOR_SBA_PORTION_AMOUNT])+([IMP_BOR_SBA_PORTION_AMOUNT]*0.005)+([IMP_BOR_SBA_PORTION_AMOUNT]*0.0025)+([IMP_BOR_SBA_PORTION_AMOUNT]*0.015)+[1244A_CLOSING_COST]+(CEILING(([IMP_BOR_SBA_PORTION_AMOUNT]+[TOTAL_FEES])/0.996,1000)*0.004),'$#,##0')
AS BALANCE_TO_BORROWER, " & _
"[PURPOSE ECONOMIC].PUR_DESCRIPTION,
Format([IMP_BOR_SBA_PORTION_AMOUNT]/([IMP_BOR_SBA_PORTION_AMOUNT]+[IMP_BOR_INJECTION]+[IMP_BOR_PARTICIPATING_AMOUNT]),'#%')
AS SBA_PERCENT,
Format([IMP_BOR_INJECTION]/([IMP_BOR_SBA_PORTION_AMOUNT]+[IMP_BOR_INJECTION]+[IMP_BOR_PARTICIPATING_AMOUNT]),'#%')
AS INJECTION_PERCENT,
Format([IMP_BOR_PARTICIPATING_AMOUNT]/([IMP_BOR_SBA_PORTION_AMOUNT]+[IMP_BOR_INJECTION]+[IMP_BOR_PARTICIPATING_AMOUNT]),'#%')
AS PARTICIPATING_PERCENT,
Format([IMP_BOR_SBA_PORTION_AMOUNT]+[IMP_BOR_INJECTION]+[IMP_BOR_PARTICIPATING_AMOUNT],'$#,##0')
AS TOTAL_AMOUNT, Format([IMP_BOR_PARTICIPATING_RATE]/100,'#.##%') AS
PARTICIPATING_RATE, IIf([IMP_BOR_SBA_AMORTIZATION]=10,'2nd Lien','2nd
RE') AS LIEN_SBA, IIf([IMP_BOR_SBA_AMORTIZATION]=10,'1st Lien','1st RE')
AS LIEN_PARTICIPATING FROM [PURPOSE ECONOMIC] RIGHT JOIN ((((([1244 A]
RIGHT JOIN IMPORT ON [1244 A].[1244A_IMP_ID] = IMPORT.IMP_ID) " & _
"LEFT JOIN EMPLOYEES AS LOAN_OFFICER ON
IMPORT.IMP_LOAN_OFFICER_ID = LOAN_OFFICER.EMPID) LEFT JOIN EMPLOYEES AS
UNDERWRITER ON IMPORT.IMP_UNDERWRITER_ID = UNDERWRITER.EMPID) LEFT JOIN
MINORITY ON IMPORT.IMP_BOR_MIN_ID = MINORITY.MIN_ID) LEFT JOIN YES_NO ON
IMPORT.IMP_BOR_MTY_ID = YES_NO.YES_SHORT_DESCRIPTION) ON [PURPOSE
ECONOMIC].PUR_ID = IMPORT.IMP_BOR_IMPACT " & _
"WHERE [1244A].[1244A_IMP_ID]=" & myID

'myDB.Execute (myQRY1244_Step1)



DoCmd.OpenQuery "qry_1244_Step2"
DoCmd.OpenQuery "qry_TrackingSheet"
DoCmd.OpenQuery "qry_TEMP_OWNER_GUARANTOR"

DoCmd.OpenQuery "qry_1244B_Step1"
DoCmd.OpenQuery "qry_1244_4_Step2"
DoCmd.OpenQuery "qry_Submission_Step2"
DoCmd.OpenQuery "qry_CheckLists_Step2"
DoCmd.OpenQuery "qry_Environmental_Step2"

DoCmd.OpenQuery "qry_Supplement_Step2"

DoCmd.SetWarnings True
End If

If IsLoaded("frm_setup") Then
Else
DoCmd.OpenForm "frm_setup", acNormal, , , , acHidden
End If

ReportPath = Forms![frm_Setup]![SET_CURRENT_DATABASE]

Set oApp = CreateObject("Word.Application")

'PRINT REPORTS
'EXHIBIT 1
If Me.ckbExhibit1 = -1 Then
Call updateTEMP_OWNERS

oApp.Documents.Open FileName:="" & Chr(34) & ReportPath &
"\Exhibit 1.doc" & Chr(34)
If myView = 1 Then
oApp.PrintOut
Else
oApp.Visible = True
End If
End If

I have more but the errors come before this so I did not add as it is
just more of the same from the lines above.

TIA,
Rodger
Click to expand...
Click to expand...
 
J

James

Hi

You could set a breakpoint fairly early on in your code and then step
through to see exactly which query/part of code is causing the prompt.

Could it be that you turn the warnings on and then go onto to "Call
updateTEMP_OWNERS ". Does this function update any tables?

James

Sorry, I was testing and I guess I forgot to take it out. I just removed
and tried again. Still having the issue.



If that code is copied-and-pasted, the DoCmd.SetWarnings False statement
is commented out.
Click to expand...
Rodger said:
Hello All,
I have a form that I use to print all my reports. The reports are
actually in Word and use different queries as the datasource. The
queries create temp table. When the code runs it gives me a warning
about deleting the table even though I have Warnings set to False. Here
is the code that I have.
myView is used to either open in Word (1) or Print to the Printer (2)
Private Sub myReports(myView)
On Error GoTo Err_myReports
Dim ReportPath
Dim oApp As Object, DocName, x, myDB
Dim dbsCurrent As Database, dbsPath As String
Dim myMessage, myStyle, myTitle, myResponse
Dim myQRY7, myQRYRealEstateAppraisal, myQRY1244_Step1, myQRY1244_Step2,
myQRY1244B_Step1
Dim myQRYTrackingSheet, myQRY_TEMP_OWNER_GUARANTOR
Dim myID
Set myDB = CurrentDb()
myID = [Forms]![frm_Reports]![cmbLoan]
If IsNull(Me.cmbLoan) Then
myMessage = "Please select a loan"
myTitle = "No Selection"
myStyle = vbCritical + vbOKOnly
myResponse = MsgBox(myMessage, myStyle, myTitle)
Exit Sub
Else
'DoCmd.SetWarnings False
Call updateTEMP_OWNERS
If DoesObjectExist("Tables", "tmp_Exhibit7") = 0 Then
Else
DoCmd.DeleteObject acTable, "tmp_Exhibit7"
End If
If DoesObjectExist("Tables", "tmp_RealEstateAppraisal") =0
Then
Else
DoCmd.DeleteObject acTable, "tmp_RealEstateAppraisal"
End If
If DoesObjectExist("Tables", "tmp_Submission") = 0 Then
Else
DoCmd.DeleteObject acTable, "tmp_Submission"
End If
If DoesObjectExist("Tables", "tmp_CheckLists") = 0 Then
Else
DoCmd.DeleteObject acTable, "tmp_CheckLists"
End If
If DoesObjectExist("Tables", "tmp_Environmental") = 0 Then
Else
DoCmd.DeleteObject acTable, "tmp_Environmental"
End If
'qry_Exhibit7
myQRY7 = "SELECT [EXHIBIT 7].EX7_ID, [EXHIBIT 7].EX7_IMP_ID,
[EXHIBIT 7].EX7_AR_UNDER_30, [EXHIBIT 7].EX7_AR_30_59, [EXHIBIT
7].EX7_AR_60_89, [EXHIBIT 7].EX7_AR_OVER_90, [EXHIBIT 7].EX7_AP_UNDER_30,
[EXHIBIT 7].EX7_AP_30_59, [EXHIBIT 7].EX7_AP_60_89, [EXHIBIT
7].EX7_AP_OVER_90, [EXHIBIT 7].EX7_AR_CONCENTRATION, [EXHIBIT
7].EX7_AP_CONCENTRATION,
[EX7_AR_UNDER_30]+[EX7_AR_30_59]+[EX7_AR_60_89]+[EX7_AR_OVER_90] AS
AR_TOTAL,
[EX7_AP_UNDER_30]+[EX7_AP_30_59]+[EX7_AP_60_89]+[EX7_AP_OVER_90] AS
AP_TOTAL, Format([AR_TOTAL]/[EX7_AR_OVER_90],'#%') AS AR_PERCENT,
Format([AP_TOTAL]/[EX7_AP_OVER_90],'#%') AS AP_PERCENT INTO tmp_Exhibit7
" & _
"FROM [EXHIBIT 7] " & _
"WHERE [EXHIBIT 7].EX7_IMP_ID = " & myID
myDB.Execute (myQRY7)
'qry_RealEstateAppraisal
myQRYRealEstateAppraisal = "SELECT IMPORT.IMP_ID,
IMPORT.IMP_BOR_NAME, IMPORT.IMP_BOR_PROJECT_ADDRESS,
IMPORT.IMP_BOR_PROJECT_ADDRESS2, IMPORT.IMP_BOR_PROJECT_CITY,
IMPORT.IMP_BOR_PROJECT_STATE, IMPORT.IMP_BOR_PROJECT_ZIP,
IMPORT.IMP_BOR_PRINCIPAL, IMPORT.IMP_BOR_SBC, IMPORT.IMP_BOR_DBA,
IMPORT.IMP_BOR_EMAIL, SETUP.SET_COMPANY_NAME, SETUP.SET_COMPANY_OWNER,
SETUP.SET_ADDRESS, SETUP.SET_CITY, SETUP.SET_STATE, SETUP.SET_ZIP,
SETUP.SET_PHONE, SETUP.SET_FAX, SETUP.SET_EMAIL1 INTO
tmp_RealEstateAppraisal " & _
"FROM IMPORT, SETUP " & _
"WHERE IMPORT.IMP_ID= " & myID
myDB.Execute (myQRYRealEstateAppraisal)
'qry_myQRY1244_Step1
myQRY1244_Step1 = "SELECT LOAN_OFFICER.LASTNAME & ', ' &
LOAN_OFFICER.FIRSTNAME AS LOAN_OFFICER_NAME, UNDERWRITER.LASTNAME & ','
& UNDERWRITER.FIRSTNAME AS UNDERWRITER_NAME, IIf([IMP_BOR_EPC]=-1,'YES X
NO','YES NO X') AS EPC, [1244 A].[1244A_OMB_APPROVAL_NUMBER], [1244
A].[1244A_EXPIRATION_DATE], [1244 A].[1244A_Q1] AS Q1, [1244
A].[1244A_Q2] AS Q2, [1244 A].[1244A_Q3] AS Q3, [1244 A].[1244A_Q4] AS
Q4, [1244 A].[1244A_Q5] AS Q5, [1244 A].[1244A_Q6] AS Q6, [1244
A].[1244A_Q7] AS Q7, [1244 A].[1244A_Q8] AS Q8, [1244 A].[1244A_Q9] AS
Q9, [1244 A].[1244A_Q10] AS Q10, [1244 A].[1244A_Q11] AS Q11, [1244
A].[1244A_Q12] AS Q12, [1244 A].[1244A_Q13] AS Q13, [1244 A].[1244A_Q14]
AS Q14, [1244 A].[1244A_Q15] AS Q15, [1244 A].[1244A_Q16] AS Q16, [1244
A].[1244A_Q17] AS Q17, [1244 A].[1244A_Q18] AS Q18, [1244 A].[1244A_Q19]
AS Q19, [1244 A].[1244A_Q20] AS Q20, [1244 A].[1244A_Q21] AS Q21, [1244
A].[1244A_Q22] AS Q22, [1244 A].[1244A_Q23] AS Q23, [1244
A].[1244A_CLOSING_COST], " & _
"MINORITY.MIN_CODE,
MINORITY.MIN_DESCRIPTION, YES_NO.YES_DESCRIPTION,
IIf([IMP_BOR_RELOCATION]=-1,'YES X NO','YES NO X') AS REL,
IIf([IMP_BOR_EXISTING_BUSINESS]=-1,'X Existing ' & Chr(13) & Chr(10) & '
New Business',' Existing ' & Chr(13) & Chr(10) & 'X New Business')AS
EXISTING, IMPORT.*, [IMP_BOR_JOBS_TWO_YEARS]+[IMP_BOR_JOBS_RETAINED] AS
JOB_TOTAL,
[IMP_BOR_PURCHASE_LAND_BUILDING]+[IMP_BOR_CONSTRUCTION_REMODELING]+[IMP_BOR­_PURCHASE_EQUIPMENT]+[IMP_BOR_PROFESSIOAL_FEES]+[IMP_BOR_OTHER_EXPENSES]
AS TOTAL_PROJECT_COST,
Format([IMP_BOR_SBA_PORTION_AMOUNT]*0.005,'$#,##0') AS RESERVE,
Format([IMP_BOR_SBA_PORTION_AMOUNT]*0.0025,'$#,##0') AS FUNDING_FEE,
Format([IMP_BOR_SBA_PORTION_AMOUNT]*0.015,'$#,##0') AS CDC_FEE,
Format([1244A_CLOSING_COST],'$#,##0') AS CLOSING_COST,
Format(([IMP_BOR_SBA_PORTION_AMOUNT]*0.005)+([IMP_BOR_SBA_PORTION_AMOUNT]*0­.0025)+([IMP_BOR_SBA_PORTION_AMOUNT]*0.015)+[1244A_CLOSING_COST],'$#,##0')
AS TOTAL_FEES, " & _
"Format(CEILING(([IMP_BOR_SBA_PORTION_AMOUNT]+[TOTAL_FEES])/0.996,1000)*0.0­04,'$#,##0')
AS UNDERWRITER_FEE,
Format(([IMP_BOR_SBA_PORTION_AMOUNT]*0.005)+([IMP_BOR_SBA_PORTION_AMOUNT]*0­.0025)+([IMP_BOR_SBA_PORTION_AMOUNT]*0.015)+[1244A_CLOSING_COST]+(CEILING((­[IMP_BOR_SBA_PORTION_AMOUNT]+[TOTAL_FEES])/0.996,1000)*0.004),'$#,##0')
AS TOTAL_FEE2,
Format(CEILING([IMP_BOR_SBA_PORTION_AMOUNT]+[TOTAL_FEE2],1000),'Currency')
AS TOTAL_DEBENTURE, Format([IMP_BOR_SBA_PORTION_AMOUNT],'$#,##0') AS
SBA_AMOUNT, Format([IMP_BOR_INJECTION],'$#,##0') AS BOR_INJECTION,
Format([IMP_BOR_PARTICIPATING_AMOUNT],'$#,##0') AS PARTICIPATING_AMOUNT,
Format(CEILING([IMP_BOR_SBA_PORTION_AMOUNT]+[TOTAL_FEE2],1000)-([IMP_BOR_SB­A_PORTION_AMOUNT])+([IMP_BOR_SBA_PORTION_AMOUNT]*0.005)+([IMP_BOR_SBA_PORTI­ON_AMOUNT]*0.0025)+([IMP_BOR_SBA_PORTION_AMOUNT]*0.015)+[1244A_CLOSING_COST­]+(CEILING(([IMP_BOR_SBA_PORTION_AMOUNT]+[TOTAL_FEES])/0.996,1000)*0.004),'­$#,##0')
AS BALANCE_TO_BORROWER, " & _
"[PURPOSE ECONOMIC].PUR_DESCRIPTION,
Format([IMP_BOR_SBA_PORTION_AMOUNT]/([IMP_BOR_SBA_PORTION_AMOUNT]+[IMP_BOR_­INJECTION]+[IMP_BOR_PARTICIPATING_AMOUNT]),'#%')
AS SBA_PERCENT,
Format([IMP_BOR_INJECTION]/([IMP_BOR_SBA_PORTION_AMOUNT]+[IMP_BOR_INJECTION­]+[IMP_BOR_PARTICIPATING_AMOUNT]),'#%')
AS INJECTION_PERCENT,
Format([IMP_BOR_PARTICIPATING_AMOUNT]/([IMP_BOR_SBA_PORTION_AMOUNT]+[IMP_BO­R_INJECTION]+[IMP_BOR_PARTICIPATING_AMOUNT]),'#%')
AS PARTICIPATING_PERCENT,
Format([IMP_BOR_SBA_PORTION_AMOUNT]+[IMP_BOR_INJECTION]+[IMP_BOR_PARTICIPAT­ING_AMOUNT],'$#,##0')
AS TOTAL_AMOUNT, Format([IMP_BOR_PARTICIPATING_RATE]/100,'#.##%') AS
PARTICIPATING_RATE, IIf([IMP_BOR_SBA_AMORTIZATION]=10,'2nd Lien','2nd
RE') AS LIEN_SBA, IIf([IMP_BOR_SBA_AMORTIZATION]=10,'1st Lien','1st RE')
AS LIEN_PARTICIPATING FROM [PURPOSE ECONOMIC] RIGHT JOIN ((((([1244 A]
RIGHT JOIN IMPORT ON [1244 A].[1244A_IMP_ID] = IMPORT.IMP_ID) " & _
"LEFT JOIN EMPLOYEES AS LOAN_OFFICER ON
IMPORT.IMP_LOAN_OFFICER_ID = LOAN_OFFICER.EMPID) LEFT JOIN EMPLOYEESAS
UNDERWRITER ON IMPORT.IMP_UNDERWRITER_ID = UNDERWRITER.EMPID) LEFT JOIN
MINORITY ON IMPORT.IMP_BOR_MIN_ID = MINORITY.MIN_ID) LEFT JOIN YES_NO ON
IMPORT.IMP_BOR_MTY_ID = YES_NO.YES_SHORT_DESCRIPTION) ON [PURPOSE
ECONOMIC].PUR_ID = IMPORT.IMP_BOR_IMPACT " & _
"WHERE [1244A].[1244A_IMP_ID]=" & myID
'myDB.Execute (myQRY1244_Step1)
DoCmd.OpenQuery "qry_1244_Step2"
DoCmd.OpenQuery "qry_TrackingSheet"
DoCmd.OpenQuery "qry_TEMP_OWNER_GUARANTOR"
DoCmd.OpenQuery "qry_1244B_Step1"
DoCmd.OpenQuery "qry_1244_4_Step2"
DoCmd.OpenQuery "qry_Submission_Step2"
DoCmd.OpenQuery "qry_CheckLists_Step2"
DoCmd.OpenQuery "qry_Environmental_Step2"
DoCmd.OpenQuery "qry_Supplement_Step2"
DoCmd.SetWarnings True
End If
If IsLoaded("frm_setup") Then
Else
DoCmd.OpenForm "frm_setup", acNormal, , , , acHidden
End If
ReportPath = Forms![frm_Setup]![SET_CURRENT_DATABASE]
Set oApp = CreateObject("Word.Application")
'PRINT REPORTS
'EXHIBIT 1
If Me.ckbExhibit1 = -1 Then
Call updateTEMP_OWNERS
oApp.Documents.Open FileName:="" & Chr(34) & ReportPath &
"\Exhibit
Click to expand...
Click to expand...

...

read more »- Hide quoted text -

- Show quoted text -
Click to expand...
 
R

Rodger

Yes, I have done this and every query that I am running gives the prompt.
The function that I am calling also has the SetWarnings Set to False.


Hi

You could set a breakpoint fairly early on in your code and then step
through to see exactly which query/part of code is causing the prompt.

Could it be that you turn the warnings on and then go onto to "Call
updateTEMP_OWNERS ". Does this function update any tables?

James

Sorry, I was testing and I guess I forgot to take it out. I just removed
and tried again. Still having the issue.

message

If that code is copied-and-pasted, the DoCmd.SetWarnings False statement
is commented out.
Click to expand...
Rodger said:
Hello All,
I have a form that I use to print all my reports. The reports are
actually in Word and use different queries as the datasource. The
queries create temp table. When the code runs it gives me a warning
about deleting the table even though I have Warnings set to False.
Here
is the code that I have.
myView is used to either open in Word (1) or Print to the Printer (2)
Private Sub myReports(myView)
On Error GoTo Err_myReports
Dim ReportPath
Dim oApp As Object, DocName, x, myDB
Dim dbsCurrent As Database, dbsPath As String
Dim myMessage, myStyle, myTitle, myResponse
Dim myQRY7, myQRYRealEstateAppraisal, myQRY1244_Step1, myQRY1244_Step2,
myQRY1244B_Step1
Dim myQRYTrackingSheet, myQRY_TEMP_OWNER_GUARANTOR
Dim myID
Set myDB = CurrentDb()
myID = [Forms]![frm_Reports]![cmbLoan]
If IsNull(Me.cmbLoan) Then
myMessage = "Please select a loan"
myTitle = "No Selection"
myStyle = vbCritical + vbOKOnly
myResponse = MsgBox(myMessage, myStyle, myTitle)
Exit Sub
Else
'DoCmd.SetWarnings False
Call updateTEMP_OWNERS
If DoesObjectExist("Tables", "tmp_Exhibit7") = 0 Then
Else
DoCmd.DeleteObject acTable, "tmp_Exhibit7"
End If
If DoesObjectExist("Tables", "tmp_RealEstateAppraisal") = 0
Then
Else
DoCmd.DeleteObject acTable, "tmp_RealEstateAppraisal"
End If
If DoesObjectExist("Tables", "tmp_Submission") = 0 Then
Else
DoCmd.DeleteObject acTable, "tmp_Submission"
End If
If DoesObjectExist("Tables", "tmp_CheckLists") = 0 Then
Else
DoCmd.DeleteObject acTable, "tmp_CheckLists"
End If
If DoesObjectExist("Tables", "tmp_Environmental") = 0 Then
Else
DoCmd.DeleteObject acTable, "tmp_Environmental"
End If
'qry_Exhibit7
myQRY7 = "SELECT [EXHIBIT 7].EX7_ID, [EXHIBIT 7].EX7_IMP_ID,
[EXHIBIT 7].EX7_AR_UNDER_30, [EXHIBIT 7].EX7_AR_30_59, [EXHIBIT
7].EX7_AR_60_89, [EXHIBIT 7].EX7_AR_OVER_90, [EXHIBIT
7].EX7_AP_UNDER_30,
[EXHIBIT 7].EX7_AP_30_59, [EXHIBIT 7].EX7_AP_60_89, [EXHIBIT
7].EX7_AP_OVER_90, [EXHIBIT 7].EX7_AR_CONCENTRATION, [EXHIBIT
7].EX7_AP_CONCENTRATION,
[EX7_AR_UNDER_30]+[EX7_AR_30_59]+[EX7_AR_60_89]+[EX7_AR_OVER_90] AS
AR_TOTAL,
[EX7_AP_UNDER_30]+[EX7_AP_30_59]+[EX7_AP_60_89]+[EX7_AP_OVER_90] AS
AP_TOTAL, Format([AR_TOTAL]/[EX7_AR_OVER_90],'#%') AS AR_PERCENT,
Format([AP_TOTAL]/[EX7_AP_OVER_90],'#%') AS AP_PERCENT INTO
tmp_Exhibit7
" & _
"FROM [EXHIBIT 7] " & _
"WHERE [EXHIBIT 7].EX7_IMP_ID = " & myID
myDB.Execute (myQRY7)
'qry_RealEstateAppraisal
myQRYRealEstateAppraisal = "SELECT IMPORT.IMP_ID,
IMPORT.IMP_BOR_NAME, IMPORT.IMP_BOR_PROJECT_ADDRESS,
IMPORT.IMP_BOR_PROJECT_ADDRESS2, IMPORT.IMP_BOR_PROJECT_CITY,
IMPORT.IMP_BOR_PROJECT_STATE, IMPORT.IMP_BOR_PROJECT_ZIP,
IMPORT.IMP_BOR_PRINCIPAL, IMPORT.IMP_BOR_SBC, IMPORT.IMP_BOR_DBA,
IMPORT.IMP_BOR_EMAIL, SETUP.SET_COMPANY_NAME, SETUP.SET_COMPANY_OWNER,
SETUP.SET_ADDRESS, SETUP.SET_CITY, SETUP.SET_STATE, SETUP.SET_ZIP,
SETUP.SET_PHONE, SETUP.SET_FAX, SETUP.SET_EMAIL1 INTO
tmp_RealEstateAppraisal " & _
"FROM IMPORT, SETUP " & _
"WHERE IMPORT.IMP_ID= " & myID
myDB.Execute (myQRYRealEstateAppraisal)
'qry_myQRY1244_Step1
myQRY1244_Step1 = "SELECT LOAN_OFFICER.LASTNAME & ', ' &
LOAN_OFFICER.FIRSTNAME AS LOAN_OFFICER_NAME, UNDERWRITER.LASTNAME & ',
'
& UNDERWRITER.FIRSTNAME AS UNDERWRITER_NAME, IIf([IMP_BOR_EPC]=-1,'YES
X
NO','YES NO X') AS EPC, [1244 A].[1244A_OMB_APPROVAL_NUMBER],
[1244
A].[1244A_EXPIRATION_DATE], [1244 A].[1244A_Q1] AS Q1, [1244
A].[1244A_Q2] AS Q2, [1244 A].[1244A_Q3] AS Q3, [1244 A].[1244A_Q4] AS
Q4, [1244 A].[1244A_Q5] AS Q5, [1244 A].[1244A_Q6] AS Q6, [1244
A].[1244A_Q7] AS Q7, [1244 A].[1244A_Q8] AS Q8, [1244 A].[1244A_Q9] AS
Q9, [1244 A].[1244A_Q10] AS Q10, [1244 A].[1244A_Q11] AS Q11, [1244
A].[1244A_Q12] AS Q12, [1244 A].[1244A_Q13] AS Q13, [1244
A].[1244A_Q14]
AS Q14, [1244 A].[1244A_Q15] AS Q15, [1244 A].[1244A_Q16] AS Q16, [1244
A].[1244A_Q17] AS Q17, [1244 A].[1244A_Q18] AS Q18, [1244
A].[1244A_Q19]
AS Q19, [1244 A].[1244A_Q20] AS Q20, [1244 A].[1244A_Q21] AS Q21, [1244
A].[1244A_Q22] AS Q22, [1244 A].[1244A_Q23] AS Q23, [1244
A].[1244A_CLOSING_COST], " & _
"MINORITY.MIN_CODE,
MINORITY.MIN_DESCRIPTION, YES_NO.YES_DESCRIPTION,
IIf([IMP_BOR_RELOCATION]=-1,'YES X NO','YES NO X') AS REL,
IIf([IMP_BOR_EXISTING_BUSINESS]=-1,'X Existing ' & Chr(13) & Chr(10) &
'
New Business',' Existing ' & Chr(13) & Chr(10) & 'X New Business')
AS
EXISTING, IMPORT.*, [IMP_BOR_JOBS_TWO_YEARS]+[IMP_BOR_JOBS_RETAINED] AS
JOB_TOTAL,
[IMP_BOR_PURCHASE_LAND_BUILDING]+[IMP_BOR_CONSTRUCTION_REMODELING]+[IMP_BOR­_PURCHASE_EQUIPMENT]+[IMP_BOR_PROFESSIOAL_FEES]+[IMP_BOR_OTHER_EXPENSES]
AS TOTAL_PROJECT_COST,
Format([IMP_BOR_SBA_PORTION_AMOUNT]*0.005,'$#,##0') AS RESERVE,
Format([IMP_BOR_SBA_PORTION_AMOUNT]*0.0025,'$#,##0') AS FUNDING_FEE,
Format([IMP_BOR_SBA_PORTION_AMOUNT]*0.015,'$#,##0') AS CDC_FEE,
Format([1244A_CLOSING_COST],'$#,##0') AS CLOSING_COST,
Format(([IMP_BOR_SBA_PORTION_AMOUNT]*0.005)+([IMP_BOR_SBA_PORTION_AMOUNT]*0­.0025)+([IMP_BOR_SBA_PORTION_AMOUNT]*0.015)+[1244A_CLOSING_COST],'$#,##0')
AS TOTAL_FEES, " & _
"Format(CEILING(([IMP_BOR_SBA_PORTION_AMOUNT]+[TOTAL_FEES])/0.996,1000)*0.0­04,'$#,##0')
AS UNDERWRITER_FEE,
Format(([IMP_BOR_SBA_PORTION_AMOUNT]*0.005)+([IMP_BOR_SBA_PORTION_AMOUNT]*0­.0025)+([IMP_BOR_SBA_PORTION_AMOUNT]*0.015)+[1244A_CLOSING_COST]+(CEILING((­[IMP_BOR_SBA_PORTION_AMOUNT]+[TOTAL_FEES])/0.996,1000)*0.004),'$#,##0')
AS TOTAL_FEE2,
Format(CEILING([IMP_BOR_SBA_PORTION_AMOUNT]+[TOTAL_FEE2],1000),'Currency')
AS TOTAL_DEBENTURE, Format([IMP_BOR_SBA_PORTION_AMOUNT],'$#,##0') AS
SBA_AMOUNT, Format([IMP_BOR_INJECTION],'$#,##0') AS BOR_INJECTION,
Format([IMP_BOR_PARTICIPATING_AMOUNT],'$#,##0') AS
PARTICIPATING_AMOUNT,
Format(CEILING([IMP_BOR_SBA_PORTION_AMOUNT]+[TOTAL_FEE2],1000)-([IMP_BOR_SB­A_PORTION_AMOUNT])+([IMP_BOR_SBA_PORTION_AMOUNT]*0.005)+([IMP_BOR_SBA_PORTI­ON_AMOUNT]*0.0025)+([IMP_BOR_SBA_PORTION_AMOUNT]*0.015)+[1244A_CLOSING_COST­]+(CEILING(([IMP_BOR_SBA_PORTION_AMOUNT]+[TOTAL_FEES])/0.996,1000)*0.004),'­$#,##0')
AS BALANCE_TO_BORROWER, " & _
"[PURPOSE ECONOMIC].PUR_DESCRIPTION,
Format([IMP_BOR_SBA_PORTION_AMOUNT]/([IMP_BOR_SBA_PORTION_AMOUNT]+[IMP_BOR_­INJECTION]+[IMP_BOR_PARTICIPATING_AMOUNT]),'#%')
AS SBA_PERCENT,
Format([IMP_BOR_INJECTION]/([IMP_BOR_SBA_PORTION_AMOUNT]+[IMP_BOR_INJECTION­]+[IMP_BOR_PARTICIPATING_AMOUNT]),'#%')
AS INJECTION_PERCENT,
Format([IMP_BOR_PARTICIPATING_AMOUNT]/([IMP_BOR_SBA_PORTION_AMOUNT]+[IMP_BO­R_INJECTION]+[IMP_BOR_PARTICIPATING_AMOUNT]),'#%')
AS PARTICIPATING_PERCENT,
Format([IMP_BOR_SBA_PORTION_AMOUNT]+[IMP_BOR_INJECTION]+[IMP_BOR_PARTICIPAT­ING_AMOUNT],'$#,##0')
AS TOTAL_AMOUNT, Format([IMP_BOR_PARTICIPATING_RATE]/100,'#.##%') AS
PARTICIPATING_RATE, IIf([IMP_BOR_SBA_AMORTIZATION]=10,'2nd Lien','2nd
RE') AS LIEN_SBA, IIf([IMP_BOR_SBA_AMORTIZATION]=10,'1st Lien','1st
RE')
AS LIEN_PARTICIPATING FROM [PURPOSE ECONOMIC] RIGHT JOIN ((((([1244 A]
RIGHT JOIN IMPORT ON [1244 A].[1244A_IMP_ID] = IMPORT.IMP_ID) " & _
"LEFT JOIN EMPLOYEES AS LOAN_OFFICER ON
IMPORT.IMP_LOAN_OFFICER_ID = LOAN_OFFICER.EMPID) LEFT JOIN EMPLOYEES AS
UNDERWRITER ON IMPORT.IMP_UNDERWRITER_ID = UNDERWRITER.EMPID) LEFT JOIN
MINORITY ON IMPORT.IMP_BOR_MIN_ID = MINORITY.MIN_ID) LEFT JOIN YES_NO
ON
IMPORT.IMP_BOR_MTY_ID = YES_NO.YES_SHORT_DESCRIPTION) ON [PURPOSE
ECONOMIC].PUR_ID = IMPORT.IMP_BOR_IMPACT " & _
"WHERE [1244A].[1244A_IMP_ID]=" & myID
'myDB.Execute (myQRY1244_Step1)
DoCmd.OpenQuery "qry_1244_Step2"
DoCmd.OpenQuery "qry_TrackingSheet"
DoCmd.OpenQuery "qry_TEMP_OWNER_GUARANTOR"
DoCmd.OpenQuery "qry_1244B_Step1"
DoCmd.OpenQuery "qry_1244_4_Step2"
DoCmd.OpenQuery "qry_Submission_Step2"
DoCmd.OpenQuery "qry_CheckLists_Step2"
DoCmd.OpenQuery "qry_Environmental_Step2"
DoCmd.OpenQuery "qry_Supplement_Step2"
DoCmd.SetWarnings True
End If
If IsLoaded("frm_setup") Then
Else
DoCmd.OpenForm "frm_setup", acNormal, , , , acHidden
End If
ReportPath = Forms![frm_Setup]![SET_CURRENT_DATABASE]
Set oApp = CreateObject("Word.Application")
'PRINT REPORTS
'EXHIBIT 1
If Me.ckbExhibit1 = -1 Then
Call updateTEMP_OWNERS
oApp.Documents.Open FileName:="" & Chr(34) & ReportPath &
"\Exhibit
Click to expand...
Click to expand...

...

read more »- Hide quoted text -

- Show quoted text -
Click to expand...
 
R

Rodger

OK I did a little more work on this today.

If I run the query straight out from Access not any code or anything I still
get the error that it wants to delete the table before it adds data to it.
I checked my options and the confirm deletions is unchecked. Any idea why
it is giving me a warning when I and running a make table query? I thought
that I was able to suppress these warnings.




Rodger said:
Hello All,

I have a form that I use to print all my reports. The reports are
actually in Word and use different queries as the datasource. The queries
create temp table. When the code runs it gives me a warning about
deleting the table even though I have Warnings set to False. Here is the
code that I have.

myView is used to either open in Word (1) or Print to the Printer (2)

Private Sub myReports(myView)

On Error GoTo Err_myReports

Dim ReportPath
Dim oApp As Object, DocName, x, myDB
Dim dbsCurrent As Database, dbsPath As String
Dim myMessage, myStyle, myTitle, myResponse
Dim myQRY7, myQRYRealEstateAppraisal, myQRY1244_Step1, myQRY1244_Step2,
myQRY1244B_Step1
Dim myQRYTrackingSheet, myQRY_TEMP_OWNER_GUARANTOR
Dim myID

Set myDB = CurrentDb()

myID = [Forms]![frm_Reports]![cmbLoan]


If IsNull(Me.cmbLoan) Then
myMessage = "Please select a loan"
myTitle = "No Selection"
myStyle = vbCritical + vbOKOnly
myResponse = MsgBox(myMessage, myStyle, myTitle)
Exit Sub
Else
'DoCmd.SetWarnings False
Call updateTEMP_OWNERS

If DoesObjectExist("Tables", "tmp_Exhibit7") = 0 Then
Else
DoCmd.DeleteObject acTable, "tmp_Exhibit7"
End If

If DoesObjectExist("Tables", "tmp_RealEstateAppraisal") = 0
Then
Else
DoCmd.DeleteObject acTable, "tmp_RealEstateAppraisal"
End If

If DoesObjectExist("Tables", "tmp_Submission") = 0 Then
Else
DoCmd.DeleteObject acTable, "tmp_Submission"
End If

If DoesObjectExist("Tables", "tmp_CheckLists") = 0 Then
Else
DoCmd.DeleteObject acTable, "tmp_CheckLists"
End If

If DoesObjectExist("Tables", "tmp_Environmental") = 0 Then
Else
DoCmd.DeleteObject acTable, "tmp_Environmental"
End If


'qry_Exhibit7
myQRY7 = "SELECT [EXHIBIT 7].EX7_ID, [EXHIBIT 7].EX7_IMP_ID,
[EXHIBIT 7].EX7_AR_UNDER_30, [EXHIBIT 7].EX7_AR_30_59, [EXHIBIT
7].EX7_AR_60_89, [EXHIBIT 7].EX7_AR_OVER_90, [EXHIBIT 7].EX7_AP_UNDER_30,
[EXHIBIT 7].EX7_AP_30_59, [EXHIBIT 7].EX7_AP_60_89, [EXHIBIT
7].EX7_AP_OVER_90, [EXHIBIT 7].EX7_AR_CONCENTRATION, [EXHIBIT
7].EX7_AP_CONCENTRATION,
[EX7_AR_UNDER_30]+[EX7_AR_30_59]+[EX7_AR_60_89]+[EX7_AR_OVER_90] AS
AR_TOTAL, [EX7_AP_UNDER_30]+[EX7_AP_30_59]+[EX7_AP_60_89]+[EX7_AP_OVER_90]
AS AP_TOTAL, Format([AR_TOTAL]/[EX7_AR_OVER_90],'#%') AS AR_PERCENT,
Format([AP_TOTAL]/[EX7_AP_OVER_90],'#%') AS AP_PERCENT INTO tmp_Exhibit7 "
& _
"FROM [EXHIBIT 7] " & _
"WHERE [EXHIBIT 7].EX7_IMP_ID = " & myID
myDB.Execute (myQRY7)

'qry_RealEstateAppraisal
myQRYRealEstateAppraisal = "SELECT IMPORT.IMP_ID,
IMPORT.IMP_BOR_NAME, IMPORT.IMP_BOR_PROJECT_ADDRESS,
IMPORT.IMP_BOR_PROJECT_ADDRESS2, IMPORT.IMP_BOR_PROJECT_CITY,
IMPORT.IMP_BOR_PROJECT_STATE, IMPORT.IMP_BOR_PROJECT_ZIP,
IMPORT.IMP_BOR_PRINCIPAL, IMPORT.IMP_BOR_SBC, IMPORT.IMP_BOR_DBA,
IMPORT.IMP_BOR_EMAIL, SETUP.SET_COMPANY_NAME, SETUP.SET_COMPANY_OWNER,
SETUP.SET_ADDRESS, SETUP.SET_CITY, SETUP.SET_STATE, SETUP.SET_ZIP,
SETUP.SET_PHONE, SETUP.SET_FAX, SETUP.SET_EMAIL1 INTO
tmp_RealEstateAppraisal " & _
"FROM IMPORT, SETUP " & _
"WHERE IMPORT.IMP_ID= " & myID
myDB.Execute (myQRYRealEstateAppraisal)

'qry_myQRY1244_Step1
myQRY1244_Step1 = "SELECT LOAN_OFFICER.LASTNAME & ', ' &
LOAN_OFFICER.FIRSTNAME AS LOAN_OFFICER_NAME, UNDERWRITER.LASTNAME & ', ' &
UNDERWRITER.FIRSTNAME AS UNDERWRITER_NAME, IIf([IMP_BOR_EPC]=-1,'YES X
NO','YES NO X') AS EPC, [1244 A].[1244A_OMB_APPROVAL_NUMBER], [1244
A].[1244A_EXPIRATION_DATE], [1244 A].[1244A_Q1] AS Q1, [1244 A].[1244A_Q2]
AS Q2, [1244 A].[1244A_Q3] AS Q3, [1244 A].[1244A_Q4] AS Q4, [1244
A].[1244A_Q5] AS Q5, [1244 A].[1244A_Q6] AS Q6, [1244 A].[1244A_Q7] AS Q7,
[1244 A].[1244A_Q8] AS Q8, [1244 A].[1244A_Q9] AS Q9, [1244 A].[1244A_Q10]
AS Q10, [1244 A].[1244A_Q11] AS Q11, [1244 A].[1244A_Q12] AS Q12, [1244
A].[1244A_Q13] AS Q13, [1244 A].[1244A_Q14] AS Q14, [1244 A].[1244A_Q15]
AS Q15, [1244 A].[1244A_Q16] AS Q16, [1244 A].[1244A_Q17] AS Q17, [1244
A].[1244A_Q18] AS Q18, [1244 A].[1244A_Q19] AS Q19, [1244 A].[1244A_Q20]
AS Q20, [1244 A].[1244A_Q21] AS Q21, [1244 A].[1244A_Q22] AS Q22, [1244
A].[1244A_Q23] AS Q23, [1244 A].[1244A_CLOSING_COST], " & _
"MINORITY.MIN_CODE, MINORITY.MIN_DESCRIPTION,
YES_NO.YES_DESCRIPTION, IIf([IMP_BOR_RELOCATION]=-1,'YES X NO','YES
NO X') AS REL, IIf([IMP_BOR_EXISTING_BUSINESS]=-1,'X Existing ' &
Chr(13) & Chr(10) & ' New Business',' Existing ' & Chr(13) & Chr(10)
& 'X New Business') AS EXISTING, IMPORT.*,
[IMP_BOR_JOBS_TWO_YEARS]+[IMP_BOR_JOBS_RETAINED] AS JOB_TOTAL,
[IMP_BOR_PURCHASE_LAND_BUILDING]+[IMP_BOR_CONSTRUCTION_REMODELING]+[IMP_BOR_PURCHASE_EQUIPMENT]+[IMP_BOR_PROFESSIOAL_FEES]+[IMP_BOR_OTHER_EXPENSES]
AS TOTAL_PROJECT_COST, Format([IMP_BOR_SBA_PORTION_AMOUNT]*0.005,'$#,##0')
AS RESERVE, Format([IMP_BOR_SBA_PORTION_AMOUNT]*0.0025,'$#,##0') AS
FUNDING_FEE, Format([IMP_BOR_SBA_PORTION_AMOUNT]*0.015,'$#,##0') AS
CDC_FEE, Format([1244A_CLOSING_COST],'$#,##0') AS CLOSING_COST,
Format(([IMP_BOR_SBA_PORTION_AMOUNT]*0.005)+([IMP_BOR_SBA_PORTION_AMOUNT]*0.0025)+([IMP_BOR_SBA_PORTION_AMOUNT]*0.015)+[1244A_CLOSING_COST],'$#,##0')
AS TOTAL_FEES, " & _

"Format(CEILING(([IMP_BOR_SBA_PORTION_AMOUNT]+[TOTAL_FEES])/0.996,1000)*0.004,'$#,##0')
AS UNDERWRITER_FEE,
Format(([IMP_BOR_SBA_PORTION_AMOUNT]*0.005)+([IMP_BOR_SBA_PORTION_AMOUNT]*0.0025)+([IMP_BOR_SBA_PORTION_AMOUNT]*0.015)+[1244A_CLOSING_COST]+(CEILING(([IMP_BOR_SBA_PORTION_AMOUNT]+[TOTAL_FEES])/0.996,1000)*0.004),'$#,##0')
AS TOTAL_FEE2,
Format(CEILING([IMP_BOR_SBA_PORTION_AMOUNT]+[TOTAL_FEE2],1000),'Currency')
AS TOTAL_DEBENTURE, Format([IMP_BOR_SBA_PORTION_AMOUNT],'$#,##0') AS
SBA_AMOUNT, Format([IMP_BOR_INJECTION],'$#,##0') AS BOR_INJECTION,
Format([IMP_BOR_PARTICIPATING_AMOUNT],'$#,##0') AS PARTICIPATING_AMOUNT,
Format(CEILING([IMP_BOR_SBA_PORTION_AMOUNT]+[TOTAL_FEE2],1000)-([IMP_BOR_SBA_PORTION_AMOUNT])+([IMP_BOR_SBA_PORTION_AMOUNT]*0.005)+([IMP_BOR_SBA_PORTION_AMOUNT]*0.0025)+([IMP_BOR_SBA_PORTION_AMOUNT]*0.015)+[1244A_CLOSING_COST]+(CEILING(([IMP_BOR_SBA_PORTION_AMOUNT]+[TOTAL_FEES])/0.996,1000)*0.004),'$#,##0')
AS BALANCE_TO_BORROWER, " & _
"[PURPOSE ECONOMIC].PUR_DESCRIPTION,
Format([IMP_BOR_SBA_PORTION_AMOUNT]/([IMP_BOR_SBA_PORTION_AMOUNT]+[IMP_BOR_INJECTION]+[IMP_BOR_PARTICIPATING_AMOUNT]),'#%')
AS SBA_PERCENT,
Format([IMP_BOR_INJECTION]/([IMP_BOR_SBA_PORTION_AMOUNT]+[IMP_BOR_INJECTION]+[IMP_BOR_PARTICIPATING_AMOUNT]),'#%')
AS INJECTION_PERCENT,
Format([IMP_BOR_PARTICIPATING_AMOUNT]/([IMP_BOR_SBA_PORTION_AMOUNT]+[IMP_BOR_INJECTION]+[IMP_BOR_PARTICIPATING_AMOUNT]),'#%')
AS PARTICIPATING_PERCENT,
Format([IMP_BOR_SBA_PORTION_AMOUNT]+[IMP_BOR_INJECTION]+[IMP_BOR_PARTICIPATING_AMOUNT],'$#,##0')
AS TOTAL_AMOUNT, Format([IMP_BOR_PARTICIPATING_RATE]/100,'#.##%') AS
PARTICIPATING_RATE, IIf([IMP_BOR_SBA_AMORTIZATION]=10,'2nd Lien','2nd RE')
AS LIEN_SBA, IIf([IMP_BOR_SBA_AMORTIZATION]=10,'1st Lien','1st RE') AS
LIEN_PARTICIPATING FROM [PURPOSE ECONOMIC] RIGHT JOIN ((((([1244 A] RIGHT
JOIN IMPORT ON [1244 A].[1244A_IMP_ID] = IMPORT.IMP_ID) " & _
"LEFT JOIN EMPLOYEES AS LOAN_OFFICER ON
IMPORT.IMP_LOAN_OFFICER_ID = LOAN_OFFICER.EMPID) LEFT JOIN EMPLOYEES AS
UNDERWRITER ON IMPORT.IMP_UNDERWRITER_ID = UNDERWRITER.EMPID) LEFT JOIN
MINORITY ON IMPORT.IMP_BOR_MIN_ID = MINORITY.MIN_ID) LEFT JOIN YES_NO ON
IMPORT.IMP_BOR_MTY_ID = YES_NO.YES_SHORT_DESCRIPTION) ON [PURPOSE
ECONOMIC].PUR_ID = IMPORT.IMP_BOR_IMPACT " & _
"WHERE [1244A].[1244A_IMP_ID]=" & myID

'myDB.Execute (myQRY1244_Step1)



DoCmd.OpenQuery "qry_1244_Step2"
DoCmd.OpenQuery "qry_TrackingSheet"
DoCmd.OpenQuery "qry_TEMP_OWNER_GUARANTOR"

DoCmd.OpenQuery "qry_1244B_Step1"
DoCmd.OpenQuery "qry_1244_4_Step2"
DoCmd.OpenQuery "qry_Submission_Step2"
DoCmd.OpenQuery "qry_CheckLists_Step2"
DoCmd.OpenQuery "qry_Environmental_Step2"

DoCmd.OpenQuery "qry_Supplement_Step2"

DoCmd.SetWarnings True
End If

If IsLoaded("frm_setup") Then
Else
DoCmd.OpenForm "frm_setup", acNormal, , , , acHidden
End If

ReportPath = Forms![frm_Setup]![SET_CURRENT_DATABASE]

Set oApp = CreateObject("Word.Application")

'PRINT REPORTS
'EXHIBIT 1
If Me.ckbExhibit1 = -1 Then
Call updateTEMP_OWNERS

oApp.Documents.Open FileName:="" & Chr(34) & ReportPath &
"\Exhibit 1.doc" & Chr(34)
If myView = 1 Then
oApp.PrintOut
Else
oApp.Visible = True
End If
End If

I have more but the errors come before this so I did not add as it is just
more of the same from the lines above.

TIA,
Rodger
Click to expand...
 
J

John W. Vinson

If I run the query straight out from Access not any code or anything I still
get the error that it wants to delete the table before it adds data to it.
I checked my options and the confirm deletions is unchecked. Any idea why
it is giving me a warning when I and running a make table query? I thought
that I was able to suppress these warnings.
Click to expand...

Confirm Deletions posts a warning message if you run a query which *deletes
records from an existing table*.

That's not what you're doing. You're deleting the entire table.

Just use error trapping to trap the error and don't issue a message. Does your
routine have an

On Error GoTo Proc_Error

or some such line? If not... it should!!!


John W. Vinson [MVP]
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

exit sub not working 6
Event procedure not working 2
Goto ErrorHandler give "label not defined" error 2

Top