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

[CJ_London]

Ok



create this function in your form module

function popActions()
dim ctrl as control
dim s as string
s=""

for each ctrl in me.controls
    if ctrl.ctrltype=acCheckbox
        On error resume next 'in case the ctrl does not have a bound label control
        If ctrl=true then s=", " & ctrl.controls(0).caption & s
    end if

me.FldHistoryActions=mid(s,3)

end function

then for each of the checkboxes in your form put

popActions

in the after update event

Note this assumes that each of your checkboxes has a bound label control

[Micron]

Frustration is a personal thing. Some have lower tolerances, but I doubt anyone here is frustrated by your thread, least of all me. Unless one is born with an innate understanding of all of Access, we were all where you are at one point. I'm here because I was helped years ago with what was to me, a complex query problem (thanks again, Orange!). So kind begets kind.

What CAN be frustrating is
- posters that seemingly refuse to post lengthy code within tags
- don't answer responder questions, thus don't facilitate our understanding of things
- simply say "it doesn't work", or "I get an error" and provide nothing else. To a lesser degree, only posting error message numbers. Now I have to look it up from the thousands?
- insist on telling us what doesn't work rather than what they want to achieve

Avoid those and you'll be off to a good start. If you really want to help, don't post pseudo data. I hate providing a solution only to find out that it's not about "shoes" at all. It's about numbers so my solution doesn't work. Sure you can hide confidential info but don't provide numerical data then tell us it's text or dates. If you really want to make me happy, don't post your code as pictures, otherwise I won't re-create a big block of code on my side in order to fix it. Those might just be my own personal pet peeves and may not bother anyone else.

Now on to your questions. An attached (or associated) label is what you get when you drag a control onto a form. If you move it, the label moves too. Some people break the bond, usually as a personal preference for ease of moving things around. I tested and a checkbox does have a member of its controls collection (as I said, only ever one member in it - the label) in my immediate window:
?forms!form3.check1.controls(0).Caption
result of inquiry: Check1
Thus I know it works. If you didin't add your own label or un-attach the automatic one, it should work

Did your research include topics of normalization and entity/attribute relationships? If not, it's a MUST. If yes, I think you missed the point. Common mis-steps are things like
- building tables like spreadsheets
- using multi value fields
- storing calculations and concatenations when not necessary (almost never is)
- allowing special characters or spaces in names
there's a host of others, some of them more personal, I think.

So when I mentioned not concatenating versus storing in records, it has to do with normalization, which is why I'm asking if you understand the concept. In simple terms, let's say I do a series of inspections. Either I alter the status, which reflects that prior ones have been done, or for a particular inspected item, I record the individual inspections as records as in table tblInspections

InspectID	InspectType	Pass	InspectDte
1	10	-1	3/09/19
2	11	-1	3/10/19
3	12	0	3/11/19

Skill testing question: Why is InspectType a number and where does it come from?
Maybe you can see from the above that if all that is required to be stored is a pass, then PassDte would do the trick. A yes/no field isn't required.Thus a big part of design is the business it needs to support.

That's all for this lesson. Hope I didn't miss one of your questions.

[mainerain]

Did your research include topics of normalization and entity/attribute relationships?

Yes. I didn't fully understand everything, but yes. I got sidetracked with my thinking due to overworking and tired. Hence my delay in posting. I see your point about keeping items separate in fields and then concatenating them for output.

Skill testing question: Why is InspectType a number and where does it come from?

comes from another Table, possibly from that Tables Primary Key?

Here is some code I came up with so far:

( on FrmHistory, Record Source is QryHistoryLoop )

Private Sub CbxClean_Click()  
 
    If CbxClean = True Then
    Me.FldHistoryClean = Me.FldHistoryDate
  
    Else: If Me.CbxClean = False Then Me.FldHistoryClean = Null

End If 
End Sub
__________________________________________

Private Sub CbxVerTags_Click()

   If CbxVerTags = True Then
   Me.FldHistoryVerTags = Me.FldHistoryDate

   Else: If Me.CbxVerTags = False Then Me.FldHistoryVerTags = Null

End If
End Sub

also I have the following, but I don't know where to put or exact syntax

Dim clean As String
Dim tags As String
Dim actions As String

If FldHistoryClean = True Then
clean = "Cleaned, "
End If

If FldHistoryVerTags = True Then
tags = "Verified Tags, "
End If

End Sub

actions = clean & tags


on Report I created an unbound Text Box named TbxActions
I don't know how to get the result to TbxActions

[Micron]

from that Tables Primary Key?

Yes. In table where the value is generated it's the PK. Where it's used to link to related tables, it's the FK (foreign key in those tables).

- posters that seemingly refuse to post lengthy code within tags

Upon review, I see that you were never asked, so now I'm asking. Please do so for anything beyond a few simple lines and use indentation. In simple terms, it's always better if you're not fully aware of how the forum can mess up your code. It's the # button on toolbar... You can always go back if you forget and edit your post for some period of time.

