Is it possible to make "Can Grow" work Horizontally? 1

[PaulChilds]

Hi,

In my report I have three columns of text boxes:

1|Song title|
2|...............|
3|Book Number|

SO when it prints out it looks something like:

The Long and winding road .................. 220

'Title' has white background as does the 'number' field and the dots span the whole length of the column underneath the other two.

Is there any way to make the 'title' (which is left justified) and the 'number' (right) fields shrink and grow horizontally so that the dots always start immediatelly 'after' the song title and before the number?

Did I explain this clearly enough?

ANyway, let me know what you think.

Cheer - Paul C

http://www.worship-leader.co.uk

http://www.liverpooldream.co.uk

http://www.ormskirkparish.co.uk

http://www.cadetsltd.co.uk

 

[PaulChilds]

Before anyone suggests using a concatenate function, I forgot to add that I need the song to be justified to the far left of the column and the number justifed to the far right - and using distribute looks too messy.

Thanks.

PC

http://www.worship-leader.co.uk

http://www.liverpooldream.co.uk

http://www.ormskirkparish.co.uk

http://www.cadetsltd.co.uk

 

[JaneC]

Paul,

the only way I can think of is to create the following function (where 50 is the total length of the songname + ... + book number)

Function padsong(SN, BN)
Dim a As Integer
Dim b As Integer
Dim c As Integer
Dim d As Integer

a = Len(Trim(SN))
b = Len(Trim(BN))
c = 50 - a - b
padsong = SN
d = 0
While d < c
padsong = padsong & &quot;.&quot;
d = d + 1
Wend
padsong = padsong & BN

End Function

On the report add an unbound text box, in the control source put = padsong([songname],[booknumber])

Remember to make the text box wide enough.

NB This will only have the desired effect if you use a fixed font (such as courier) and not a variable one !!

Hope this helps

Jane

 

[PaulChilds]

Thanks for the reply - one question:-

Where do I need to put the function?

Thanks,

PC

http://www.worship-leader.co.uk

http://www.liverpooldream.co.uk

http://www.ormskirkparish.co.uk

http://www.cadetsltd.co.uk

 

[JaneC]

It needs to go in a module. start a new module, copy and paste my code from &quot;Function&quot; to &quot;End function&quot; then save the module - doesn't matter what you call the module , I usually call it Utils.

J

 

[PaulChilds]

Thanks. I wasn't sure about that!

http://www.worship-leader.co.uk

http://www.liverpooldream.co.uk

http://www.ormskirkparish.co.uk

http://www.cadetsltd.co.uk

 

[PaulChilds]

Thanks for that - It works pretty good - and using distribute to justify it doen't look bad at all now!

A star for you!

http://www.worship-leader.co.uk

http://www.liverpooldream.co.uk

http://www.ormskirkparish.co.uk

http://www.cadetsltd.co.uk

 

[scriverb]

Here is another approach. Create three controls on your report.
1. Create a Label Control and stretch it the width of the report from where you want the Song Title to start all the way over to where the Book Number should end. Fill it with periods(.) all the from left to right.
2. Create a Text Control for Song Title. Place it at the far left on top of the label created in #1. It should be titled Song_Title and the background should be white with Courier font at 8 pt. Resize down to the far left so that it is one character width in size.
3. Create a Text Control for Book Number. Place it at the far right on top of the label created in #1. It should be titled Book_Number and the background should be white with Courier font at 8pt. Resize down to the far right so that it is just a sliver with 0 width and place in on top of the label with the periods at the same horizontal location as the right edge of that label.

What we are going do with the following code is resize and place the two text boxes for each detail line. The underlying label with the periods will only be displayed between the two text boxes. The size of the two textboxes will be controled by this VBA code in the OnFormat event procedure of the Detail Section of the report.

Me.Song_Title.Width = (Len(Me.Song_Title) * 0.07) * 1440
Me.Book_Number.Left = (5 * 1440) - ((Len(Me.Book_Number) * 0.07) * 1440) - 100
Me.Book_Number.Width = (Len(Me.Book_Number) * 0.07) * 1440

You will have to adjust this code slightly to your report layout situation.

The width, left, height properties of a control are displayed in the properties window as inches but they can only be adjusted by assigning TWIPS. 1440 twips to the inch. This constant stays the same.

See the red 5? This has to be changed to reflect the exact location of the Book_Number controls left position in the report. Remember this control should be designed with 0 width and look like a vertical line and be placed at the far right matching edge of the label with the periods. Mine was located at the 5&quot; mark. So, if yours is at 6.5 then replace the 5 in the code with 6.5. Now we have a new constant which is the right hand edge of the Book_Number control.

See the red 0.07? This is a constant that reflects the width of one Courier New character. I had to test this out a bit to get it right and if you have selected another Fixed Font you will have to possibly modify this number. This is the Font Width Constant.

The red 100 is an adjustment constant. Since the Book_Number control is a right justified control there is a slight amount of dead space to the left of the number. This seems to work in eliminating the blank space and have the periods bump right up against the number. May have to be adjusted slightly.

Finally, if you choose to use a different font or font size then the adjustable constants will have to be adjusted. But, with this little bit of code and the setup of the controls will work for your entire database.

Good luck. Let me know if you like this approach.


Bob Scriver

Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[scriverb]

Change the 0.07 to 0.075. Just a slight adjustment in the font character width.

Let me know how this works with your database.

Bob Scriver

Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[MichaelRed]

and ... just for the 'fun' of it ...

This (somehow) requires three controls - two hidden ones with the fields from the Reports' Recordsource which are the left and right justified test items (below these are txtHolidate and txtHoliName respectrively) their placement and size are irrelevant, so I just place them on the report, set their visisble property to &quot;No&quot; and then place the third control over them. Sixe the third control to the width desired (and you may arbitrarily adjust it later). Include the following procedure - not that it is in hte OnFormat Event of the section of the report with the control. The last line sets the sort-of text into the control, so it should be unbound.


Of course, the whole thinngggyyyyy is just a snippet w/out error trapping or checking and is set up to ONLY work with the specific literally named controls and fill character, so it is NOT intended for use in a production app, but just as an illustration of a somewhat different technique.

And, oh by the way, you can use any olde fonte and feel free to change it as you please.

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

    Dim LTxtWdth As Long
    Dim rTxtWdth As Long
    Dim CtrlWdth As Long
    Dim FillWdth As Long
    Dim MyFillWdth As Long
    Dim MyTxt As String

    LTxtWdth = Me.TextWidth(txtHoliDate)
    rTxtWdth = Me.TextWidth(txtHoliName)
    FillWdth = Me.TextWidth(".")

    CtrlWdth = Me.txtHoliday.Width
    MyFillWdth = CtrlWdth - (LTxtWdth + rTxtWdth)
    Me.txtHoliday = [HoliDate] & String(MyFillWdth / FillWdth, ".") & [HoliName]

End Sub
[code]





MichaelRed
m.red@att.net

Searching for employment in all the wrong places