Adding a unicode character to a string in vb

[ScubaBart]

Ok, this one should be simple and I really think I am following the right rules but it's not returning the desired results.

I am using Allen Browne's ConcatRelated() function to retrieve a list of inspected Hives in the Apiary and it does exactly that.

My problem is that sometimes the Hives may have a longer name and by default, the characters automatically wordwrap in the text box (is there a way to stop that from happening ?).

To clarify the list, I decided it should be a simple task to modify the string building sequence in the ConcatRelated function to add a bullet and a space at the beginning of each line of the assembled string.

So the basic premise of this actually worked but the bullet is not a bullet. I'm not very familiar with the Unicode system but I used the standard MS Character Map to determine the Unicode value for a bullet.

Turns out it is U+2022 and because it is part of the extended character set, I used the ChrW() function for this. As I said, it works, just not with the correct character. It returns something else as shown in the pic here. I picked the code for the bullet based on the font that is actually being used in the form (Calibri)



For additional reference, teh modified code for the ConcatRelated function is shown at the end of this post. I attempted to use some alternate characters but some Unicode values include hex code and vba doesn't like it if I use letters in the ChrW() function.

What am I doing wrong ?



Modified code (I added a comment where I made the modification, 2 places to work correctly)

Option Compare DatabaseOption Explicit


Public PrevForm As String  'Public Variable to reference the calling form when opening a new form
Public RefRecordID As Long  'Public Variable to reference the specific ID (Primary Key) of a record when moving to a new or previous form
Public ApiaryID As String    'Public variable to reference the currently active Apairy
Public HiveID As String     'Public variable to reference the currently active Hive
Public ApiaryFilter As Long  'Public Variable to reference any filter applied to the Apiary list when moving to a new or previous form
Public BeginFilterDate As Date 'Public Variable to reference the start date to filter records by date
Public EndFilterDate As Date   'Public Variable to reference the end date to filter records by date


Public Function ConcatRelated(strField As String, _
    strTable As String, _
    Optional strWhere As String, _
    Optional strOrderBy As String, _
    Optional strSeparator = ", ") As Variant
On Error GoTo Err_Handler
    'Purpose:   Generate a concatenated string of related records.
    'Return:    String variant, or Null if no matches.
    'Arguments: strField = name of field to get results from and concatenate.
    '           strTable = name of a table or query.
    '           strWhere = WHERE clause to choose the right values.
    '           strOrderBy = ORDER BY clause, for sorting the values.
    '           strSeparator = characters to use between the concatenated values.
    'Notes:     1. Use square brackets around field/table names with spaces or odd characters.
    '           2. strField can be a Multi-valued field (A2007 and later), but strOrderBy cannot.
    '           3. Nulls are omitted, zero-length strings (ZLSs) are returned as ZLSs.
    '           4. Returning more than 255 characters to a recordset triggers this Access bug:
    '               http://allenbrowne.com/bug-16.html
    Dim rs As DAO.Recordset         'Related records
    Dim rsMV As DAO.Recordset       'Multi-valued field recordset
    Dim strSql As String            'SQL statement
    Dim strOut As String            'Output string to concatenate to.
    Dim lngLen As Long              'Length of string.
    Dim bIsMultiValue As Boolean    'Flag if strField is a multi-valued field.
    
    'Initialize to Null
    ConcatRelated = Null
    
    'Build SQL string, and get the records.
    strSql = "SELECT " & strField & " FROM " & strTable
    If strWhere <> vbNullString Then
        strSql = strSql & " WHERE " & strWhere
    End If
    If strOrderBy <> vbNullString Then
        strSql = strSql & " ORDER BY " & strOrderBy
    End If
    Set rs = DBEngine(0)(0).OpenRecordset(strSql, dbOpenDynaset)
    'Determine if the requested field is multi-valued (Type is above 100.)
    bIsMultiValue = (rs(0).Type > 100)
    
    'Loop through the matching records
    Do While Not rs.EOF
        If bIsMultiValue Then
            'For multi-valued field, loop through the values
            Set rsMV = rs(0).Value
            Do While Not rsMV.EOF
                If Not IsNull(rsMV(0)) Then
                    strOut = strOut & ChrW(2022) & " " & rsMV(0) & strSeparator 'MODIFIED to add bullet
                End If
                rsMV.MoveNext
            Loop
            Set rsMV = Nothing
        ElseIf Not IsNull(rs(0)) Then
            strOut = strOut & ChrW(2022) & " " & rs(0) & strSeparator 'MODIFIED to add bullet
        End If
        rs.MoveNext
    Loop
    rs.Close
    
    'Return the string without the trailing separator.
    lngLen = Len(strOut) - Len(strSeparator)
    If lngLen > 0 Then
        ConcatRelated = Left(strOut, lngLen)
    End If


