Setting report orientation using VBA

[thudson]

I am trying to set the orientation of a form using VBA.
The information as to which orientation is contained in a table, which is filtered to choose the correct report. See my previous post:
Formating Text box in Report from a Form
I have found on MSDN that you can set the Printer Object for Portrait or Landscape, but Orientation for a Report or Form referes to Right to Left and Left To Right, for Arabic for instance I guess.
So how in VBA do you set the Page Orientation?
I am thinking that perhaps this choice would hav to be done Before opening the report!
Can someone confirm that this is the only option?

 

[thudson]

I have tried putting the checking on the button that Opens the report, but it says it can't find the Form. But it is being called from that form, so why can't it find it?
Here is the code:

Private Sub Cmd_Open_Rep_Click()
Dim strWhere As String                  'The criteria string.
If Forms!["Frm_Card_Params"]![Ort] = "Portrait" Then
DoCmd.OpenReport "Rep_Port_Grt", acViewPreview, , , , Me.Filter ', strWhere
Else
DoCmd.OpenReport "Rep_Land_Grt", acViewPreview, , , , Me.Filter ', strWhere
End If
End Sub

 

[sneuberg]

I just tried putting

Me.Orientation = 1 'Right to Left 0 is Left to Right

in a reports Form Load and it says "To set this property open the form or report in design view" so it seem you are right about have to do this before running the report. You will have to open in design view, change the orientation, save and close it and then run the report

So far as not finding the form, I suggest trying

If Me.Ort = "Portrait" Then

The intellisense should show Ort in the drop down after you type Me.

 

[thudson]

Thanks that worked!
Now I have got the difficult bit!
I have a database with some verses for greetings cards I have worked out how to set up the report and save it, then choose the verse using a function that I have put in a query, that uses whatever has been choosen in the combo box.
That lists the verses I have filtered and there is a tick box to allow a choice to be made.
When chosen, a button is pressed that runs the following Event Procedure:

Private Sub Cmd_Chk_Ticked_Click()
Dim strWhere As String                  'The criteria string.
If Forms![Frm_Card_Params]![Ort] = "Portrait" Then
DoCmd.OpenReport "Rep_Port_Grt", acViewPreview, , , , Me.Filter ', strWhere

Else
DoCmd.OpenReport "Rep_Land_Grt", acViewPreview, , , , Me.Filter ', strWhere

End If

End Sub

However it is saying that Forms![Frm_Card_Params]![Ort] is not available in design view!
What is it refering to as the button is on a Form and it has not opened a Report.
Is there a better way of checking which report is required?
If this is not clearenough I will attach a copy of the database, if requested to do so.

 

[sneuberg]

I suggest unloading a copy of the database.

 

[thudson]

Hi Sneuberg
Open Frm_Card_Params first and choose card size.
Click on filter button to display parameters for that card.
Then click button below the details.
Here's where it starts to go wrong.
The verse Event and Mood combo's are displayed in a new window, but the report also opens.
I want only the combo buttons to be displayed and subsequently the list of verses. Then I want to choose a verse and put the text into the report I have set up in the first step.
I have also noticed that my choices are not being cleared in the Verses table.
I would be much obliged if you could advise what I need to do to straighten these problems out.

 

[sneuberg]

Thanks for uploading the database. It make things easier and let's ignore any of my previous posts and start from scratch.

Before you tackle this page orientation issue I'm going to suggest you change your design and consolidate the three forms into one form. The user would select the Event, Mood, and Card Size from combo boxes and then click a button to view the verses. Perhaps you can start with Frm_Flt_Verses or a copy of it and add the combo boxes to it.

I suggest the combo boxes for Events and Moods show only the Events and Moods for which there are verses rather than everything in their respective tables. With the Events combo as an example you can accomplish this by getting the distinct EventIDs from the Verses table (SELECT DISTINCT EventID FROM Verses) save that query and join it with the Events Table to make a row source query for the Events combo. Repeat this for the Mood combo.

In the after update of the Event and Mood combos you can check if the other combo has a selection and if both do then enable the button to query the verses or just run the query from the after updates.

I think you will also want to add code to ensure only one verse is selected. You can do this in the on click event of the option button. If the option button has been set to true you just update all the others to false.

Anyway with everything in one place you'll have a fighting chance at getting the report to work. As you have it now you are getting the error about Forms![Frm_Card_Params]![Ort] not being available because the form is not open. If you have everything open and selected that problem disappears and one about invalid use of null in the report crops up. Something about the filter you are trying to pass. You can fight that one later once things are consolidated.

 

[thudson]

Hi Sneuberg

I have amalgamated the forms and the filtering works OK.
But there are 2 problems.
1. I am unable to select a verse with the radio button and looking through the table all the past selections are still selected.
2. When I click on the button to create a Report nothing is printed in the report. Is this because the selection part is not working?

Here is a revised copy of the database.

 

[sneuberg]

Concerning "1. I am unable to select a verse with the radio button". I have some suggested changes that would clear up that problem. Basically getting the card data table out of the record source of the form and moving that data to the combo box for the card data.:

I find it’s always good to make a backup of my projects now and then. I suggest you make one now in case you have to backtrack.

In the Record Source for Q_4Cmb_Verses form (the Q_4Cmb_Verses query) remove the Crd Data table. It’s making the query non updatable
In the Cbo_Card_Size combo properties change:

Row Source Type to Table/Query
Row Source to Crd_Data
Default Value “01”
Column Count 7
Column Widths 0";1";0";0";0";0";0";0" or how you want them

