Column Width

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

In Access 2003, I have a report with 14 columns going across the top of each
page. There are 3 textboxes under 3 of the columns that can grow and shrink
because data there is of varying length (memo format). There is a horizontal
line under each row and in order to create column lines for each column, the
code I inputed is:
Private Sub Report_Page()
Dim intLineCount As Integer
Dim intColumnWidth As Integer
Dim intReportHeight As Integer
Dim intLineTop As Integer
intLineTop = 900
intReportHeight = (9 * 1150)
intColumnWidth = 1050
For intLineCount = 0 To 15
Me.Line (intLineCount * intColumnWidth, intLineTop)-Step (0, intReportHeight)
Next
Me.Line (0, intLineTop)-Step(15 * intColumnWidth, intReportHeight), , B
End Sub

Is there any way to make the column widths variable (i.e., the columns with
very little data will shrink and the columns with large amounts of data will
expand)?
Any help would be greatly appreciated.
 
I suppose this would be possible. You would need to grab the max width of
each column in the Report Header section. You could then use these values to
calculate the width of the controls in the detail section (and/or the column
labels).

I just did a small test with a table containing three text fields (Mon, Tue,
& Wed). I added several records with varying lengths of text. I created a
query for my reports record source.

SELECT tblMonTueWed.Mon, tblMonTueWed.Tue, tblMonTueWed.Wed,
Len([Mon]) AS MonLen, Len([Tue]) AS TueLen, Len([Wed]) AS WedLen
FROM tblMonTueWed;

I added three text boxes in the Report Header section:
Name: txtMax1
Control source: =Max([MonLen])

Name: txtMax2
Control source: =Max([TueLen])

Name: txtMax3
Control source: =Max([WedLen])

I added three text boxes in the Report Detail section:
Name: txt1
Control source: [Mon]

Name: txt2
Control source: [Tue]

Name: txt3
Control source: [Wed]

I then added code to the Format event of the Detail Section:

Private Sub Detail_Format(Cancel As Integer, _
FormatCount As Integer)
Dim lngRptWidth As Long
Dim intTotChars As Integer
Dim intCtlNum As Integer
Dim dblCharWidth As Double
Dim dblLeft As Double
lngRptWidth = Me.Width
'find the total number of characters needed across
For intCtlNum = 1 To 3
intTotChars = intTotChars + _
Me("txtMax" & intCtlNum)
Next
'find the width needed for one character
dblCharWidth = lngRptWidth / intTotChars
For intCtlNum = 1 To 3
Me("txt" & intCtlNum).Left = dblLeft
Me("txt" & intCtlNum).Width = _
Me("txtMax" & intCtlNum) * dblCharWidth
dblLeft = dblLeft + _
Me("txtMax" & intCtlNum) * dblCharWidth

Next
End Sub
 
