Adding new line of text to Long Text field w/ VBA

[ScubaBart]

So, still building on my Apiary / Hive / Inspection application, converting everything over to VBA and it's going pretty well, learning stuff along the way and while I'm not to this point yet, I will be soon.

When I do certain things with a given Apiary or Hive such as make it in-active or move it, I decided I want to automatically enter a line in the Description / History field which is a Long Text field in each table as needed.

For instance, if I move a Hive from one Apiary to another, I want to add a new line something like, "Moved from Apiary XYZ on 01/01/2024" (or whatever the date is).


So, I would be using a pop-up form to complete the move and have it pulling data from the appropriate query that contains all the relevant fields and I'm thinking something like:

Form!Move_Hive!Description.Caption = "Hive moved from Apiary " & [Form]![Move_Hive]![Apiary] & " on " & Format$(Now(), "Short Date") & vbCrLf & [Form]![Move_Hive]![Description]

Then I would expect to see:

"Hive moved from Apiary XYZ on 9/12/24
Blah Blah Blah rest of existing Description"



Am I anywhere close ? Not sure about the use of the Caption in the first part but am using it for other things like plain text fields and other controls and buttons so figured it was appropriate.

One thing I am still figuring out is when I need the full path format for controls and when I only need the control name and when I need the brackets and when I don't. So that stuff may be wrong but I think adding it is worse than not when trying it for the first time.

[Welshgasman]

You would not use the Caption property, but the Value property, which you can omit, so
Me.hive = me.hive & me.txtToAdd

[ScubaBart]

You would not use the Caption property, but the Value property, which you can omit, so
Me.hive = me.hive & me.txtToAdd

Forgot about the Value property. makes sense.

does the Me prefix essentially take the place of the full Form!.... syntax ?

[mike60smart]

Hi
I would recommend that instead of adding new lines to a text Control, it would be better to have a table with a unique record to record each change made to a specific Apiary.

[Welshgasman]

Forgot about the Value property. makes sense.

does the Me prefix essentially take the place of the full Form!.... syntax ?

Yes, but only refers to the object the code is running in Form or Report.

[Micron]

I'm with anyone who thinks these should be separate records. If nothing else, it can provide a history. Once you start melding comments/events in one field it's very difficult (if possible at all) to segregate them based on anything, such as a time line.

[Welshgasman]

I concur, but just responded to the query as asked.

[moke123]

The Me. keyword will also invoke intellisense in the form or report module.

[ScubaBart]

Thanks for all the input.

I understand the reasoning behind the suggestions to add a field or separate linked table for "events" like this and it may be that I end up doing that, at least for a couple specific types of 'events' but for a few other parts of my app, it doesn't justify the need for a searchable list of records.

I will know more as I get into the nitty gritty of those particular aspects and the overall flow of the database. Right now, still converting all the macros to VBA and fine tuning the basic navigation from form to form. Only able to get in a few bits of time here and there.

[Welshgasman]

Probably worth doing now.
That way you could have a date/time when something happened?
Try and think ahead a few months, not just a few minutes.