Insert comment if checkbox is ticked

[Nokoff]

Hi Guys, thank you for this wonderful community and the people that make it so, I am a first time poster but long time lurker.

I am working on a database which will basically serve to be a massive Auditing Checklist. Lots of "Is this done" type yes/no questions to record, some with a comment.

Brief Description: I have many child-forms on a tab-control, each with a different heading/category of things to check for. When someone visits a store, I would like them to record their findings in this form.



In the above example, if the Floors are clean, no input is necessary. If the floors are not clean then the user ticks the checkbox and the text "Floors were not clean" should be automatically entered into the field: Cleanliness 1. The reason for this is that I wish to link the value from this field to a Microsoft Word document using mail merge for reporting purposes, and in the report it needs to state any problems in full sentences.

I have tried using this code in order to update the comment field when the checkbox is ticked, however, the problem with this is that I would have to insert code manually for every single checkbox in my forms, and I have about 150 checkboxes in total.

Private Sub Cleanliness_A1_AfterUpdate()    If Cleanliness_A1.Value = -1 Then  'If checkbox is checked
      Cleanliness_C1.Value = Cleanliness_C1.DefaultValue
      Cleanliness_C1.Enabled = True
    Else
      Cleanliness_C1.Value = ""
      Cleanliness_C1.Enabled = False
    End If
End Sub

I would like to know, is there a better way to do this? I am still a newbie with regards to Access and Database design and structure, and I can't help but think there is a much better, more efficient way to accomplish this.

Kind regards


Nokoff

[CJ_London]

First you should normalise your data - you should have a table with fields something like

tblCleanliness
CleanlinessPK text (e.g."C1") primary key
AreaDesc text (e.g."Floors")

then another

