Access 2007 - queries & printing - form vs reports

In order to select and sort records for viewing on a form, I create a query then open my form using the query.  Then from the form which happens to be landscape, I have a button for printing and the only way I've gotten it to work is to define the query a second time and open a report with the query and sending it to the printer.  It seems so redundant.  I'm thinking there must be a better way.  So here are my questions.

1) Is there a way to sort the records if I open the form using the filters?  It seems like a filtered form does not have an option for sorting the records.
2) Using an embedded macro for printing on the form always prints in portrait.  From what I've read, I don't think there is a way to change the orientation of the form to use landscape.  Has this changed?
3) Can I optimize my code by only using a report instead of a form?  Since my form and report are almost identical, I was wondering if I could get rid of my form and just use the report..... but then I couldn't figure out a way to make it do the same thing, preview the report then print if the user presses a print button.  If I put a print button on the report, is there a way to call the same report without having to re-define the query again?  

I'm guessing I'm making this more complicated than I need to.  Please help.
Helen
greenprgAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dale FyeCommented:
In general, forms are designed for entering and editing information, and do not generally work well as reports (although some people insist on printing forms).  Generally you should create a separate report if you want to print your data, although you might be able to use the same query on a form and on the report that applies to this form.
With regard to your specific questions.
1.  Yes, you can sort a form based on various columns using the OrderBy property of the form.  You can do this in either single or continuous view, although it is easier to see when you use a continuous form and can see multiple records at one time.  To do this, you might add a combo box to the header or footer of the form, and set the combo boxes Row Source Type property (data tab) to "Field List", and set the Row Source property to the name of the query or table that the form uses.  Then, in the AfterUpdate event of that combo box, change the forms OrderBy property.  This might look like:

Private Sub cbo_OrderBy_AfterUpdate

    me.OrderBy = me.cbo_OrderBy

End Sub

If you are using a continuous form, you might want to consider disconnecting the column header labels and putting them in the forms headers.  Then you could use the click event of each of those headers to change the sort column and direction.  You would have to put code similar to the following in the click event of each of these labels.

Private Sub lbl_field1_Click

    if instr(me.OrderBy, "Field1") = 0 Then
        me.OrderBy = "[Field1]"
    elseif instr(me.OrderBy, "DESC") = 0 then
        me.OrderBy = "[Field1] DESC"
    else
        me.OrderBy = "[Field1]"
    End If

End Sub
   
2.  As stated before, forms are designed for editing and viewing and are optimized for the screen size and resolution of your users.  Reports are designed for printing, and should be created with readability (landscape/portrait) AND paper size.  Create a report that is based on your form and print it instead of the form.

3.  No.  Use the form for entering and reviewing your data.  Use a report to print or send that information to others.

HTH
Dale
0
lee555J5Commented:
1. Yes. You should be able to right-click any field on your form and sort ascending or descending.
2. Yes, you can set a form to print landscape. This is a design time setting, but your users should be able to select the view they want. However, if you design your form wide for landscape, it will not automatically adjust to print portrait; that is, you will clip part of your form to the next sheet.
With the form open, click the Office button, select Print, and select Print Preview. Click Page Setup and select the Page tab. You can select landscape there.
3. No. If you only give your users a report, they can never do data entry.
Lee
0
greenprgAuthor Commented:
Thank you both for all the info.  It helps alot.  It sounds like I'm going in the right direction....it just seems redundant to have a form and a report that look almost identical.

One last question, if I used a filter on my form, then a combo box to define the OrderBy (so I can use more than 1 field for the sort order), once the user is viewing the report they like, is there an easy way to send it all to the printer?  I'm currently re-defining the query for the report all over again since the search criteria is on the previous form that called the form which the user is now viewing.  For the Print button on this form I have to recreate the query and replace Me! with Forms!ToolingSrchCriteria! which makes my code even longer.

Is there a way to optimize this?

Thanks!
Helen

The viewing form uses the following code....

targetStr = "ToolingNum Is Not Null"
If targetProgram <> "" Then
   targetStr = "CustomerProg = " & Chr$(34) & Me!targetProgram & Chr$(34)
End If
If targetRequestor <> "" Then
   targetStr = targetStr & " AND Requestor = " & Chr$(34) & Me!targetRequestor & Chr$(34)
End If
If targetEngineer <> "" Then
   targetStr = targetStr & " AND (AssignedTo = " & Chr$(34) & Me!targetEngineer & Chr$(34) & " OR CheckedBy = " & Chr$(34) & Me!targetEngineer & Chr$(34) & ")"
End If
If SortOrder = "Status-Program-Request Date" Then
   strSort = "Status, CustomerProg, ReqstrDate"
Else
   strSort = "Customer, CustomerProg, ReqstrDate"
End If
strFields = "ToolingNum, Revision, ToolName, Requestor, RequstrDate, DateRequired, AssignedTo, Status, Customer, CustomerProg"
Set QD = db.CreateQueryDef("MyQuery", "SELECT " & strFields & " FROM ToolingTable WHERE " & strWhere  & " ORDER BY " & strSort & ";")

which becomes this for the print button on the viewing form....

   targetStr = "RecordNum Is Not Null"

   If Forms!ToolingSrchCriteria!targetProgram <> "" Then
      targetStr = "CustomerProg = " & Chr$(34) & Forms!ToolingSrchCriteria!targetProgram & Chr$(34) & ""
   End If
   
   If Forms!ToolingSrchCriteria!targetRequestor <> "" Then
      targetStr = targetStr & " AND Requestor = " & Chr$(34) & Forms!ToolingSrchCriteria!targetRequestor & _
      Chr$(34) & ""
   End If
   
   If Forms!ToolingSrchCriteria!targetEngineer <> "" Then
      targetStr = targetStr & " AND AssignedTo = " & Chr$(34) & Forms!ToolingSrchCriteria!targetEngineer & _
      Chr$(34) & ""
   End If

etc.   .....which seems so redundant.....

Open in new window

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Download your Guide
Dale FyeCommented:
Once you have the Filter and Sort order defined in your form, you should be able to open the report with the same criteria using the [WhereCondition] parameter of the OpenReport method.  Once the report is open, you should be able to change the SortOrder of the report using the OrderBy and OrderByOn properties of the report (unless the report already has a Sort Order defined in report).  To do this you might do something like:

Private sub cmd_PreviewReport

    docmd.openreport "reportName", acViewPreview, , me.filter
    reports("reportName").OrderBy = me.OrderBy
    reports("reportName").OrderByOn = true

End Sub

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
greenprgAuthor Commented:
Thanks for all the suggestions. I have a lot to learn and lots of new "concepts" for me to practice with.

Thank you!
Helen
0
greenprgAuthor Commented:
I appreciate all the advice and tips.  My code will be much better with all your suggestions.

Thanks!
Helen
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.