Exit_Handler:
    'Clean up
    Set rsMV = Nothing
    Set rs = Nothing
    Exit Function


Err_Handler:
    MsgBox "Error " & Err.Number & ": " & Err.Description, vbExclamation, "ConcatRelated()"
    Resume Exit_Handler
End Function

[June7]

AFAIK, only way to prevent wrapping is to make textbox wide enough to display entire line. How else would you expect data to display?

Try using ASCII code: Chr(149)
Works for me.

Might find this of interest:
https://www.excelforum.com/excel-pro...alt-codes.html
https://www.accessforums.net/showthread.php?t=90757

[CJ_London]

is there a way to stop that from happening

make the textbox wider, use a smaller font, use the left function to only take X number of characters. Use wizhook function to return the current length of the string (google to find out more about wizhook)

chrW takes a numeric parameter value, so text is not appropriate. See this link https://altcodeunicode.com/alt-codes...point-symbols/ - you'll want the Alt code (2nd column) - proabably 8226

[ScubaBart]

AFAIK, only way to prevent wrapping is to make textbox wide enough to display entire line. How else would you expect data to display?

Try using ASCII code: Chr(149)
Works for me.

I'm old enough to remember the basic MS text editor, Notepad, that allowed you to turn word wrap off. It's useful a times.

Chr(149) works. So, the basic Chr() function uses ASCII and not Unicode. Good to know.

Thanks !!

[ScubaBart]

......chrW takes a numeric parameter value, so text is not appropriate. See this link https://altcodeunicode.com/alt-codes...point-symbols/ - you'll want the Alt code (2nd column) - proabably 8226

Yes, the ChrW(8226) returns the same bullet as Chr(149).

Will have to research this a bit.

Thanks

[June7]

Notepad is still around and has that feature.

However, Access is not a text editor or word processing app.

As CJ suggested, can truncate string but would that be useful to you?

[davegri]

Try chr(149) instead of chrw(2022)

Immediate pane shows
?chr(0149) & " Bullet"
• Bullet

Edit: June7 posted same while I was researching.

[ScubaBart]

Notepad is still around and has that feature.

However, Access is not a text editor or word processing app.

As CJ suggested, can truncate string but would that be useful to you?

Yea, truncating wouldn't be beneficial as it may cut off key identifying info. The field if a quick reference field that the end user will be able to quickly tell which Hives have been inspected. The bullet approach will work. And I use a vertical scroll bar when the list is longer than my text box.

[Minty]

I would have thought a Subform displaying the related records would be more efficient and allow any formatting to be applied?

Any reason you didn't try that?

[CJ_London]

Good point - a subform also gets over the issue of wrapping - set the text box on the subform wider than the subform control and include a horizontal scrollbar

[ScubaBart]

I would have thought a Subform displaying the related records would be more efficient and allow any formatting to be applied?

Any reason you didn't try that?

This is on a continuous form so a sub-form isn't allowed.

[June7]

Might find this interesting https://www.fmsinc.com/MicrosoftAcce...edSubforms.asp

[davegri]

This is on a continuous form so a sub-form isn't allowed.

But there is a trick that works. Link the subform as a single form. After it is linked, change it to a continuous form.
You can Google it to find examples.

[June7]

Dave, not sure what you are advising. Main form cannot be continuous. No problem with subform being continuous.

[davegri]

Dave, not sure what you are advising. Main form cannot be continuous. No problem with subform being continuous.

Ah, I misunderstood the question. However, Google search indicates that subforms CAN be added to continuous main forms, in spite of warning message.