Changing fonts in pre-defined reports

[barit]

Is it possible to have the end-user select/change the font style, font size etc. used in a report when the end-user can only preview or print selected reports?

I have a series of predefined and formatted reports. The end-user chooses the report they desire and then can both preview and print the report.(Very standard stuff) I would like to have the user be able to select font style, bold, italics, etc once they have previewed the report.Is this possible?

Using a Windows API and following detailed instruction of others, I have been able to call up the windows font dialog box from a command button on a form. I cannot however, get the selection from the font dialog to apply to a pre-determined report.

If it is possible to do this, can someone point me in an appropriate direction to resolve this dilemma.

Thanks. Its greatly appreciated

 

[dhookom]

You can write code in the On Open event of the report that
1) loops through all controls on the report
2) sets the fontname property of each control to the user selected font
3) ignores any errors since many controls don't have a fontname property

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[barit]

Hi Duane;

Thanks for the response. This is the second time you have graciously responded to my requests for help and have offered advice to me. It is greatly appreciated.

Anyways..If you will bear with me me I will try to execute your suggestions.

Part 1 - Loop through the controls on the report. Am I looking for code like the following in the On Open event of my report

Dim ctl as Control
For Each ctl In Me.ReportName
Select Case ctl.ControlType
Case acTextBox, acComboBox, acLabel
ctl = ???
End Select
Next

If this is what I am looking for what would I put ctl = to

The following is the code in my command button that on click calls the windows font dialog. Where you see "With cboEventData" this is the name of the field on the form that I am using to test if the font dialog works.

Dim cdl As CommonDlg
Set cdl = New CommonDlg

cdl.hWndOwner = Me.hWnd

With cboEventData
cdl.FontName = .FontName
cdl.FontItalic = .FontItalic
cdl.FontSize = .FontSize
cdl.FontUnderline = .FontUnderline
cdl.FontWeight = .FontWeight
cdl.FontColor = .ForeColor
End With
cdl.Min = 8
cdl.Max = 24

cdl.hWndOwner = Application.hWndAccessApp
cdl.ShowFont

With cboEventData
.FontName = cdl.FontName
.FontItalic = cdl.FontItalic
.FontSize = cdl.FontSize
.FontUnderline = cdl.FontUnderline
.FontWeight = cdl.FontWeight
.ForeColor = cdl.FontColor
End With
Set cdl = Nothing

How would I relate ctl= in my on open event procedure to cdl FontName,FontItalic,FontSize,FontUnderline etc.?

I really would appreciate your help with this. As you can probably tell, I am in over my head but am determined to make this work.

Thanks for taking the time to respond.

Barit

 

[dhookom]

Personally, I wouldn't provide this functionality for any of my users. I can't think of any situation where this would add any value to an application considering the amount of work it takes. (just my opinion)

Dim ctl as Control 
For Each ctl In Me.Controls
 Select Case ctl.ControlType
 Case acTextBox, acComboBox, acLabel
    ctl.FontName = Forms!frmYourForm!cboEventData
 End Select
Next

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[barit]

Thanks for the quick response. I respect your opinion on providing this functionality and on the whole agree. There are however, selected instances such as mailing labels, and a few reports that will be distributed to others where this functionality and ability to personalize is important.

I tried the code you provided, unfortunately it is not working. The report in preview mode still looks the same. No font change. Even if I go into cboEventData and change the Font Name, it is not being reflected in the preview of the report.

What am I doing wrong?

 

[dhookom]

I'm surprised it doesn't work for you. I created a form named frmYourForm and added a control cboEventData with a value of "Arial". I then created a report with a font of Algerian. I copied the exact code from above into the On Open event of the report and when the report displays, I see Arial, not Algerian.

Do you realize that you can't change the font of an open report?

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[barit]

One last try.

When you said in your last message you added a control cboEventData with a value of "Arial" what exactly do you mean. Is the control source set ="Arial"?

How the control is set up on my form is the only potential difference I can think of.

The report I am trying to change the font on is not open. The form from which I am using the dialog and command buttons is setting the criteria for the report and then once selected a command button is used to open the form.

Thanks for continuing to help me try to make this work

 

[dhookom]

