Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations bkrike on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
msgbox
- Thread starter Kori
- Start date
- Thread starter
- #3
I took out the failsafe code to shorten this god awful procedure.
The code line I am having trouble on is on line 1440
Sub PrepareToPrint(l_s_report As String)
'MS ACCESS WILL NOT WORK WITH CRYSTAL AND SETSQLQUERY PROPERTY!!!
100 Dim sngTPP As Single
110 Dim IntWidthT As Integer
120 Dim IntWidthP As Integer
130 Dim sqltext As String
140 Dim add As String
150 Dim ls_printer_driver As String
160 Dim ls_printer_name As String
170 Dim ls_printer_port As String
180 Dim ls_printer_information As String
190 Dim rc As Integer
200 Dim keyvalue As String, keydefault As String, keyname As String
210 Dim sectionname As String, FileName As String
220 Dim i As Integer
230 Dim ret As Integer
240 Dim dyn As Recordset
250 Dim sn As Recordset
260 Dim SS As Recordset
270 Dim query As String
280 Dim bill_to As String
Dim startdate As String
Dim enddate As String
290 If gs_report = "mailing1.rpt" Or gs_report = "custlist.rpt" Or gs_report = "custdet.rpt" Or gs_report = "custquot.rpt" Or gs_report = "sbqu14.rpt" Then
300 frmReports.show
310 frmReports.Hide
320 If gs_report = "mailing1.rpt" Then
330 sqltext = "SELECT * "
340 sqltext = sqltext & "FROM customer_information"
'SQLText = SQLText & "FROM customer_information LEFT OUTER JOIN customer_contact ON customer_information.customer_id = customer_contact.customer_id " & Chr(10)
350 add = " WHERE "
360 ElseIf gs_report = "custlist.rpt" Then
370 sqltext = "SELECT * "
380 sqltext = sqltext & "FROM customer_information"
'SQLText = SQLText & "FROM customer_information LEFT OUTER JOIN customer_contact ON customer_information.customer_id = customer_contact.customer_id, " & Chr(10)
390 add = " WHERE "
400 ElseIf gs_report = "custdet.rpt" Then
410 sqltext = "SELECT * "
420 sqltext = sqltext & "FROM customer_information"
'SQLText = SQLText & "FROM customer_information LEFT OUTER JOIN customer_notes ON customer_information.customer_id = customer_notes.customer_id, " & Chr(10)
'SQLText = SQLText & "customer_information LEFT OUTER JOIN customer_contact ON customer_information.customer_id = customer_contact.customer_id, " & Chr(10)
430 add = " WHERE "
440 ElseIf gs_report = "custquot.rpt" Then
450 sqltext = "SELECT * "
460 sqltext = sqltext & "FROM customer_information"
' SQLText = SQLText & "FROM customer_information RIGHT OUTER JOIN tril_sessions ON customer_information.customer_id = tril_sessions.customer_id, " & Chr(10)
'SQLText = SQLText & "tril_sessions LEFT OUTER JOIN dealer_information ON tril_sessions.cmw_dealer_number = dealer_information.cmw_dealer_number" & Chr(10)
470 add = " WHERE "
480 ElseIf gs_report = "sbqu14.rpt" Then
490 sqltext = "SELECT * "
500 sqltext = sqltext & "FROM customer_information"
510 add = " WHERE "
520 End If
530 If frmCustomerQuery!txtCustomerNum <> "" Then
540 sqltext = sqltext & add & "customer_information.customer_number = '" & dbFormatString(frmCustomerQuery!txtCustomerNum) & "'" & Chr(10)
550 add = " AND "
560 End If
570 If frmCustomerQuery!txtCompanyName <> "" Then
580 sqltext = sqltext & add & "customer_information.company_name LIKE '" & dbFormatString(frmCustomerQuery!txtCompanyName) & "*'" & Chr(10)
590 add = " AND "
600 End If
610 If frmCustomerQuery!txtNameSearch.Text <> "" Then
620 sqltext = sqltext & add & "customer_information.alpha_search LIKE '" & dbFormatString(frmCustomerQuery!txtNameSearch) & "*'" & Chr(10)
630 add = " AND "
640 End If
650 If frmCustomerQuery.ddlbType.Text <> "" Then
660 sqltext = sqltext & add & "(customer_information.customer_type LIKE '" & dbFormatString(frmCustomerQuery!ddlbType.Text) & "*'" & Chr(10)
670 If frmCustomerQuery.ddlbType2.Text <> "" Then
680 add = " OR "
690 sqltext = sqltext & add & "customer_information.customer_type LIKE '" & dbFormatString(frmCustomerQuery!ddlbType2.Text) & "*'" & Chr(10)
700 If frmCustomerQuery.ddlbType3.Text <> "" Then
710 add = " OR "
720 sqltext = sqltext & add & "customer_information.customer_type LIKE '" & dbFormatString(frmCustomerQuery!ddlbType3.Text) & "*'" & Chr(10)
730 End If
740 End If
750 sqltext = sqltext & "
"
760 add = " AND "
770 End If
' If frmCustomerQuery.ddlbType.Text <> "" Then
' SQLText = SQLText & add & "customer_information.customer_type LIKE '" & dbFormatString(frmCustomerQuery!ddlbType.Text) & "*'" & Chr(10)
' add = " OR "
' SQLText = SQLText & add & "customer_information.customer_type_2 LIKE '" & dbFormatString(frmCustomerQuery!ddlbType.Text) & "*'" & Chr(10)
' SQLText = SQLText & add & "customer_information.customer_type_3 LIKE '" & dbFormatString(frmCustomerQuery!ddlbType.Text) & "*'" & Chr(10)
'
' If frmCustomerQuery.ddlbType2.Text <> "" Then
' SQLText = SQLText & add & "customer_information.customer_type LIKE '" & dbFormatString(frmCustomerQuery!ddlbType2.Text) & "*'" & Chr(10)
' SQLText = SQLText & add & "customer_information.customer_type_2 LIKE '" & dbFormatString(frmCustomerQuery!ddlbType2.Text) & "*'" & Chr(10)
' SQLText = SQLText & add & "customer_information.customer_type_3 LIKE '" & dbFormatString(frmCustomerQuery!ddlbType2.Text) & "*'" & Chr(10)
' End If
' If frmCustomerQuery.ddlbType3.Text <> "" Then
' SQLText = SQLText & add & "customer_information.customer_type LIKE '" & dbFormatString(frmCustomerQuery!ddlbType3.Text) & "*'" & Chr(10)
' SQLText = SQLText & add & "customer_information.customer_type_2 LIKE '" & dbFormatString(frmCustomerQuery!ddlbType3.Text) & "*'" & Chr(10)
' SQLText = SQLText & add & "customer_information.customer_type_3 LIKE '" & dbFormatString(frmCustomerQuery!ddlbType3.Text) & "*'" & Chr(10)
' End If
' add = " AND "
' End If
780 If frmCustomerQuery!txtCity <> "" Then
790 sqltext = sqltext & add & "customer_information.city LIKE '" & dbFormatString(frmCustomerQuery!txtCity) & "*'" & Chr(10)
800 add = " AND "
810 End If
820 If frmCustomerQuery!txtState <> "" Then
830 sqltext = sqltext & add & "customer_information.state LIKE '" & dbFormatString(frmCustomerQuery!txtState) & "*'" & Chr(10)
840 add = " AND "
850 End If
860 If frmCustomerQuery!txtZip <> "" Then
870 sqltext = sqltext & add & "customer_information.zip LIKE '" & frmCustomerQuery!txtZip & "*'" & Chr(10)
880 add = " AND "
890 End If
900 If frmCustomerQuery!txtCounty <> "" Then
910 sqltext = sqltext & add & "customer_information.county LIKE '" & dbFormatString(frmCustomerQuery!txtCounty) & "*'" & Chr(10)
920 add = " AND "
930 End If
940 If frmCustomerQuery!txtCountry <> "" Then
950 sqltext = sqltext & add & "customer_information.country LIKE '" & dbFormatString(frmCustomerQuery!txtCountry) & "*'" & Chr(10)
960 add = " AND "
970 End If
980 If gs_report <> "sbqu14.rpt" And frmCustomerQuery!txtLastModified <> "" Then
990 sqltext = sqltext & add & "customer_information.last_modified BETWEEN" & " #" & frmCustomerQuery!txtLastModified.Text & "# AND #" & frmCustomerQuery!txtLastModifiedEnd & "#" & Chr(10)
1000 add = " AND "
1010 End If
'Prospect and Customer
1020 If frmCustomerQuery!chkProspect.Value = True And frmCustomerQuery!chkCustomer.Value = True Then
1030 sqltext = sqltext & add & "(customer_information.prospect = 0 OR customer_information.prospect = 1)" & Chr(10)
1040 add = " AND "
'Prospect
1050 ElseIf frmCustomerQuery!chkProspect.Value = True Then
1060 sqltext = sqltext & add & "customer_information.prospect = 1" & Chr(10)
1070 add = " AND "
'Customer
1080 ElseIf frmCustomerQuery!chkCustomer.Value = True Then
1090 sqltext = sqltext & add & "customer_information.prospect = 0" & Chr(10)
1100 add = " AND "
1110 End If
1120 If frmCustomerQuery!ddlbBillToCustomer.Text <> "" Then
1130 query = "SELECT * FROM customer_information WHERE customer_information.alpha_search = '" & dbFormatString(frmCustomerQuery!ddlbBillToCustomer.Text) & "'" & Chr(10)
1140 Set SS = g_cmwdb.OpenRecordset(query, dbOpenSnapshot, dbReadOnly)
1150 If Not SS.EOF Then
1160 If Not IsNull(SS!bill_to_customer) Then
1170 bill_to = SS!bill_to_customer
1180 End If
1190 End If
1200 SS.Close
1210 sqltext = sqltext & add & "customer_information.bill_to_customer = '" & dbFormatString(bill_to) & "'" & Chr(10)
1220 add = " AND "
1230 End If
1240 If frmCustomerQuery!ddlbTerritory.Text <> "" Then
1250 sqltext = sqltext & add & "customer_information.territory LIKE '" & dbFormatString(frmCustomerQuery!ddlbTerritory.Text) & "*'" & Chr(10)
1260 add = " AND "
1270 End If
1280 If frmCustomerQuery!ddlbMultiplierCode.Text <> "" Then
1290 sqltext = sqltext & add & "customer_information.price_code LIKE '" & dbFormatString(frmCustomerQuery!ddlbMultiplierCode.Text) & "*'" & Chr(10)
1300 add = " AND "
1310 End If
1320 If frmCustomerQuery!txtIntPhoneNumber <> "" Then
1330 sqltext = sqltext & add & "customer_information.telephone_number LIKE '" & frmCustomerQuery!txtIntPhoneNumber & "*'" & Chr(10)
1340 add = " AND "
1350 End If
1360 If frmCustomerQuery!ddlbDealership <> "" Then
1370 sqltext = sqltext & add & "customer_information.dba_name = '" & dbFormatString(frmCustomerQuery!ddlbDealership) & "'" & Chr(10)
1380 End If
'Delete all records from customer temp tables
1390 g_cmwdb.Execute "DELETE FROM w_customer_info", 64
1400 g_cmwdb.Execute "DELETE FROM w_quote_range", 64
1410 Set sn = g_cmwdb.OpenRecordset(sqltext, dbOpenSnapshot, dbReadOnly)
1420 Set dyn = g_cmwdb.OpenRecordset("w_customer_info", dbOpenDynaset, 0, dbPessimistic)
'If no record found then give msg and get out of routine
1430 If sn.EOF Then
1440 ret = MsgBox("No records found with the selections made.", vbOKOnly)
1450 Unload frmCustomerQuery
1460 GoTo FailSafe_Exit
1470 Else
1480 sn.MoveFirst
End If
1490 Do While Not (sn.EOF)
1500 dyn.AddNew
1510 dyn!customer_id = sn!customer_id
1520 dyn!cmw_dealer_number = sn!cmw_dealer_number
1530 dyn!territory = sn!territory
1540 dyn!address_line_1 = sn!address_line_1
1550 dyn!address_line_2 = sn!address_line_2
1560 dyn!address_line_3 = sn!address_line_3
1570 dyn!address_line_4 = sn!address_line_4
1580 dyn!alpha_search = sn!alpha_search
1590 dyn!bill_to_customer = sn!bill_to_customer
1600 dyn!city = sn!city
1610 dyn!comments = sn!comments
1620 dyn!company_name = sn!company_name
1630 dyn!county = sn!county
1640 dyn!customer_number = sn!customer_number
1650 dyn!CUSTOMER_TYPE = sn!CUSTOMER_TYPE
1660 dyn!dba_name = sn!dba_name
1670 dyn!domestic = sn!domestic
1680 dyn!domestic_address_detail = sn!domestic_address_detail
1690 dyn!domestic_address_detail_2 = sn!domestic_address_detail_2
1700 dyn!domestic_address_detail_3 = sn!domestic_address_detail_3
1710 dyn!fax_number = sn!fax_number
1720 dyn!last_modified = sn!last_modified
1730 dyn!new_in_system = sn!new_in_system
1740 dyn!po_box_number = sn!po_box_number
1750 dyn!po_number = sn!po_number
1760 dyn!po_number_required = sn!po_number_required
1770 dyn!price_code = sn!price_code
1780 dyn!prospect = sn!prospect
1790 dyn!sales_tax = sn!sales_tax
1800 dyn!State = sn!State
1810 dyn!tax_code = sn!tax_code
1820 dyn!tax_id = sn!tax_id
1830 dyn!telephone_number = sn!telephone_number
1840 dyn!zip = sn!zip
1850 dyn!country = sn!country
1860 dyn.Update
1870 sn.MoveNext
1880 Loop
1890 dyn.Close
1900 sn.Close
'I am seeing if we are using the "Customer Quote Summary"
'report. if so I need to know the date ranges for the
'last modified quotes not last modified customers
1910 If gs_report = "sbqu14.rpt" Then
1920 Set dyn = g_cmwdb.OpenRecordset("w_quote_range", dbOpenDynaset, 0, dbPessimistic)
1930 dyn.AddNew
1940 dyn!quote_start = frmCustomerQuery!txtLastModified.Text
1950 dyn!quote_end = frmCustomerQuery!txtLastModifiedEnd.Text
1960 dyn.Update
1970 dyn.Close
1980 Call GlobalReport
1990 End If
2000 Else
2010 frmReports.rptReports.SelectionFormula = ""
2020 End If
2030 Unload frmCustomerQuery
2040 frmReports.rptReports.ReportFileName = l_s_report
2050 If g_i_print = 0 Then
'PREVIEW ON SCREEN
'Unload a previous report form version if it exists
2060 Call UnloadReportsForm
'Show the CRW report and set destination to window
2070 frmReports.show
2080 frmReports.Hide
2090 frmReports.rptReports.Destination = 0
'CrystalReport.Preview
'frmReports.cdgPrint.action = 0
'Get the form width in twips
2100 IntWidthT = frmReports.ScaleWidth
'Get the form width in pixels
2110 frmReports.ScaleMode = 3
2120 IntWidthP = frmReports.ScaleWidth
'Reset the ScaleMode back to twips
2130 frmReports.ScaleMode = 1
'Calculate twips/pixel for the current display mode
2140 sngTPP = IntWidthT / IntWidthP
'Set the width and heigh of the window to fit the client area
2150 frmReports.rptReports.WindowWidth = frmReports.ScaleWidth / sngTPP
2160 frmReports.rptReports.WindowTitle = "PrintPreview"
'Now show the window
2170 DoEvents
2180 Else
'SEND TO PRINTER
2190 frmReports.rptReports.Destination = 1
2200 On Error GoTo PreparetoPrintError
2210 frmReports.cdgPrint.action = 5 'Enable Printer Dialog
2220 frmReports.rptReports.CopiesToPrinter = frmReports.cdgPrint.Copies
2230 frmReports.cdgPrint.CancelError = True
2240 End If
'frmReports.rptReports.Connect = "DSN=Accesslogon;UID=;PWD=;DSQ=" & App.Path & "\salesbui.mdb"
2250 frmReports.rptReports.RetrieveDataFiles
2260 frmReports.rptReports.action = 1
'Restore mouse pointer
2270 Screen.MousePointer = 0
2280 GoTo FailSafe_Exit '*** FAILSAFE REM Exit Sub
2290 PreparetoPrintError:
' ret = MsgBox(ERROR, MB_OK + MB_ICONEXCLAMATION)
2300 GoTo FailSafe_Exit '*** FAILSAFE REM Exit Sub
End Sub
The code line I am having trouble on is on line 1440
Sub PrepareToPrint(l_s_report As String)
'MS ACCESS WILL NOT WORK WITH CRYSTAL AND SETSQLQUERY PROPERTY!!!
100 Dim sngTPP As Single
110 Dim IntWidthT As Integer
120 Dim IntWidthP As Integer
130 Dim sqltext As String
140 Dim add As String
150 Dim ls_printer_driver As String
160 Dim ls_printer_name As String
170 Dim ls_printer_port As String
180 Dim ls_printer_information As String
190 Dim rc As Integer
200 Dim keyvalue As String, keydefault As String, keyname As String
210 Dim sectionname As String, FileName As String
220 Dim i As Integer
230 Dim ret As Integer
240 Dim dyn As Recordset
250 Dim sn As Recordset
260 Dim SS As Recordset
270 Dim query As String
280 Dim bill_to As String
Dim startdate As String
Dim enddate As String
290 If gs_report = "mailing1.rpt" Or gs_report = "custlist.rpt" Or gs_report = "custdet.rpt" Or gs_report = "custquot.rpt" Or gs_report = "sbqu14.rpt" Then
300 frmReports.show
310 frmReports.Hide
320 If gs_report = "mailing1.rpt" Then
330 sqltext = "SELECT * "
340 sqltext = sqltext & "FROM customer_information"
'SQLText = SQLText & "FROM customer_information LEFT OUTER JOIN customer_contact ON customer_information.customer_id = customer_contact.customer_id " & Chr(10)
350 add = " WHERE "
360 ElseIf gs_report = "custlist.rpt" Then
370 sqltext = "SELECT * "
380 sqltext = sqltext & "FROM customer_information"
'SQLText = SQLText & "FROM customer_information LEFT OUTER JOIN customer_contact ON customer_information.customer_id = customer_contact.customer_id, " & Chr(10)
390 add = " WHERE "
400 ElseIf gs_report = "custdet.rpt" Then
410 sqltext = "SELECT * "
420 sqltext = sqltext & "FROM customer_information"
'SQLText = SQLText & "FROM customer_information LEFT OUTER JOIN customer_notes ON customer_information.customer_id = customer_notes.customer_id, " & Chr(10)
'SQLText = SQLText & "customer_information LEFT OUTER JOIN customer_contact ON customer_information.customer_id = customer_contact.customer_id, " & Chr(10)
430 add = " WHERE "
440 ElseIf gs_report = "custquot.rpt" Then
450 sqltext = "SELECT * "
460 sqltext = sqltext & "FROM customer_information"
' SQLText = SQLText & "FROM customer_information RIGHT OUTER JOIN tril_sessions ON customer_information.customer_id = tril_sessions.customer_id, " & Chr(10)
'SQLText = SQLText & "tril_sessions LEFT OUTER JOIN dealer_information ON tril_sessions.cmw_dealer_number = dealer_information.cmw_dealer_number" & Chr(10)
470 add = " WHERE "
480 ElseIf gs_report = "sbqu14.rpt" Then
490 sqltext = "SELECT * "
500 sqltext = sqltext & "FROM customer_information"
510 add = " WHERE "
520 End If
530 If frmCustomerQuery!txtCustomerNum <> "" Then
540 sqltext = sqltext & add & "customer_information.customer_number = '" & dbFormatString(frmCustomerQuery!txtCustomerNum) & "'" & Chr(10)
550 add = " AND "
560 End If
570 If frmCustomerQuery!txtCompanyName <> "" Then
580 sqltext = sqltext & add & "customer_information.company_name LIKE '" & dbFormatString(frmCustomerQuery!txtCompanyName) & "*'" & Chr(10)
590 add = " AND "
600 End If
610 If frmCustomerQuery!txtNameSearch.Text <> "" Then
620 sqltext = sqltext & add & "customer_information.alpha_search LIKE '" & dbFormatString(frmCustomerQuery!txtNameSearch) & "*'" & Chr(10)
630 add = " AND "
640 End If
650 If frmCustomerQuery.ddlbType.Text <> "" Then
660 sqltext = sqltext & add & "(customer_information.customer_type LIKE '" & dbFormatString(frmCustomerQuery!ddlbType.Text) & "*'" & Chr(10)
670 If frmCustomerQuery.ddlbType2.Text <> "" Then
680 add = " OR "
690 sqltext = sqltext & add & "customer_information.customer_type LIKE '" & dbFormatString(frmCustomerQuery!ddlbType2.Text) & "*'" & Chr(10)
700 If frmCustomerQuery.ddlbType3.Text <> "" Then
710 add = " OR "
720 sqltext = sqltext & add & "customer_information.customer_type LIKE '" & dbFormatString(frmCustomerQuery!ddlbType3.Text) & "*'" & Chr(10)
730 End If
740 End If
750 sqltext = sqltext & "
"760 add = " AND "
770 End If
' If frmCustomerQuery.ddlbType.Text <> "" Then
' SQLText = SQLText & add & "customer_information.customer_type LIKE '" & dbFormatString(frmCustomerQuery!ddlbType.Text) & "*'" & Chr(10)
' add = " OR "
' SQLText = SQLText & add & "customer_information.customer_type_2 LIKE '" & dbFormatString(frmCustomerQuery!ddlbType.Text) & "*'" & Chr(10)
' SQLText = SQLText & add & "customer_information.customer_type_3 LIKE '" & dbFormatString(frmCustomerQuery!ddlbType.Text) & "*'" & Chr(10)
'
' If frmCustomerQuery.ddlbType2.Text <> "" Then
' SQLText = SQLText & add & "customer_information.customer_type LIKE '" & dbFormatString(frmCustomerQuery!ddlbType2.Text) & "*'" & Chr(10)
' SQLText = SQLText & add & "customer_information.customer_type_2 LIKE '" & dbFormatString(frmCustomerQuery!ddlbType2.Text) & "*'" & Chr(10)
' SQLText = SQLText & add & "customer_information.customer_type_3 LIKE '" & dbFormatString(frmCustomerQuery!ddlbType2.Text) & "*'" & Chr(10)
' End If
' If frmCustomerQuery.ddlbType3.Text <> "" Then
' SQLText = SQLText & add & "customer_information.customer_type LIKE '" & dbFormatString(frmCustomerQuery!ddlbType3.Text) & "*'" & Chr(10)
' SQLText = SQLText & add & "customer_information.customer_type_2 LIKE '" & dbFormatString(frmCustomerQuery!ddlbType3.Text) & "*'" & Chr(10)
' SQLText = SQLText & add & "customer_information.customer_type_3 LIKE '" & dbFormatString(frmCustomerQuery!ddlbType3.Text) & "*'" & Chr(10)
' End If
' add = " AND "
' End If
780 If frmCustomerQuery!txtCity <> "" Then
790 sqltext = sqltext & add & "customer_information.city LIKE '" & dbFormatString(frmCustomerQuery!txtCity) & "*'" & Chr(10)
800 add = " AND "
810 End If
820 If frmCustomerQuery!txtState <> "" Then
830 sqltext = sqltext & add & "customer_information.state LIKE '" & dbFormatString(frmCustomerQuery!txtState) & "*'" & Chr(10)
840 add = " AND "
850 End If
860 If frmCustomerQuery!txtZip <> "" Then
870 sqltext = sqltext & add & "customer_information.zip LIKE '" & frmCustomerQuery!txtZip & "*'" & Chr(10)
880 add = " AND "
890 End If
900 If frmCustomerQuery!txtCounty <> "" Then
910 sqltext = sqltext & add & "customer_information.county LIKE '" & dbFormatString(frmCustomerQuery!txtCounty) & "*'" & Chr(10)
920 add = " AND "
930 End If
940 If frmCustomerQuery!txtCountry <> "" Then
950 sqltext = sqltext & add & "customer_information.country LIKE '" & dbFormatString(frmCustomerQuery!txtCountry) & "*'" & Chr(10)
960 add = " AND "
970 End If
980 If gs_report <> "sbqu14.rpt" And frmCustomerQuery!txtLastModified <> "" Then
990 sqltext = sqltext & add & "customer_information.last_modified BETWEEN" & " #" & frmCustomerQuery!txtLastModified.Text & "# AND #" & frmCustomerQuery!txtLastModifiedEnd & "#" & Chr(10)
1000 add = " AND "
1010 End If
'Prospect and Customer
1020 If frmCustomerQuery!chkProspect.Value = True And frmCustomerQuery!chkCustomer.Value = True Then
1030 sqltext = sqltext & add & "(customer_information.prospect = 0 OR customer_information.prospect = 1)" & Chr(10)
1040 add = " AND "
'Prospect
1050 ElseIf frmCustomerQuery!chkProspect.Value = True Then
1060 sqltext = sqltext & add & "customer_information.prospect = 1" & Chr(10)
1070 add = " AND "
'Customer
1080 ElseIf frmCustomerQuery!chkCustomer.Value = True Then
1090 sqltext = sqltext & add & "customer_information.prospect = 0" & Chr(10)
1100 add = " AND "
1110 End If
1120 If frmCustomerQuery!ddlbBillToCustomer.Text <> "" Then
1130 query = "SELECT * FROM customer_information WHERE customer_information.alpha_search = '" & dbFormatString(frmCustomerQuery!ddlbBillToCustomer.Text) & "'" & Chr(10)
1140 Set SS = g_cmwdb.OpenRecordset(query, dbOpenSnapshot, dbReadOnly)
1150 If Not SS.EOF Then
1160 If Not IsNull(SS!bill_to_customer) Then
1170 bill_to = SS!bill_to_customer
1180 End If
1190 End If
1200 SS.Close
1210 sqltext = sqltext & add & "customer_information.bill_to_customer = '" & dbFormatString(bill_to) & "'" & Chr(10)
1220 add = " AND "
1230 End If
1240 If frmCustomerQuery!ddlbTerritory.Text <> "" Then
1250 sqltext = sqltext & add & "customer_information.territory LIKE '" & dbFormatString(frmCustomerQuery!ddlbTerritory.Text) & "*'" & Chr(10)
1260 add = " AND "
1270 End If
1280 If frmCustomerQuery!ddlbMultiplierCode.Text <> "" Then
1290 sqltext = sqltext & add & "customer_information.price_code LIKE '" & dbFormatString(frmCustomerQuery!ddlbMultiplierCode.Text) & "*'" & Chr(10)
1300 add = " AND "
1310 End If
1320 If frmCustomerQuery!txtIntPhoneNumber <> "" Then
1330 sqltext = sqltext & add & "customer_information.telephone_number LIKE '" & frmCustomerQuery!txtIntPhoneNumber & "*'" & Chr(10)
1340 add = " AND "
1350 End If
1360 If frmCustomerQuery!ddlbDealership <> "" Then
1370 sqltext = sqltext & add & "customer_information.dba_name = '" & dbFormatString(frmCustomerQuery!ddlbDealership) & "'" & Chr(10)
1380 End If
'Delete all records from customer temp tables
1390 g_cmwdb.Execute "DELETE FROM w_customer_info", 64
1400 g_cmwdb.Execute "DELETE FROM w_quote_range", 64
1410 Set sn = g_cmwdb.OpenRecordset(sqltext, dbOpenSnapshot, dbReadOnly)
1420 Set dyn = g_cmwdb.OpenRecordset("w_customer_info", dbOpenDynaset, 0, dbPessimistic)
'If no record found then give msg and get out of routine
1430 If sn.EOF Then
1440 ret = MsgBox("No records found with the selections made.", vbOKOnly)
1450 Unload frmCustomerQuery
1460 GoTo FailSafe_Exit
1470 Else
1480 sn.MoveFirst
End If
1490 Do While Not (sn.EOF)
1500 dyn.AddNew
1510 dyn!customer_id = sn!customer_id
1520 dyn!cmw_dealer_number = sn!cmw_dealer_number
1530 dyn!territory = sn!territory
1540 dyn!address_line_1 = sn!address_line_1
1550 dyn!address_line_2 = sn!address_line_2
1560 dyn!address_line_3 = sn!address_line_3
1570 dyn!address_line_4 = sn!address_line_4
1580 dyn!alpha_search = sn!alpha_search
1590 dyn!bill_to_customer = sn!bill_to_customer
1600 dyn!city = sn!city
1610 dyn!comments = sn!comments
1620 dyn!company_name = sn!company_name
1630 dyn!county = sn!county
1640 dyn!customer_number = sn!customer_number
1650 dyn!CUSTOMER_TYPE = sn!CUSTOMER_TYPE
1660 dyn!dba_name = sn!dba_name
1670 dyn!domestic = sn!domestic
1680 dyn!domestic_address_detail = sn!domestic_address_detail
1690 dyn!domestic_address_detail_2 = sn!domestic_address_detail_2
1700 dyn!domestic_address_detail_3 = sn!domestic_address_detail_3
1710 dyn!fax_number = sn!fax_number
1720 dyn!last_modified = sn!last_modified
1730 dyn!new_in_system = sn!new_in_system
1740 dyn!po_box_number = sn!po_box_number
1750 dyn!po_number = sn!po_number
1760 dyn!po_number_required = sn!po_number_required
1770 dyn!price_code = sn!price_code
1780 dyn!prospect = sn!prospect
1790 dyn!sales_tax = sn!sales_tax
1800 dyn!State = sn!State
1810 dyn!tax_code = sn!tax_code
1820 dyn!tax_id = sn!tax_id
1830 dyn!telephone_number = sn!telephone_number
1840 dyn!zip = sn!zip
1850 dyn!country = sn!country
1860 dyn.Update
1870 sn.MoveNext
1880 Loop
1890 dyn.Close
1900 sn.Close
'I am seeing if we are using the "Customer Quote Summary"
'report. if so I need to know the date ranges for the
'last modified quotes not last modified customers
1910 If gs_report = "sbqu14.rpt" Then
1920 Set dyn = g_cmwdb.OpenRecordset("w_quote_range", dbOpenDynaset, 0, dbPessimistic)
1930 dyn.AddNew
1940 dyn!quote_start = frmCustomerQuery!txtLastModified.Text
1950 dyn!quote_end = frmCustomerQuery!txtLastModifiedEnd.Text
1960 dyn.Update
1970 dyn.Close
1980 Call GlobalReport
1990 End If
2000 Else
2010 frmReports.rptReports.SelectionFormula = ""
2020 End If
2030 Unload frmCustomerQuery
2040 frmReports.rptReports.ReportFileName = l_s_report
2050 If g_i_print = 0 Then
'PREVIEW ON SCREEN
'Unload a previous report form version if it exists
2060 Call UnloadReportsForm
'Show the CRW report and set destination to window
2070 frmReports.show
2080 frmReports.Hide
2090 frmReports.rptReports.Destination = 0
'CrystalReport.Preview
'frmReports.cdgPrint.action = 0
'Get the form width in twips
2100 IntWidthT = frmReports.ScaleWidth
'Get the form width in pixels
2110 frmReports.ScaleMode = 3
2120 IntWidthP = frmReports.ScaleWidth
'Reset the ScaleMode back to twips
2130 frmReports.ScaleMode = 1
'Calculate twips/pixel for the current display mode
2140 sngTPP = IntWidthT / IntWidthP
'Set the width and heigh of the window to fit the client area
2150 frmReports.rptReports.WindowWidth = frmReports.ScaleWidth / sngTPP
2160 frmReports.rptReports.WindowTitle = "PrintPreview"
'Now show the window
2170 DoEvents
2180 Else
'SEND TO PRINTER
2190 frmReports.rptReports.Destination = 1
2200 On Error GoTo PreparetoPrintError
2210 frmReports.cdgPrint.action = 5 'Enable Printer Dialog
2220 frmReports.rptReports.CopiesToPrinter = frmReports.cdgPrint.Copies
2230 frmReports.cdgPrint.CancelError = True
2240 End If
'frmReports.rptReports.Connect = "DSN=Accesslogon;UID=;PWD=;DSQ=" & App.Path & "\salesbui.mdb"
2250 frmReports.rptReports.RetrieveDataFiles
2260 frmReports.rptReports.action = 1
'Restore mouse pointer
2270 Screen.MousePointer = 0
2280 GoTo FailSafe_Exit '*** FAILSAFE REM Exit Sub
2290 PreparetoPrintError:
' ret = MsgBox(ERROR, MB_OK + MB_ICONEXCLAMATION)
2300 GoTo FailSafe_Exit '*** FAILSAFE REM Exit Sub
End Sub
A message box does have a return value which indicates the users selection (i.e. vbCancel, vbNo, etc.) but in this case you use the vbOkOnly constant so the return value can only be vbOk.
Try replacing this line:
ret = MsgBox("No records found with the selections made.", vbOKOnly)
with:
MsgBox "No records found with the selections made.", vbOKOnly
I pasted your code and the msg worked fine the way it is.
Because of that, I'd be interested in seeing what happens inside dbFormatString and and what type of data you're passing. There are no quotes in the actual data itself, correct?
Josh
Try replacing this line:
ret = MsgBox("No records found with the selections made.", vbOKOnly)
with:
MsgBox "No records found with the selections made.", vbOKOnly
I pasted your code and the msg worked fine the way it is.
Because of that, I'd be interested in seeing what happens inside dbFormatString and and what type of data you're passing. There are no quotes in the actual data itself, correct?
Josh
A messagebox does return something...
it returns a constant value that tells you what button the user pressed on. On a 'okonly' messagebox it doesn't have much use, but for example on a 'yesno' messagebox it's darn handy!
I can't see anything wrong with the msgbox code... maybe the error is to be found elswhere...
it returns a constant value that tells you what button the user pressed on. On a 'okonly' messagebox it doesn't have much use, but for example on a 'yesno' messagebox it's darn handy!
I can't see anything wrong with the msgbox code... maybe the error is to be found elswhere...
- Thread starter
- #8
- Status
Similar threads
