Help Request: Using CheckBox to Dynamically Add text to a field...

[hwest]

Greetings Forum-goers.

I'm new to the forums and new to using ACCESS in general. I've gotten good at snipping and reformatting Excel formulas to get what I want, but I'm finding Access is a whole new beast, and I'm not nearly well acquainted enough with VBA to just slap things together.



I've been attempting to created a database, forms, and reports based on a simple Excel system I've been using and I've run into the first of many roadblocks I am sure I won't be able to solve, so here we be.

I'm using Check Boxes to indicate whether or not a certain service/testing is required. I used the formula =IIf([CLAUSES] Like "*PH*",True,False) to cause the check box to be checked if the value "PH" is in the CLAUSES field. This worked fine in the CONTROL SOURCE, however I cannot figure out how to reverse that so that if the check box is checked in the form, "PH" will be entered into the table when entering new records. I've searched to no avail, and though I'm sure I ran across something that was close to the answer I wasn't able to make it work. Having what I have in the CONTROL SOURCE causes the checkbox to be locked in new records, since it's dependent upon the text in the field.

A few important notes: The CLAUSES field in question will have multiple letter codes (PH for physical testing, DI for dimensional verififcation, RT for X-ray, etc.. I was separating these using commas, though that isn't critical... I've created multiple checkboxes, one for each required test/service, which should each check the CLAUSES field for its letter code, or enter its specific code into the field for new records... the checking part works fine as mentioned above, but the reverse of it is a no go so far.)

Thank you in advance for any help you may be able to provide.

[orange]

Welcome to the forum.
You're right --Access is not Excel. Different products for different purposes and audiences. Built upon different object models.

You have focused on a detail -- IIf statement--, but what is the application in overview.

[hwest]

Thank you for a quick reply, I'm not sure what you're asking though? I'm working in MS ACCESS 2016. The object I'm trying to create will be a form used to create new records on a table which will be the master log of information, that will be pulled to other forms/reports. The -- IIf -- Statement is what I was using in Control Source of the Check Box, however it only reads information from the field in the table from old records, I can't figure out how to have the field populated when the check box is clicked on a new record.

The overview of the project in question is as a master database to be used for populating information dynamically in various forms and reports across our operation.

[davegri]

As you've discovered, you can't change the value of a textbox control via the click event if the control has a calculation in the Control Source. Remove those calculations.
Have a bound Yes/No field in the table for each checkbox, bound to the form checkboxes. Then in the form code:

Option Compare Database
Option Explicit

Private Sub Clauses_AfterUpdate()
    If Clauses.Text Like "*PH*" Then
        chkPH = True
    Else
        chkPH = False
    End If
    If Clauses.Text Like "*DI*" Then
        chkDI = True
    Else
        chkDI = False
    End If
    If Clauses.Text Like "*RT*" Then
        chkRT = True
    Else
        chkRT = False
    End If
End Sub

When the clauses textbox is changed, this code will run and set the checkboxes.

[Gicu]

Hi hwest and welcome!
In the AfterUpdate event of your checkboxes you need to add code similar to this (in this example the check box for the PH text is named chkPH and the Clauses text box is named txtClauses):

Private Sub chkPH_AfterUpdate()
if me.chkPH=True
.need to add PH if not present
    if instr(me.txtClauses,"PH")=0 then me.txtClauses=Trim("PH," & me.txtClauses)
else
'need to remove PH if present
 if instr(me.txtClauses,"PH")>0 then me.txtClauses=Trim(replace(me.txtClauses,"PH","")
end if
'clean up extra commas
me.txtClauses=trim(replace(me.txtClauses,",,",","))
if left(me.txtClauses,1)="," then me.txtClauses=mid(me.txtClauses,2)
if right(me.txtClauses,1)="," then me.txtClauses=left(me.txtClauses,len(me.txtClauses-1))

Cheers,
Vlad

[davegri]

New approach, have a look at this. No bound fields for the checkboxes needed in the table. Actually, checkboxes changed to radio buttons in a frame so that only one can be checked at a time.
If isn't clear where (what field) you want the PH, DI, or RT to appear in the updated table.
Also, if Clauses contains DIRT, which is it, DI or RT?
hwest-davegri-v01.accdb