Change the control sources of the card data text boxes to the Cbo_Card_Size for example the control source for CardID would be =[Cbo_Card_Size].[Column](0) , Card_Sz would be =[Cbo_Card_Size].[Column](1)

In the cmdFilter_Click of the Frm_Verses_mstr remove the following code

 If Not IsNull(Me.Cbo_Card_Size) Then
        strWhere = strWhere & "([Card_Sz] = """ & Me.Cbo_Card_Size & """) AND "
    End If

In Cmd_Open_Rep_Click of the Frm_Verses_mstr change

If Forms![Frm_Verses_mstr]![Ort] = "Portrait" Then

to

If Me.Cbo_Card_Size.Column(6) = "Portrait" Then

On Frm_Verses_mstr change the text box name Text51 to Wth_Sz

If you find these instruction confusing you can look in the attached database for clarification where I've made these changes. This will get the report open but still with nothing in it. I'll look into that as I get time.

 

[thudson]

Thank you for what you have done!
I will look at your modifications tomorrow.
I am learning a lot from your inputs.
Thank you again!

 

[jdraw]

I found a few links that may be useful (untested).

https://msdn.microsoft.com/en-us/library/office/aa173177(v=office.11).aspx
https://msdn.microsoft.com/en-us/library/bb258178.aspx
http://www.pcreview.co.uk/threads/setting-orientation-in-vba.2622008/
http://www.techrepublic.com/forums/questions/printing-an-access-form-in-landscape-mode/

Good luck. Let us know if you get something working.

 

[sneuberg]

and looking through the table all the past selections are still selected.

To keep the Tick_Box set to only one verse requires code and the addition of the VerseID to Frm_Verses_mstr form, detail section as a hidden textbox. It really doesn’t have to be hidden, but you probably don’t want to see it. You need this hidden textbox in the code so that the code doesn’t reset the tick box being clicked. After create the hidden textbox create a click event for the Tick_Box option button and make it look like.

Private Sub Tick_Box_Click()

DoCmd.RunCommand acCmdSaveRecord
‘set all the tick boxes to no except for the one being clicked
CurrentDb.Execute "UPDATE Verses SET Tick_Box = 0 WHERE VerseID <> " & Me.VerseID


End Sub

This will at least make sure no more than one is selected. You probably need more than this as you probably want to ensure at least one is selected, but this should get you started.

 

[sneuberg]

Before I forget to ask is this going to be a multiuser system? If it is you will have to do this differently, e.g., two users can't not simultaneously share the use of the Verses table or they will be stumping on each others selections.

If it is multiuser then it would be best to design it for that now.

 

[thudson]

It will be a single user system.
I have a web version that I am trying to emulate as a single user version, so that users can add their own verses.
If you want to see how I have done this on the web > http://www.1066cards4u.co.uk

I have modified as suggested and it works well!
Just 2 problems to sort out:
1. When the Filter reset button is clicked I get the following error:-
Runtime error 2448
You can't assign a value to this object
The code in error:-
Ctl.Value = Null

2. How do you get the verse text to print in the report?

 

[sneuberg]

If you've made the changes I suggestion then you will also want to

1. In Cmd_Open_Rep_Click of the Frm_Verses_mstr the Filter is in wrong place in report open. Th code should be changed to

If Me.Cbo_Card_Size.Column(6) = "Portrait" Then
    DoCmd.OpenReport "Rep_Port_Grt", acViewPreview, , Me.Filter
Else
    DoCmd.OpenReport "Rep_Land_Grt", acViewPreview, , Me.Filter
End If

2. Record Source in the Reports should be Q_4Cmb_Verses not Crd_Data

3. The Control Source for Text1 in the Reports should be Verse

This will get a verse displayed in the report.

 

[sneuberg]

With the changes I suggested in my last post the verse displayed in the reports is the first one in the subset of the selected Mood and Event, not the selected verse. The filter past to the report should not be the filter of the form. It needs to filter the specific VerseID.

I'll get some suggested code to you later.

 

[sneuberg]

1. When the Filter reset button is clicked I get the following error:-
Runtime error 2448
You can't assign a value to this object
The code in error:-
Ctl.Value = Null

Sorry I didn't see this. I've attached the database with the changes I've made to this point. Maybe you can compare this to what you have to see what's wrong.

 

[sneuberg]

Here's code for the Frm_Verses_mstr form that will single out the specific verse. If you only need the verse in the report and not the mood or event you could simplify this by make the Record Source of the report just the Verses table and then just filter the VerseID.

Private Sub Cmd_Open_Rep_Click()

Dim SelectedVerseID As Long
SelectedVerseID = Nz(DLookup("VerseID", "Verses", "Tick_Box = True"))
If Me.Cbo_Card_Size.Column(6) = "Portrait" Then
    DoCmd.OpenReport "Rep_Port_Grt", acViewPreview, , Me.Filter & " AND VerseID = " & SelectedVerseID
Else
    DoCmd.OpenReport "Rep_Land_Grt", acViewPreview, , Me.Filter & " AND VerseID = " & SelectedVerseID
End If  

End Sub

I've attached the database with this code. I'll leave you alone now, but I you need any further help let me know.

 

[thudson]

Right! Just the issue of why the reset filter not working.
Only started to be a problem since the recent code changes.
My problem due to my lack of experience is to enterparate what the errors implications are!
Also, I want to allow the user to select the font and size.
Cant I do this by allowing the Font controls to be shown, when I build a Runtime version?
Any articles or posts on this would be helpful.

 

[sneuberg]

Ignore this. See later post

Right! Just the issue of why the reset filter not working.

I don't understand what you mean by the reset filter. Would you upload your database and tell me what steps you are going through and what result you are expecting.