Display concatenated strings as RTF on form text box

[ontopofmalvern]

I have a table which has several text fields that I want to concat together in SQL with some formatting but after I have set the data source of text box to the concated string it will not allow me to set the format to RTF. So the text is unformatted and format tags are shown as plain stings (e.g. "plain<strong>BOLD,/strong>" instead of "plainBOLD").

I can only get the text box to accept RFT format if the source is either a single long text field OR the text string created by concating in a VBA function.

The former solution doesn't seem practical or efficient as it would effectively mean creating duplicate data in the table that would require updating each time one of the contributing fields is altered. The second solution works - but is slow (the form can have as many as 40+ such text boxes).

Ideally I want to be able to concat in the form's underlying SQL and the form just to see <strong> as a formatting tag rather than part of the string.

Any ideas, it feels this should be do-able, I think it should also be easy.

ps a poor compromise could be just concat in a character that will force a line break in plain text on an unformatted text box - but that would be a plan B.

 

[CJ_London]

think your code is wrong the comma should be a <

see this link - there is a db you can download which you can use to determine the formatting codes required

how to find richtext codes for highlight, underline etc

You might use this with the objective of highlighting searched for text in a text field, or perhaps to format the date and time values in a single field a different colour (although date/time fields would then be text format). Will also work with numbers but they also will be converted to text...

www.access-programmers.co.uk

 

[ontopofmalvern]

That's just a typing error on my post - that aside I can't set text box propery format to RTF if source is a concated string, only if is a single long text field from a table.

However I believe I have stumbled across a solution. Which I'm now checking and will post what I find if it works.

 

[CJ_London]

can't set text box propery format to RTF if source is a concated string

you didn't provide your whole code so from your comments I now assume by source you mean controlsource

you would need to assign a string to your control

mycontrol="plain" & "<strong>BOLD</strong>"

 

[ontopofmalvern]

If anybody is following this I have found an old form I created that allows me to do exactly what I want, but I cannot for the life of me see what the difference is, I have checked in detail all the form property setting and the text box setting... but whatever I do when ever I try on my new form to change the text box Text Format property to Rich Text (using the drop down options) format I get an error "The setting you entered isn't valid for this property"

 

[ontopofmalvern]

Yes the ControlSource, when set to a single field that is a short or long text field in table I am allowed to switch Text Format from 'Plain' to 'Rich'

however if the ControlSource is a string concated in SQL (e.g. "<strong>" & tbl.fieldName & "</strong> AS txt " it will not let me change Text Format to Rich. I'm utterly baffled as I have done it before.

so if control source is 'fieldName' - no problem I can set format to Rich Text
if control source is 'txt' I cannot

 

[CJ_London]

it would really help if you provided a more accurate example of what you are actually doing. I have no problem with concatenating in a field and displaying as richtext in a form.

This is my sql - as a recordsource to a form, but works just as well if used in a query and the query used as the recordsource

SELECT admActions.ActionType, admActions.ActionName, [actiontype] & " " & "<strong>" & [actionname] & "</strong>" AS Expr1
FROM admActions;

in continuous form (created using the wizard)

this is the form result in datasheet view

I suspect what you need to do is set the richtext property before you save the form for the first time. Or perhaps when you first add the control if not using a form wizard to create the form

 

[ontopofmalvern]

Yes, thanks you are certainly onto something there. The other thing that seems to make a difference is having a query that is saved as a query rather than having underlying SQL in the page itself (which is my prefered way of working as it means less files to navigate). I think I have enough now to make it work. Tah

All seems very silly work arounds to me, as long as all you're puting strings into box rather than numbers you should be able to render it however you like. Hey-hoo that's access for you.

 

[ontopofmalvern]

simpler solution is instead of binding text box directly to underlying SQL use a formaular, so if the field you want is called 'field', type '=[field]' into text box then you can change textfromat to RichText.