Solved: Print orientation problem

[lucky245]

I am creating a report using Sql in an Access module in which I pass in a Request no and a title (see below):banghead:

Sub RunGlobalReport(intPrintno As String, strTitle As String)
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim strSQL As String

Set db = CurrentDb
Set qdf = db.QueryDefs("qryStaffListQuery")

strSQL = CreateEWTDSQL(intPrintno)
qdf.SQL = strSQL
qdf.name = strTitle

DoCmd.OpenQuery strTitle, acViewPreview

qdf.name = "qryStaffListQuery"

End Sub


Function CreateEWTDSQL(intPrintno As Integer) As String
Dim strSQL As String
'Set row source for list box
strSQL = "SELECT strSurname,strInitials, strStaffNo,strGrade,"

If intPrintno = 1 Then strSQL = strSQL & " AvgHoursNorm1, AvgHoursUnsoc1 "
If intPrintno = 2 Then strSQL = strSQL & " AvgHoursNorm2, AvgHoursUnsoc2 "
If intPrintno = 3 Then strSQL = strSQL & " AvgHoursNorm3, AvgHoursUnsoc3 "

strSQL = strSQL & "FROM qryEWTD "
CreateEWTDSQL = strSQL
End Function




This works perfectly except it needs to be in landscape and not portrait, I would also like to reduce the margins from their preset sizes if possible. I do not need this format all of the time hence wishing to change it as required.

Thankyou for any help:bow:

[lucky245]

I am creating a report using Sql in an Access module in which I pass in a Request no and a title (see below):banghead:

Sub RunGlobalReport(intPrintno As String, strTitle As String)
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim strSQL As String

Set db = CurrentDb
Set qdf = db.QueryDefs("qryStaffListQuery")

strSQL = CreateEWTDSQL(intPrintno)
qdf.SQL = strSQL
qdf.name = strTitle

DoCmd.OpenQuery strTitle, acViewPreview

qdf.name = "qryStaffListQuery"

End Sub

'*****************************************************
Function CreateEWTDSQL(intPrintno As Integer) As String
Dim strSQL As String
'Set row source for list box
strSQL = "SELECT strSurname,strInitials, strStaffNo,strGrade,"

If intPrintno = 1 Then strSQL = strSQL & " AvgHoursNorm1, AvgHoursUnsoc1 "
If intPrintno = 2 Then strSQL = strSQL & " AvgHoursNorm2, AvgHoursUnsoc2 "
If intPrintno = 3 Then strSQL = strSQL & " AvgHoursNorm3, AvgHoursUnsoc3 "

strSQL = strSQL & "FROM qryEWTD "
CreateEWTDSQL = strSQL
End Function




This works perfectly except it needs to be in landscape and not portrait, I would also like to reduce the margins from their preset sizes if possible. I do not need this format all of the time hence wishing to change it as required.

Thankyou for any help:bow:

I have created a work around (probably a better solution) where I create a generic Report and just change its Title. I can then easily manipulate the reports printing option using code.

I'm still interested if anyone has a solution to my original problem.

Thanks

[OBP]

If you run the SQL doesn't it generate a query?
If it does can't you then use the query to generate a report?
The text box to change the the title could be on the form that calls the report in the first place, I quite often save the data to a simple table and use dlookup on the report.

[lucky245]

Thanks you posted this at the same time as I managed to work that out. Thanks for your help anyway.

Time and perseverance (http://www.google.co.uk/search?hl=en&sa=X&oi=spell&resnum=0&ct=result&cd=1&q=perseverance&spell=1) will achieve the desired result in the end.
:thumb

[OBP]

Sometimes we get bogged down in the detail and can't see the wood for the trees.:yes