Can I make a text box only show the last line item in multiple line items of text

[achammar]

Hello everyone,

Odd request. I have a form with a table as a record source. It is split form so the top is datasheet view, and the bottom is single form view. (I'm not super good with Access but I know Excel VBA very well so I can make my way around)
Anyway, what I do is add lines of text to the existing text in a text box on the bottom half of the form, so I end up with multiple lines of text. I can see all the lines in that text box below because it is large enough to show all the lines of text.
The corresponding field in the top half of course changes to match it. It's kind of like a history. But I only want to show 1 line in the top portion of the form because the rows are sized to show only 1 row of text. Catch is I want it to show the last line of text, not the first line, so I can see the last entry made, not the first. I'm pretty sure this is not going to be possible, but I won't know until I ask..

ps... I do not want to use the auto history option.


Thanks!

[Micron]

A textbox with multiple distinct lines (i.e they have line wraps/carriage returns) is still only showing one field in one record for all of those lines. If this is like a compilation of comments I'd design it so that each one is its own separate record for a few reasons, one of them being it's too hard (impossible?) to separate them. Imagine if you had asked if you could simply pick out the 3rd line. Consider also what can happen if you allow this multi line field to be edited - it is entirely possible to delete it all, whether by accident or not.

If you cannot separate the comments into records, then using code you might be able to find the last line wrap/carriage return that has text after it and then get everything that comes after it. However, I would not consider that the better solution. For me, the fact that the last entry might have wrap/return characters but no text makes this more of a problem.

[achammar]

Thank you Micron,

Each line item is separated by a return (Vbnewline). The new text gets entered by programmatically with code using another text box with no control source and OK button and the value of that textbox just gets added to the end of the history text box (which as a control source in the table) as a new line when Ok is clicked. Only 1 line can be added at a time. I see your point about accidently deleting all the lines because the history text box can be directly edited also, but I'm not too worried about that. It just a small project for me. I can see the full history in the text box at the bottom, but the field text box at the top only shows 1 line, and it's the first one. I just want it to show the last one so I can see the last entry made. I don't think it's possible, but I thought I'd ask. 1 solution is to have each line item added to the beginning of my history text box and have the newest on top, but I'm trying to avoid that, but I could make that work. Thank you very much for your reply. I don't know if what I want is making sense completely lol

[Gicu]

Hi, you should be able to do that by using a custom function using Split() to switch the order of the lines, basically sorting an array descending.

[CJ_London]

You will find that 'off the shelf' objects such as split forms cater for most eventualities, but not every eventuality. A split form has a subform using the same recordsource as the main form (it is effectively just a datasheet view of the main form), so modify your recordsource to suit the subform and you will find that affects the main form. The solution is to 'grow your own'. Have your main form, with a separate subform and some navigation code in the subform to change the record in the main form. That is what we used to do before split forms came along - all split forms do is save you having to think about how to do it with a little bit of code - and prevent you from building an efficient app once you start to have large numbers of records. The code might be something like this in the subform current event

me.parent.filter="PK=" & me.PK
me.parent.filteron=true

you mention autohistory so presumably your field is a memo field? Memo fields can be difficult to manipulate as in most cases only the first 255 characters can be accessed from a sql point of view. But may not be a problem for your case

Next problem - how to determine the last line. As Micron suggests, find the last line based on the character before (wrap/carriage return) the last line. For example in your recordsource you might call a function where you pass your memo field as a parameter. The function then uses the split function to break the field on the wrap/carriage return into an array, then returns the last element. Be aware that memo fields are html, so the wrap/carriage return may else something else. And if it is a memo field, there may be more to it.

Then you have the issue that the last element might contain text which exceeds what can be displayed in the control - so you don't see the last line, only the first line of the element. At this point, google Stephen Lebans who did a lot of work on this sort of problem.

Or perhaps someone has hit return by mistake so the last element is actually blank.

If this is required functionality, I would look at your table design - As Micron suggests I would be using a continuous (sub)form which contains one comment per record. Then the requirement is very easy to fulfil.

Edit - just seen post #3 - so seems to me have your code put the new entry at the beginning would be an easy solution

[Micron]

vbNewLine is a constant (if I have pegged that correctly) that is a meld of Chr(13) and Chr(10). I don't know if you can have these trailing non-printable characters in a field or not - they might get stripped out the same way that trailing or leading spaces are removed from fields by Access. If you can answer that based on your data, then perhaps some coding solution can be offered. One thing that likely matters a lot is whether or not the field is formatted as RTF. If so, the chr() values will be replaced by <div> tags.

[Gicu]

See this for what I (and Ajax) suggested earlier:
https://stackoverflow.com/questions/...verse-an-array

[Micron]

I think it's quite simple if all you want to do is move the cursor to the beginning of the last line. Or am I missing something?

Dim strIn As String
strIn = Me.someControl
If Nz(strIn, "") <> "" Then
   Me.SomeControl.SetFocus
   Me.SomeControl.SelStart = InStrRev(strIn, Chr(10)) 
End If

Obviously replace SomeControl with the proper name.

EDIT -probably the part I'm missing has to do with the split form - something I have little experience with since they're not real practical. However, in a textbox with sufficient height to show all the rows in the record, it does move the upper rows out of sight, so maybe...

[Gicu]

That I what I first thought but it would only work when the control gets the focus, reversing the array would work without that.

[Micron]

it would only work when the control gets the focus,

You can accomplish that from any other event, provided that the control is not disabled?
The objects I have in any db that I can test with is limited. Perhaps OP needs to review all that has been offered and if he/she wishes to post a db copy that is compacted/repaired and zipped we could zero in - as long as questions have been answered.

[CJ_London]

it would only work when the control gets the focus,

point is - the subform part is a datasheet - so only the current record would show the last line using setfocus, not all the lines. And as previously stated, with a split form, you cannot use code to impact only the subform part or visa versa.

[Gicu]

Please review the attached file for a simple way to do it as suggested (custom function with Split\Join and resorting of array).

Cheers,
Vlad

[achammar]

Thank you all for your responses! Some of them are over my head, but I think I'm not explaining right. Here is a sample file. The Comment History field is the only one of concern. It is the last column in the upper portion of the form. If you select a record that has a value in that field the bottom portion of the form populates with that record obviously. The record source is a table.. very simple.
Just put a value in the "Change" box at the bottom (this is the value that you want to change the quantity by). It can be positive or negative. The result box will populate with the new line item to add to the "Change history" box. When you click ok that "Result" box value will be added to the change history box and the form updates. That all works fine. You'll see each line item in the history box below. In the upper portion you will see only the first line item. I want to see the last one instead.
I hope that clears thing up.

Thank you all again!

[Gicu]

Did you have a look at my sample? I have Access 2013 and it tells me I need a newer version to open yours.
Cheers,

[achammar]

Gicu,

I just did and it looks like it does what I want! I didn't want to do the extra work of making a query, but at least it's an easy one lol... I will let you know how it goes. Thank you!

Also, I emailed that file to myself yesterday so I could work on it at home. When I went to open it, it told me I needed a newer version of Access also and I have Microsoft 365 the newest updated version so I don't know what's going on with it. I can open up any other Access file from work at home, just not this one. Makes no sense.