hello,
I have some code that cycles through a recordset and then prints out each a report for each school and saves it through acrobat distiller.
this all works fine except that access when I run this module will insist on popping up our hp printer rather than acrobat. The default is acrobat and when I click print in any other part of access eg a table then the dialog box pops up with acrobat pdf as the default, why is this code making the hp pop up first?
any ideas?
below is the code..
Private Sub KS4_Click()
Dim repQuery2 As QueryDef
Dim dBase2 As Database
Dim rsRep2 As DAO.Recordset
Dim strrep2 As String
Dim data2 As Integer
Dim KS4_iresponse1 As Integer
Dim KS4_iresponse2 As Integer
KS4_iresponse1 = MsgBox("Do you want to view all reports?", vbYesNo)
Select Case KS4_iresponse1
Case vbYes
Set dBase2 = CurrentDb()
Set repQuery2 = dBase2.QueryDefs("PDF_KS4_pdf_export")
Set rsRep2 = CurrentDb.OpenRecordset("1_KS4_PerformanceForms")
repQuery2.sql = "SELECT * FROM 2_KS4_report_query;"
repQuery2.Close
DoCmd.OpenReport "KS4_report", acViewPreview
Case vbNo
KS4_iresponse2 = MsgBox("do you want to export all the files?", vbYesNo)
Select Case KS4_iresponse2
Case vbYes
Set dBase2 = CurrentDb()
Set repQuery2 = dBase2.QueryDefs("PDF_KS4_pdf_export")
Set rsRep2 = CurrentDb.OpenRecordset("1_KS4_PerformanceForms")
Do While Not rsRep2.EOF
data2 = rsRep2.Fields("DFES NO").Value
repQuery2.sql = "SELECT SCHOOL_BASE_DATA_SCHOOL_BASIC_DATA.DFES, * FROM 2_KS4_report_query INNER JOIN SCHOOL_BASE_DATA_SCHOOL_BASIC_DATA ON [2_KS4_report_query].estab = SCHOOL_BASE_DATA_SCHOOL_BASIC_DATA.DFES WHERE ((([2_KS4_report_query].estab)= " & data2 & "));"
repQuery2.Close
DoCmd.Rename "KS4_" & data2, acReport, "KS4_report"
DoCmd.OpenReport "KS4_" & data2, acViewPreview
DoCmd.RunCommand acCmdPrint
DoCmd.RunCommand acCmdClose
DoCmd.Rename "KS4_report", acReport, "KS4_" & data2
rsRep2.MoveNext
Loop
Set rsRep2 = Nothing
Case vbNo
Set dBase2 = CurrentDb()
Set repQuery2 = dBase2.QueryDefs("PDF_KS4_pdf_export")
Set rsRep2 = CurrentDb.OpenRecordset("1_KS4_PerformanceForms")
repQuery2.sql = "SELECT SCHOOL_BASE_DATA_SCHOOL_BASIC_DATA.DFES, * FROM 2_KS4_report_query INNER JOIN SCHOOL_BASE_DATA_SCHOOL_BASIC_DATA ON [2_KS4_report_query].estab = SCHOOL_BASE_DATA_SCHOOL_BASIC_DATA.DFES WHERE ((([2_KS4_report_query].estab)=[Please Enter a DfES Number]));"
repQuery2.Close
DoCmd.OpenReport "KS4_report", acViewPreview
End Select
End Select
Exit_KS4_Click:
Exit Sub
Err_KS4_Click:
MsgBox Err.Description
Resume Exit_KS4_Click
End Sub
I have some code that cycles through a recordset and then prints out each a report for each school and saves it through acrobat distiller.
this all works fine except that access when I run this module will insist on popping up our hp printer rather than acrobat. The default is acrobat and when I click print in any other part of access eg a table then the dialog box pops up with acrobat pdf as the default, why is this code making the hp pop up first?
any ideas?
below is the code..
Private Sub KS4_Click()
Dim repQuery2 As QueryDef
Dim dBase2 As Database
Dim rsRep2 As DAO.Recordset
Dim strrep2 As String
Dim data2 As Integer
Dim KS4_iresponse1 As Integer
Dim KS4_iresponse2 As Integer
KS4_iresponse1 = MsgBox("Do you want to view all reports?", vbYesNo)
Select Case KS4_iresponse1
Case vbYes
Set dBase2 = CurrentDb()
Set repQuery2 = dBase2.QueryDefs("PDF_KS4_pdf_export")
Set rsRep2 = CurrentDb.OpenRecordset("1_KS4_PerformanceForms")
repQuery2.sql = "SELECT * FROM 2_KS4_report_query;"
repQuery2.Close
DoCmd.OpenReport "KS4_report", acViewPreview
Case vbNo
KS4_iresponse2 = MsgBox("do you want to export all the files?", vbYesNo)
Select Case KS4_iresponse2
Case vbYes
Set dBase2 = CurrentDb()
Set repQuery2 = dBase2.QueryDefs("PDF_KS4_pdf_export")
Set rsRep2 = CurrentDb.OpenRecordset("1_KS4_PerformanceForms")
Do While Not rsRep2.EOF
data2 = rsRep2.Fields("DFES NO").Value
repQuery2.sql = "SELECT SCHOOL_BASE_DATA_SCHOOL_BASIC_DATA.DFES, * FROM 2_KS4_report_query INNER JOIN SCHOOL_BASE_DATA_SCHOOL_BASIC_DATA ON [2_KS4_report_query].estab = SCHOOL_BASE_DATA_SCHOOL_BASIC_DATA.DFES WHERE ((([2_KS4_report_query].estab)= " & data2 & "));"
repQuery2.Close
DoCmd.Rename "KS4_" & data2, acReport, "KS4_report"
DoCmd.OpenReport "KS4_" & data2, acViewPreview
DoCmd.RunCommand acCmdPrint
DoCmd.RunCommand acCmdClose
DoCmd.Rename "KS4_report", acReport, "KS4_" & data2
rsRep2.MoveNext
Loop
Set rsRep2 = Nothing
Case vbNo
Set dBase2 = CurrentDb()
Set repQuery2 = dBase2.QueryDefs("PDF_KS4_pdf_export")
Set rsRep2 = CurrentDb.OpenRecordset("1_KS4_PerformanceForms")
repQuery2.sql = "SELECT SCHOOL_BASE_DATA_SCHOOL_BASIC_DATA.DFES, * FROM 2_KS4_report_query INNER JOIN SCHOOL_BASE_DATA_SCHOOL_BASIC_DATA ON [2_KS4_report_query].estab = SCHOOL_BASE_DATA_SCHOOL_BASIC_DATA.DFES WHERE ((([2_KS4_report_query].estab)=[Please Enter a DfES Number]));"
repQuery2.Close
DoCmd.OpenReport "KS4_report", acViewPreview
End Select
End Select
Exit_KS4_Click:
Exit Sub
Err_KS4_Click:
MsgBox Err.Description
Resume Exit_KS4_Click
End Sub