tblQuestions
QuestionPK autonumber
BuildingFK long
CleanlinessFK text (Child of CleanlinessPK in tblCleanliness
CheckOK Boolean (default false)
Comments text

I've assume something to link this back to the building, business, whatever as BuildingFK

then you would only need the one bit of code

The only other way you can do this that I can think of is to create a public function in a module

Public Function Cleanliness_AfterUpdate()  
 If screen.activecontrol= -1 Then  'If checkbox is checked
      screen.activeform.subformname.form.controls(replace(screen.activecontrol,"A","C"))= screen.activeform.subformname.form.controls(replace(screen.activecontrol,"A","C")).DefaultValue
      screen.activeform.subformname.form.controls(replace(screen.activecontrol,"A","C")).Enabled = True
    Else
      screen.activeform.subformname.form.controls(replace(screen.activecontrol,"A","C")) = ""
      screen.activeform.subformname.form.controls(replace(screen.activecontrol,"A","C")).Enabled = False
    End If
End Sub

change the subformname to the name of your subform control

then in your A1, A2 controls etc on afterupdate property put =Cleanliness_AfterUpdate() (i.e. i.e. replace the [Event Procedure]). You can do this by highlighting all the 'A' controls before typing this in.

[Nokoff]

First you should normalise your data - you should have a table with fields something like

tblCleanliness
CleanlinessPK text (e.g."C1") primary key
AreaDesc text (e.g."Floors")

then another

tblQuestions
QuestionPK autonumber
BuildingFK long
CleanlinessFK text (Child of CleanlinessPK in tblCleanliness
CheckOK Boolean (default false)
Comments text

I've assume something to link this back to the building, business, whatever as BuildingFK

then you would only need the one bit of code

The only other way you can do this that I can think of is to create a public function in a module

Public Function Cleanliness_AfterUpdate()  
 If screen.activecontrol= -1 Then  'If checkbox is checked
      screen.activeform.subformname.form.controls(replace(screen.activecontrol,"A","C"))= screen.activeform.subformname.form.controls(replace(screen.activecontrol,"A","C")).DefaultValue
      screen.activeform.subformname.form.controls(replace(screen.activecontrol,"A","C")).Enabled = True
    Else
      screen.activeform.subformname.form.controls(replace(screen.activecontrol,"A","C")) = ""
      screen.activeform.subformname.form.controls(replace(screen.activecontrol,"A","C")).Enabled = False
    End If
End Sub

change the subformname to the name of your subform control

then in your A1, A2 controls etc on afterupdate property put =Cleanliness_AfterUpdate() (i.e. i.e. replace the [Event Procedure]). You can do this by highlighting all the 'A' controls before typing this in.

Thank you very much! I figured this out and adapted the code you gave me to work for my situation.

However, now I would like to convert this to a web database, and I see that VBA is not supported for web databases. Is there any way to do this using macros instead? Or some other way?

[June7]

I think macro equivalent would be SetValue action.

You will probably be very frustrated by trying to filter records on those multiple Yes/No fields.

[knarfreppep]

I wonder if a non-existent problem isn't being solved here.

If the checkbox Label is "Floors were not cleaned", the mere checking of that box records the fact that the floors were not cleaned ... why put those words into a comment field you'll need to clear out if the user realises they checked the box in error and un-checks it?

To my mind that goes against all that normalisation seeks to achieve.

I'd leave the comment field though, for things like 'Worse than ever'.

[June7]

Agree this comment is redundant. And if user unchecks the checkbox, any comments, even their own input, still needs to be cleared.

Again, non-normalized data structure possibly real issue. Instead of checkboxes, normalized structure would be a record in a related table for each observed condition. A 'checklist' may be fine for a paper document as a reference when doing 'walk through' but for relational database makes for poor data structure.

[knarfreppep]

I wonder if a better (normalised) way is to have -
.tblEntities with fields lngID, strName, etc.
.tblEntity_AuditExceptions with fields lngEntityID, intException, memComments
.tblLookup_AuditExceptionCategories with fields bytID, strDescription
.tblLookup_AuditExceptions with fields intID, bytCategory, and strDescription

This would facilitate a radical un-complication of your form.

[Nokoff]

I wonder if a non-existent problem isn't being solved here.

If the checkbox Label is "Floors were not cleaned", the mere checking of that box records the fact that the floors were not cleaned ... why put those words into a comment field you'll need to clear out if the user realises they checked the box in error and un-checks it?

To my mind that goes against all that normalisation seeks to achieve.

I'd leave the comment field though, for things like 'Worse than ever'.

Hey, thanks for taking the time to comment.

The reason I'm using the comment field is so that I can transfer that text over to a mail merge word document report. I know it's a bit archaic but it's what is required.

[knarfreppep]

A 'checklist' may be fine for a paper document as a reference when doing 'walk through' but for relational database makes for poor data structure.

Absolutely ... of course, the paper/PDF/XLS 'checklist' can always, easily be produced from the normalised data.

[Nokoff]

That's interesting knarfreppep I'll have a look at that in the morning.

[knarfreppep]

The reason I'm using the comment field is so that I can transfer that text over to a mail merge word document report. I know it's a bit archaic but it's what is required.

Oh yes, I get what you want to do ... but the place to get that text from is tblLookup_AuditExceptions, where it will be stored only once in your dB but used many times with all the advantages that brings ... that's 'normalization'.

[Nokoff]

What is the easiest way of getting the audit question text and audit exception text from the table fields without using VBA to populate the fields in my form?

[knarfreppep]

Use a query.

[knarfreppep]

You would end up with a form and report looking not much more complicated than this -

Audit Exceptions

Entity XYZ Coy

Category > Exception
Cleanliness > Floors not cleaned Comment : Some areas OK
Cleanliness > Door handles smudged
Signage > Faded
Signage > Out of date
Tidyness > Waste paper baskets not emptied
Uniforms > Creased

[knarfreppep]

What is the easiest way of getting the audit question text and audit exception text from the table fields without using VBA to populate the fields in my form?

Properly done, audit question text and audit exception text are the same thing save for the optional comment.