how to stretch text in a fixed textbox in ms access report?

Assuming a text box [txtUserName] on a report with a rectangle [boxUserName] that you want to fill with the letters from txtUserName. The code in the On Format event would be:

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

    Dim intChar As Integer       'current character in string 

    Dim intChars As Integer      'number of total characters 

    Dim intTotalWidth As Integer 'width of rectangle to fill 

    Dim intCharWidth As Integer  'character spacing 

    Dim strUserName As String    'text value to spread 

    intChars = Len(Me.txtUserName) 

    intTotalWidth = Me.boxUserName.Width 

    intCharWidth = intTotalWidth / intChars 

    strUserName = Me.txtUserName 

    Me.CurrentY = Me.boxUserName.Top 

    For intChar = 1 To intChars 

        Me.CurrentY = Me.boxUserName.Top 

        Me.CurrentX = Me.boxUserName.Left + intCharWidth * (intChar - 1) + intCharWidth / 2

        Me.Print Mid(strUserName, intChar, 1) 

    Next 

End Sub The results would look like:

The boxUserName has the red outline. You may need to do some tweaking of the code to get a better positioning, font size, etc.

I expect you could do this with multiple text boxes based on the number of length of the text. For instance, if the longest value was 16 characters, use 16 text boxes. Fill each text box from left to right and then use code to set their Left property to spread them evenly in your space.

The other solution is to use Me.Print in the On Format event of the section. Determine the length and use a loop to print individual characters adjusting the CurrentX property. In this scenario, the original text box would not show.

You can center the text, of course, but Access reports don't natively support stretching text out to fit a text box control.

• Can you provide a screenshot of the report showing how much space you have to fill in this control and how much text is actually in it?
• Also, what problem are you trying to solve by doing this?

It's probably possible to insert spaces between the letters in a given string of text in the control,using the Space function. However, that would require a VBA function that runs on every field in every record where you want to achieve this. It's doable, but might be pretty slow.