Add a control bound to your form control to see the value of the control
=Forms!frmYourFOrm!cboEventData
I assume by your second to the last statement you actually mean "open the report"

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[barit]

No matter what I try i cannot get this to work.

On my form I have the following:

- an unbound combo box called cboEventData that is used to identify a parameter for my report.
-I have a text box "Text40" that is hidden. Text40 has as its control source the results of the selected choice from cboEventData. =([Forms]![LabelsDialogue]![cboEventData].[Column](4). My report query uses Text40 to select the report results.
-a Command Button called "PreviewLabels" used to open the "Labels" Report
-a Command Button called "ChangeFont" used to open the Windows Font Dialog, allow selection of the font and to set the font value etc.

As noted previously, I have code behind the Command Button "ChangeFont" set to change the font of the combobox cboEventData. This it does perfectly.

I have put the code you suggested in the OnOpen event of my report "Labels", but it will not show the changed font that is shown in cboEventData.

I have tried changing the With statement in my code to change the font of Text40 since the results of this hidden text box are reflected in the criteria statement of my report query. It does not make a difference.

I am very confused as to what to try next. Any suggestions?
Thanks for bearing with me on this.

 

[dhookom]

Unhide Text40 (I would also take the time to change its name) to make sure a valid font name is in the box. Then open the report in preview. If this doesn't work, what value is displayed in your former Text40 and what is the code in your report.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[barit]

I have unhidden Text40 and renamed it "SpecificEvent". SpecificEvent does not show a font name. It displays the results of the Select statement used to create the records in the drop down list in cboEventData.In the properties section of this control the FontName is Tahoma

The Select statement for cboEventData is as follows:

SELECT [Occassion] & "         " & [EventActivity] & "       " & [Event Date] AS EventData, OccassionType.Occassion, EventActivity.EventActivity, [EventInfo].[EventDate], [EventInfo].[EventID]
FROM OccassionType RIGHT JOIN (EventActivity RIGHT JOIN [EventInfo] ON EventActivity.EventActivityID = [EventInfo].[EventActivityID]) ON OccassionType.OccassionID = [EventInfo].OccassionID
ORDER BY [Event Info].[Event Date] DESC;

As noted previously, the control source for SpecificEvent is =([Forms]![LabelsDialog]![cboEventData].[Column](4).

The Select statement upon which the report "Labels" is based is as follows:

SELECT DISTINCTROW MailListName.MailingListName1, PersonEvent.[Event ID], Person.MailingListNameID AS CountOfMailingListName, Person.LastName, Person.FirstName, PersonEvent.LabelRequired, Person.Address1Street, Person.Address1City, Person.[Address1Province/State], Person.Address1Country, Person.[Address1Postal/Zip Code]
FROM (MailListName RIGHT JOIN Person ON MailListName.MailingListNameID = Person.MailingListNameID) LEFT JOIN PersonEvent ON Person.[Person ID] = PersonEvent.[Person ID]
WHERE (((PersonEvent.[Event ID])=[Forms]![LabelsDialog]![SpecificEvent]) AND ((PersonEvent.LabelRequired)=Yes))
ORDER BY Person.LastName, Person.FirstName

My Command Button "ChangeFont" is currently set to change the font of cboEventData which it does. However, the changed font is not reflected on the report "labels". I have retried the process changing the With statement in Command Button "ChangeFont" to change the font of "SpecificEvent". This part works fine.

I then changed the on open statement of the report to

Dim ctl As Control
For Each ctl In Me.Controls
Select Case ctl.ControlType
Case acTextBox, acComboBox, acLabel
ctl.FontName = [Forms]![LabelsDialog]![SpecificEvent]

No luck

For some reason the report is not picking up the change in font.

Can the With statement in Command Button "ChangeFont" be changed to identify the Report"Labels" controls directly?

Thanks again

 

[barit]

Duane;

Just wanted to let you know I finally got this to work using the following code.

Dim ctl As Control
For Each ctl In Me.Controls
Select Case ctl.ControlType
Case acTextBox, acComboBox, acLabel
ctl.FontName = [Forms]![LabelsDialog]!SpecificEvent.FontName

I appeared to just be missing .FontName after the form field name.

Once again, thank you so much for your patience and assistance. I truly appreciate the time and effort you have spent attempting to solve my problem.

Barit