Access 2010 - vbCrLf trimmed? (1 Viewer)

I first need to explain what I had working well in Access 2007.

Pt_Data form has one text-box named txtPP. There's a button labeled Prescribe which opens another form frmSelection. The selection form has 3 list boxes listing 1)Antibiotics, 2)NSAIDS and 3)Miscellaneous. What I did was select the required medications from each of the list-boxes, gathered them in a string variable, adding a vbCrLf after each selection so that I have something like this for prescription:

- Tab. Cefixime 200mg
- Tab. Diclofenac 50mg
- Tab. Metoclopramide

This entire text would be then copied to the txtPP in Pt_Data form on hitting the OK button on the frmSelection form.

After upgrading to Access 2010, I found there is a very nice addition of converting the textbox to Rich Text so as to enable one to format the words individually in the text-box. I find this is useful in order to stress some medication or some instruction in the PP text-box. So I converted the PP text box property to Rich Text. The code is the same. BUT now when I press the OK btn on the frmSelection after selecting the items, the string does get pasted into the txtPP but vbCrLf is gone! Vanished. The text appears thus:

- Tab. Cefixime 200mg - Tab. Diclofenac 50mg - Tab. Metoclopramide

On checking the code, vbCrLf is still there! I tried changing it to vbNewline, to Chr(13) & Chr(10) etc. But still the same behavior. It seems the carriage return and linefeed gets trimmed in the rich text. If I change the property back to plain text, I get everything back to what I wanted.

Can anyone explain to me this odd behavior? What is the workaround? OR what am I doing wrong?

Thanks.

Rich text uses the same as HTML for line breaks:

<BR>

So use that instead of vbCrLf.

Thanks a lot boblarson. That has worked. BUT what about my previous records? There are about a 1000 of them. The old records still show the same behaviour because they have been stored as plain text in the database fields. The new records will be saved withe the tags. Any method you can suggest to update the past records with the tags? Like say run a Find & Replace query on the table?

You probably want to do an Update Query using the Replace Function to replace the characters

Chr(13) & Chr(10)

used like that (that is the ASCII equivalent of vbCrLf)

with your

<br>

Thanks. But it doesn't help. Tried but failed miserably. Lucky I had made a duplicate of my DB.

jhsurti said:

Thanks. But it doesn't help. Tried but failed miserably. Lucky I had made a duplicate of my DB.

Click to expand...

It is ALWAYS good to test on a copy and to make another copy before doing live data.

So, since we aren't mind readers here, what did you actually try? What was the SQL of the query? We can't really tell what happened unless you give us more information. Also, you should say what "failed miserably" means. What was the actual results.

I tried it in the query design grid. But it seems the Replace function doesn't work there.
Another thing I tried was to put this in the criteria field: "*" & Chr(13) & Chr(10) & "*" and in the update field: "*" & "<BR>" & "*". This resulted in the field being updated to "*<BR>*".

I have been thinking of writing a code that goes through the entire recordset but I am not sure of the SQL statement to use. Should I collect the string data in a variable, pass it on to the Replace function and pass it back on to the recordset? Or can it be done directly on the filed? Any guide would be helpful.

Thanks.

Another thought: Replacing all the data would mean storing it in the database with the html tags, right? So just in case the data has to be used somewhere else for filtering or say some query, we will have to consider these tags. Is there a way to add tags when the record loads in the form? How to do that with code? The record source for the form is of course the database.

jhsurti said:

Another thought: Replacing all the data would mean storing it in the database with the html tags, right? So just in case the data has to be used somewhere else for filtering or say some query, we will have to consider these tags. Is there a way to add tags when the record loads in the form? How to do that with code? The record source for the form is of course the database.

Click to expand...

You can use a Rich Text Control instead of a plain text one.

As for the query it should look something like this:

Thanks a lot. I presume that in the Replace function, the place marked by you as LastName is the filed name I wish to have my replace function to act?
So if my table name is BData and the field i wish to update is PPMother then the statement should be like this:

UPDATE BData SET PPMother = Replace([PPMother],Chr(13) & Chr(10),"<br>",1);

Is that correct?