vba to write text to Long Text Data Type Field from click event

[mainerain]

I would like to click on a check box to make it true and write the caption text to a Long Text Data Type Field. Ideally I would Concatenate multiple captions in the Text Field.



Thanks for assistance

[ranman256]

cant you just type into the Long Text field?
but

txtLong = txtLong & vbCrLf & txtCap

[CJ_London]

in the check box after update event put code something like

if me.checkboxname=true then me.longtextboxname=me.ctrl1 & " " & me.ctrl2

use your own names for the control names

if you mean caption then rather than me.ctrl1 you would use me.ctrl1.caption

[mainerain]

Thanks Ajax, that helped.

I want to be able to independently add text to FldHistoryActions. I don't know the syntax for writing variables to a field or how to update when a check box is checked or unchecked.

I have

Private Sub FldHistoryCleaned_AfterUpdate ()
Dim cleaned As String

If Me.FldHistoryCleaned = True Then
cleaned = LblCleaned.Caption & (", ")
Else: cleaned = ""

End If
End Sub

Private Sub FldHistoryVerifiedTags_AfterUpdate()
Dim tags As String

If Me.FldHistoryVerifiedTags = True Then
tags = LblVerifiedTags.Caption & (", ")
Else: tags = ""

End If
End Sub

Me.FldHistoryActions = cleaned & tags

[Micron]

rule of thumb: when a procedure ends, variables within it die along with their values, thus you cannot place the code that updates a field outside of the procedure as you are showing.
You can refer to a label control to get its caption, or you can refer to the default member of a control's collection (there is only one member and that is the attached label) as I have here. If the label isn't attached, it will error. I'm showing this approach for the future because it's very useful when you have to loop through a form's controls and prompt "Please fill in values for ...) and use the attached label caption rather than the name of the control like so many people do. The control name is usually too cryptic to show to a user in that case. You can always change the following to use a reference to your label instead.

Private Sub FldHistoryCleaned_AfterUpdate ()

If Me.FldHistoryCleaned = True Then '<< if it's not true, there is only false. Since there's nothing to do if false, you don't need an ELSE
   Me.FldHistoryActions = Me.FldHistoryActions & ", " & Me.lblCleaned.Controls.(0).Caption
End If
End Sub

First procedure would run and add , + space at the beginning, then the caption. Code same for other control(s). If none are true, no change. Regardless if one or both are true, one or both captions are added with a preceding ", " (without quotes), ending in the caption.

[mainerain]

Thanks for the reply. I tried your code and had a couple issues.

It would not except
Me.lblCleaned.Controls.(0).Caption

I had to remove the . before (0)

and then got a Compile Error: Method or data member not found for

.FldHistoryActions
located here:
= Me.FldHistoryActions & ", " & Me.lblCleaned.Controls(0).Caption

[CJ_London]

the implication of your error is that lblCleaned does not have an associated label control. Think it is time you provided more information - perhaps a screenshot of your form together with identifying the control types and names as an example of what you are trying to get.

[mainerain]

Anything else?

[CJ_London]

yes examples of what you are trying to achieve - presumably to populate the FldHistoryActions control? - e.g. do you want 'Loop Tag, Event Date, etc' or something else? And what basis you are using to decide which values to appear? perhaps because the control is populated with a value?

[mainerain]

Yes, to populate FldHistoryActions.

To provide a listing of items that can easily be checked off and yet can be uniformly printed out for reports using the Captions. The listing will be used as a reminder of items to be addressed, provide consistency of recording no matter who does the work. All checkbox items will be reflected in FldHistoryActions, not the existing Text Fields.

The ability to check off an item and have its Caption displayed in FldHistoryActions and disappear when unchecked.

Thank you for your time and effort

[CJ_London]

Yes, to populate FldHistoryActions

with what? please don't describe what you are trying to do - show it with examples and the basis for determining what is to appear. You know your app, I don't

[mainerain]

box checked = Caption in Action field
box unchecked = Caption removed from Action Field

check box: FldHistoryCleaned
check box: FldHistoryVerTags
check box: FldHistoryVerFlowDir
check box: FldHistoryVerGndStraps
check box: FldHistorySavedConf
check box: FldHistoryUpdateAMS
check box: FldHistoryUpdateDev
check box: FldHistoryVerAlerts
check box: FldHistoryPerTuner

Label: LblCleaned
Label: LblVerTags
Label: LblVerFlowDir
Label: LblVerGndStraps
Label: LblSavedConf
Label: LblUpdateAMS
Label: LblUpdateDev
Label: LblVerAlerts
Label: LblPerTuner

Caption: Cleaned
Caption: Verified Tags
Caption: Verified Flow Direction
Caption: Verified Ground Straps
Caption: Saved Configuration to Loop Folder
Caption: Updated AMS
Caption: Updated Device
Caption: Verified Alerts
Caption: Ran Performance Tuner

[Micron]

It would not except
Me.lblCleaned.Controls.(0).Caption
I had to remove the . before (0)

Yes, sorry - typo from moving stuff around

and then got a Compile Error: Method or data member not found for
= Me.FldHistoryActions & ", " & Me.lblCleaned.Controls(0).Caption

As commented on already, then your labels are not attached. I said in my post that they had to be but I guess you didn't check that.

[Micron]

Really, the whole exercise looks to me like something I'd never do. As a user, I check a box, some caption gets added, I do another, another added, then I go "crap! I didn't mean to do that." Now I go back and uncheck. Now what? Wait, you made it so that I can't uncheck a checked? Now what?

This is like a calculated field, which is almost always a no-no and this is a no-no situation. A form should be presenting a concatenation of values from RECORDS and NOT be storing them as csv data in some field.

[mainerain]

First of all I want to say thanks to everyone who has replied. I am an admitted Newbie.

I am trying to make my life easier at work by creating a database. That said I am putting it together by the seat of my pants. I am surprised that I have made some good gains up to this point by getting suggestions on this forum, trial and error, and a few training video's. I realize that it's probably frustrating to folks on this forum because you know how to go about things and I don't. That is why I am so thankful for anyone responding to my posts.


"A form should be presenting a concatenation of values from RECORDS and NOT be storing them as csv data in some field."

Micron, so having the "Caption" Text contained in a Table, then selecting a check box takes a value and Concatenates with other values which are then written to FldHistoryActions? I guess I don't understand. Hopefully you understand what I am trying to accomplish. I just don't know the best method.

What does it mean "your labels are not attached?" I dragged the field to my form and the box appeared with a place for a Caption.