Thanks so much for your help, but I am a little confused. (Bear with me
please, I'm relatively new to Access.) What does thhe paragraph you wrote
"SELECT tblMonTueWed.Mon, tblMonTueWed.Tue, tblMonTueWed.Wed,
Len([Mon]) AS MonLen, Len([Tue]) AS TueLen, Len([Wed]) AS WedLen
FROM tblMonTueWed" relate to (i.e., the query design)? Also, I have no
report header; would this affect your instructions?

I would appreciate your help.

Thanks,
Danielle


Duane Hookom said:
I suppose this would be possible. You would need to grab the max width of
each column in the Report Header section. You could then use these values to
calculate the width of the controls in the detail section (and/or the column
labels).

I just did a small test with a table containing three text fields (Mon, Tue,
& Wed). I added several records with varying lengths of text. I created a
query for my reports record source.

SELECT tblMonTueWed.Mon, tblMonTueWed.Tue, tblMonTueWed.Wed,
Len([Mon]) AS MonLen, Len([Tue]) AS TueLen, Len([Wed]) AS WedLen
FROM tblMonTueWed;

I added three text boxes in the Report Header section:
Name: txtMax1
Control source: =Max([MonLen])

Name: txtMax2
Control source: =Max([TueLen])

Name: txtMax3
Control source: =Max([WedLen])

I added three text boxes in the Report Detail section:
Name: txt1
Control source: [Mon]

Name: txt2
Control source: [Tue]

Name: txt3
Control source: [Wed]

I then added code to the Format event of the Detail Section:

Private Sub Detail_Format(Cancel As Integer, _
FormatCount As Integer)
Dim lngRptWidth As Long
Dim intTotChars As Integer
Dim intCtlNum As Integer
Dim dblCharWidth As Double
Dim dblLeft As Double
lngRptWidth = Me.Width
'find the total number of characters needed across
For intCtlNum = 1 To 3
intTotChars = intTotChars + _
Me("txtMax" & intCtlNum)
Next
'find the width needed for one character
dblCharWidth = lngRptWidth / intTotChars
For intCtlNum = 1 To 3
Me("txt" & intCtlNum).Left = dblLeft
Me("txt" & intCtlNum).Width = _
Me("txtMax" & intCtlNum) * dblCharWidth
dblLeft = dblLeft + _
Me("txtMax" & intCtlNum) * dblCharWidth

Next
End Sub


--
Duane Hookom
MS Access MVP
--

Danielle said:
In Access 2003, I have a report with 14 columns going across the top of
each
page. There are 3 textboxes under 3 of the columns that can grow and
shrink
because data there is of varying length (memo format). There is a
horizontal
line under each row and in order to create column lines for each column,
the
code I inputed is:
Private Sub Report_Page()
Dim intLineCount As Integer
Dim intColumnWidth As Integer
Dim intReportHeight As Integer
Dim intLineTop As Integer
intLineTop = 900
intReportHeight = (9 * 1150)
intColumnWidth = 1050
For intLineCount = 0 To 15
Me.Line (intLineCount * intColumnWidth, intLineTop)-Step (0,
intReportHeight)
Next
Me.Line (0, intLineTop)-Step(15 * intColumnWidth, intReportHeight), , B
End Sub

Is there any way to make the column widths variable (i.e., the columns
with
very little data will shrink and the columns with large amounts of data
will
expand)?
Any help would be greatly appreciated.
Click to expand...
Click to expand...
 
The "SELECT..." is the record source/query of the sample report that I
created.

You can and should create a report header.

--
Duane Hookom
MS Access MVP
--

Danielle said:
Thanks so much for your help, but I am a little confused. (Bear with me
please, I'm relatively new to Access.) What does thhe paragraph you wrote
"SELECT tblMonTueWed.Mon, tblMonTueWed.Tue, tblMonTueWed.Wed,
Len([Mon]) AS MonLen, Len([Tue]) AS TueLen, Len([Wed]) AS WedLen
FROM tblMonTueWed" relate to (i.e., the query design)? Also, I have no
report header; would this affect your instructions?

I would appreciate your help.

Thanks,
Danielle


Duane Hookom said:
I suppose this would be possible. You would need to grab the max width of
each column in the Report Header section. You could then use these values
to
calculate the width of the controls in the detail section (and/or the
column
labels).

I just did a small test with a table containing three text fields (Mon,
Tue,
& Wed). I added several records with varying lengths of text. I created a
query for my reports record source.

SELECT tblMonTueWed.Mon, tblMonTueWed.Tue, tblMonTueWed.Wed,
Len([Mon]) AS MonLen, Len([Tue]) AS TueLen, Len([Wed]) AS WedLen
FROM tblMonTueWed;

I added three text boxes in the Report Header section:
Name: txtMax1
Control source: =Max([MonLen])

Name: txtMax2
Control source: =Max([TueLen])

Name: txtMax3
Control source: =Max([WedLen])

I added three text boxes in the Report Detail section:
Name: txt1
Control source: [Mon]

Name: txt2
Control source: [Tue]

Name: txt3
Control source: [Wed]

I then added code to the Format event of the Detail Section:

Private Sub Detail_Format(Cancel As Integer, _
FormatCount As Integer)
Dim lngRptWidth As Long
Dim intTotChars As Integer
Dim intCtlNum As Integer
Dim dblCharWidth As Double
Dim dblLeft As Double
lngRptWidth = Me.Width
'find the total number of characters needed across
For intCtlNum = 1 To 3
intTotChars = intTotChars + _
Me("txtMax" & intCtlNum)
Next
'find the width needed for one character
dblCharWidth = lngRptWidth / intTotChars
For intCtlNum = 1 To 3
Me("txt" & intCtlNum).Left = dblLeft
Me("txt" & intCtlNum).Width = _
Me("txtMax" & intCtlNum) * dblCharWidth
dblLeft = dblLeft + _
Me("txtMax" & intCtlNum) * dblCharWidth

Next
End Sub


--
Duane Hookom
MS Access MVP
--

Danielle said:
In Access 2003, I have a report with 14 columns going across the top of
each
page. There are 3 textboxes under 3 of the columns that can grow and
shrink
because data there is of varying length (memo format). There is a
horizontal
line under each row and in order to create column lines for each
column,
the
code I inputed is:
Private Sub Report_Page()
Dim intLineCount As Integer
Dim intColumnWidth As Integer
Dim intReportHeight As Integer
Dim intLineTop As Integer
intLineTop = 900
intReportHeight = (9 * 1150)
intColumnWidth = 1050
For intLineCount = 0 To 15
Me.Line (intLineCount * intColumnWidth, intLineTop)-Step (0,
intReportHeight)
Next
Me.Line (0, intLineTop)-Step(15 * intColumnWidth, intReportHeight), , B
End Sub

Is there any way to make the column widths variable (i.e., the columns
with
very little data will shrink and the columns with large amounts of data
will
expand)?
Any help would be greatly appreciated.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top