The "default" for a Boolean control is True, thus you can skip the = True. I've never seen Else: as opposed to Else. Else: looks like a line label. Surprised it doesn't error on you. You are also omitting Me in some places. Best to be consistent.

Since there is only 2 possible conditions in your situation, and neither one of them require any further logic, then maybe

Private Sub CbxVerTags_Click()
If Me.CbxVerTags Then Me.FldHistoryVerTags = Me.FldHistoryDate
If Me.CbxVerTags = False Then Me.FldHistoryVerTags = Null
End Sub

or again, as there's only the possibility of T or F

Private Sub CbxVerTags_Click()
If Me.CbxVerTags Then
   Me.FldHistoryVerTags = Me.FldHistoryDate
Else 
   Me.FldHistoryVerTags = Null
End If

End Sub

I don't know where to put or exact syntax

Me neither. No idea if this is about a form or report.

[mainerain]

Thanks for the info about how to post replies.

Dim clean As String
Dim tags As String
Dim actions As String

If FldHistoryClean = Then
   clean = "Cleaned, "
Else 
   FldHistoryClean = Null
End If

If FldHistoryVerTags Then
   tags = "Verified Tags, "
Else
   FldHistoryVerTags = Null
End If

End Sub

FrmHistory provides data to TblHistory

TblHistory provides data to QryHistoryLoop

QryHistoryLoop provides data to RptHistoryLoop

How do I use the above code (or some other code) to Print text via RptHistoryLoop on what was checked off on FrmHistory?
Since I don't need to store the result I figured I would write it to an unbound text field named CbxActions in the report. Do I put the code at Private Sub Report_Load()?

Sorry if not giving enough info for clarity. I don't know what I don't know.

[ssanfu]

OK, showing my age......

<snip> I've never seen Else: as opposed to Else. Else: looks like a line label. Surprised it doesn't error on you. <snip>

In Access Help -> Single line If...Then...Else Statement Syntax

If condition Then [statements] [Else elsestatements]

With the single-line form, it is possible to have multiple statements executed as the result of an If...Then decision. All statements must be on the same line and separated by colons, as in the following statement:

If A > 10 Then A = A + 1 : B = B + A : C = C + B

While use of the colon for line continuation had be depreciated, it is still available for backward compatibility.
Back in the day, memory was very expensive. It cost $100 for 1 mb of memory for my 286 computer - that $100 bought 9 DIP chips (8 bits + parity). Needless to say, I only had 2 mb of memory in my computer.


Nowadays memory is relatively inexpensive. IMHO, the single line syntax should never be used because it is easier to miss the fact that it is an If statement (when reading code) and there is no reason to try and conserve bytes (memory)

Which is easier to see and read?

    strSQL = "SELECT *"
    strSQL = strSQL & " FROM Inspection"
    strSQL = strSQL & " WHERE PartNumber = '" & Me.PartNumber & "' AND IssueID = '" & Me.cmbID & "'"
    '    Debug.Print strSQL
    MsgBox (Me.PartNumber)
    If Digits = 1 Then MyString = "One" Else MyString = "More than one"

    Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)


' or

    strSQL = "SELECT *"
    strSQL = strSQL & " FROM Inspection"
    strSQL = strSQL & " WHERE PartNumber = '" & Me.PartNumber & "' AND IssueID = '" & Me.cmbID & "'"
    '    Debug.Print strSQL
    MsgBox (Me.PartNumber)
    If Digits = 1 Then 
      MyString = "One" 
    Else 
      MyString = "More than one" 
    End If

    Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)

My personal preference is to always use the block form of the If...Then...Else Statement.

[Micron]

I'm looking for a hidden message because you emphasized depreciated
I don't know if the meaning is that it's not worth as much as it used to be (as in depreciation) or if it's no longer to be used, as in deprecated.

You're saying that the single line syntax was less of a memory hog??
I was aware of the single line syntax for declaring variables though.
In the end I might be missing the point because your example doesn't contain the colon I was referring to as in Else:

If it works as mainerain posted, then who am I to argue?

[Micron]

If FldHistoryClean = Then

That's not what I wrote, is it?

[ssanfu]

@Micron

I saw the word depreciated when looking at a book on HTML5 programming. I looked up depreciated and deprecated in "The Free Dictionary" (https://www.thefreedictionary.com).

You are correct - I sit corrected.
Under deprecated, the 3rd definition is what I meant:

3. Computers To mark (a component of a software standard) as obsolete to warn against its use in the future so that it may be phased out.

I was using

If A > 10 Then A = A + 1 : B = B + A : C = C + B

as an example of using the colon to have multiple statements on one line.


In the second example, I was trying to illustrate the readability of the single line form vs the block form if the "IF...Then...Else" statement. (so no colon)

[Micron]

@ssanfu, you should know that in my high school graduation year book, someone signed "To the guy who uses $20 words" or something like that. Sorry for being me!
BTW, $1 could get you a burger, coke and small fries then, so $20 worth of letters in a word was a lot.