accvbalearner
Registered User.
- Local time
- Today, 16:55
- Joined
- Jan 3, 2013
- Messages
- 42
Hello All,
Need assistance or an idea on how to proceed.
Here is my issue:
I have a report in Access that prints four columns of time along with names as such. Its a timesheet, some names are big, some are small. In short I have added the code below to the Detail Section On Print Event so that the textbox values are evaluated and the text is size adjusted as necessary to fit in the text box.
Now I have four columns of time that the code is evaluating as if it is a long date. In the source query I stripped the date from the time by subtracting the work date from the timestamp, but Access still evaluates as if it is 'Saturday, December 30, 1899 07:30 AM' and makes the text in the boxes super tiny.
Any ideas on how I can adjust the code to either bypass those time textboxes in the detail section or how I can make access see the time as only a time with none of the date information with it?
The textbox is currently formatted as Medium Time. I've tried the Format([TIME],"hh:nn") but that just changed it to Short Time and it's still tiny. I did the format in both the Report and Source query, same result.
Any assistance would be greatly appreciated. As it is now, I've removed the Event Procedure from the Property and I keep adding it back when its needed by other users.
Thanks in advance for any help or ideas that can be provided.
Take Care,
accvbalearner
Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
Dim ctl As Control, strText As Variant, strName As String
' This routine uses the TextWidth methods to determine the maximum size
' of font possible to ensure a text string is printed in full in the
' report's current font without loosing any characters.
Me.ScaleMode = 1 ' set all measurments to twips
For Each ctl In Me.Detail.Controls
If ctl.ControlType = acTextBox Then
strName = ctl.Name
If Nz(ctl.Tag, "") = "" Then
ctl.Tag = ctl.FontSize
End If
' set the control's fontsize to a suitable large size to begin with
ctl.FontSize = ctl.Tag
' make sure the report font size is equal to the control's fontsize.
Me.FontSize = ctl.FontSize
' grab the text from the control
strText = ctl.Value
' evaluate the Loop until the text fits the Width of the box less 24%. Do this
' by reducing the font size incrementally and re-testing the Loop's criteria.
If Len(strText) > 0 Then
Do Until TextWidth(strText) < ctl.Width '- (ctl.Width * 0.26)
ctl.FontSize = ctl.FontSize - 1
' reset the report's font size so the TextWidth function will
' continue to track the reducing font size correctly.
Me.FontSize = ctl.FontSize
Loop
' now evaluate for the height of the text to make sure it fits vertically
Do Until TextHeight(strText) < ctl.Height - (ctl.Height * 0.26)
ctl.FontSize = ctl.FontSize - 1
' reset the report's font size so the TextHeight function will
' continue to track the reducing font size correctly.
Me.FontSize = ctl.FontSize
Loop
End If
End If
Next ctl
End Sub
Need assistance or an idea on how to proceed.
Here is my issue:
I have a report in Access that prints four columns of time along with names as such. Its a timesheet, some names are big, some are small. In short I have added the code below to the Detail Section On Print Event so that the textbox values are evaluated and the text is size adjusted as necessary to fit in the text box.
Now I have four columns of time that the code is evaluating as if it is a long date. In the source query I stripped the date from the time by subtracting the work date from the timestamp, but Access still evaluates as if it is 'Saturday, December 30, 1899 07:30 AM' and makes the text in the boxes super tiny.
Any ideas on how I can adjust the code to either bypass those time textboxes in the detail section or how I can make access see the time as only a time with none of the date information with it?
The textbox is currently formatted as Medium Time. I've tried the Format([TIME],"hh:nn") but that just changed it to Short Time and it's still tiny. I did the format in both the Report and Source query, same result.
Any assistance would be greatly appreciated. As it is now, I've removed the Event Procedure from the Property and I keep adding it back when its needed by other users.
Thanks in advance for any help or ideas that can be provided.
Take Care,
accvbalearner
Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
Dim ctl As Control, strText As Variant, strName As String
' This routine uses the TextWidth methods to determine the maximum size
' of font possible to ensure a text string is printed in full in the
' report's current font without loosing any characters.
Me.ScaleMode = 1 ' set all measurments to twips
For Each ctl In Me.Detail.Controls
If ctl.ControlType = acTextBox Then
strName = ctl.Name
If Nz(ctl.Tag, "") = "" Then
ctl.Tag = ctl.FontSize
End If
' set the control's fontsize to a suitable large size to begin with
ctl.FontSize = ctl.Tag
' make sure the report font size is equal to the control's fontsize.
Me.FontSize = ctl.FontSize
' grab the text from the control
strText = ctl.Value
' evaluate the Loop until the text fits the Width of the box less 24%. Do this
' by reducing the font size incrementally and re-testing the Loop's criteria.
If Len(strText) > 0 Then
Do Until TextWidth(strText) < ctl.Width '- (ctl.Width * 0.26)
ctl.FontSize = ctl.FontSize - 1
' reset the report's font size so the TextWidth function will
' continue to track the reducing font size correctly.
Me.FontSize = ctl.FontSize
Loop
' now evaluate for the height of the text to make sure it fits vertically
Do Until TextHeight(strText) < ctl.Height - (ctl.Height * 0.26)
ctl.FontSize = ctl.FontSize - 1
' reset the report's font size so the TextHeight function will
' continue to track the reducing font size correctly.
Me.FontSize = ctl.FontSize
Loop
End If
End If
Next ctl
End Sub