Text Box columns

I have a need to display a variable in a text box but tabulate the elements into a series of columns

I tried this by padding out each element with spaces (see code below)
    lngSpaces = 50 - Len(Trim(jdwe_Desc))
    strEventSummary = " " & jdwe_Desc & Space(lngSpaces) & _
    Format(jdwe_StartDate, "dd-mmm-yy") & _
    Space(13 - Len(Trim(Format(jdwe_StartDate, "dd-mmm-yy")))) & _
    Space(3 - Len(Trim(jdwe_NoPlaces))) & jdwe_NoPlaces & _
    Space(9 - Len(Trim(jdwe_NoBookings))) & jdwe_NoBookings & _
    Space(9 - Len(Trim(jdwe_NoConfirms))) & jdwe_NoConfirms & _
    Space(9 - Len(Trim(jdwe_NoParticipants))) & jdwe_NoParticipants & _
    Space(9 - Len(Trim(jdwe_Reserved))) & jdwe_Reserved

The problem isn't with proportional fonts - I've tried Courier (horrible font but proportional) and the spaces between just do not move the StartDate to the same position.

What I am trying to do is line-up each column with a fixed header label like so:

Event Description                                                        Date Starts  Plcs  Bkgs  Conf  Part  Resv
Singing and Playing, Playing and Singing                        28-Jan-11    16      6       6      6        0

Then when the next event is displayed we should see:

Event Description                                                        Date Starts  Plcs  Bkgs  Conf  Part  Resv
Oboe workshop                                                            28-Jan-11    16      6       6      6        0

but what we get is:

Event Description                                                        Date Starts  Plcs  Bkgs  Conf  Part  Resv
Oboe workshop                            28-Jan-11    16      6       6      6        0

Thanks in advance ... somebody

Who is Participating?
Try using a function:

Function PadTo(vText as Variant, intCol as Integer) As String
    PadTo = Left(vText + Space(intCol), intCol)
End Function

Open in new window

then build your string field by field (allows viewing of the intermediate parts)

strEventSummary = PadTo(" " & jdwe_Desc, 50)
'Format date before passing
strEventSummary = PadTo(Format(jdwe_StartDate, "dd-mmm-yy"), 13)
strEventSummary = PadTo(jdwe_NoPlaces, 3)
. . .

Dale FyeCommented:
Is this for a form, report, or what?

Why is this data in a varaible, and not a recordset?

Why don't you just break this into its component parts and display each element in its own control?  Then you can guarantee that they line up appropriately.

Another method would be to use a listbox, but they generally align everything as text, so you would have to pad them in that as well.
MikeDTEAuthor Commented:
It's a form

It's a variable because many procedures affect the data in the string:

A first payment made on a booking increases the NoConfirms
A new booking increases the NoBookings
A cancellation decreases the NoConfirms and NoBookings

etc. etc.

So the variable is Global and is used on many of the forms to give a quick indication to the user about the status of the event.

I originally had the summary in a list box but it wasn't as easy to manipulate or display as a text field.  I could split up the single text boxes into one for each element and then position them next to each other and that is what I will do if I can't find another way.

MikeDTEAuthor Commented:

I have no got this working with a non-propotional font but it does look a bit out of place when you compare the text with other fields on the forms.

I tried PadTo but the result is the same.  The issue is that proportional font spaces are very narrow even compared to an 'i' or 'l' which are the narrowest characters.  I cannot see any-way around using a non propotional font so I am going to split the present single text box into 7 smaller individual text boxes and stick them together and centre the text in each text box.

Thanks for all teh help - I'll divide up the points later today.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.