User Customisable Labels In Form

I have a simple, 50 field, flat MS Access 2013 database.
Fields 11-50 are Yes/No fields, called: ynKEYWORD_11 up to ynKEYWORD_50

The main form is called CONTACTS.
In this form, I want the labels beside the 40 checkbox fields
to be easily editable by a novice user without access to Design Mode.
This will effectively allow them to set their own keywords.

I will have a simple table (datasheet) like this for users to edit Keywords (i.e. CONTACTS Labels).
11                English
12                Irish
13                Male
14                Female
How can this be done?
Would it best to use a text box beside the checkbox or the label attached to the checkbox?
LVL 24
EirmanChief Operations ManagerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Helen FeddemaCommented:
If the KEY_ID values are used for the filtering, and the key words are only so the users know what they are filtering by, I think a textbox next to each checkbox would be best.  However, this is rather risky, since users might mis-label the checkboxes.
Dale FyeOwner, Developing Solutions LLCCommented:
I would recommend another table (tbl_KeyWords) with fields Key_ID, and KeyWord and values in the Key_ID field from 11 - 50

Then you could create another form for the user to enter the KeyWord values associated with each Key_ID, and then use the main forms Form_Load event to loop through the Key_ID values and enter the captions of the labels associated with eacy Key_ID.
Helen FeddemaCommented:
Dale's suggestion is a good idea (though perhaps more than the users would want to do).  My technique of a form for editing lookup tables could be useful here -- I am attaching an Access Archon article describing it.
Lookup Tables editing
Powerful Yet Easy-to-Use Network Monitoring

Identify excessive bandwidth utilization or unexpected application traffic with SolarWinds Bandwidth Analyzer Pack.

On the surface, it always seems like flat is simpler but once you start building queries, I think you might regret the decision.   Forty sounds  like a lot of options but given your example, you might actually run out quickly.  I would be much more inclined to normalize the schema.   My advice is to create some queries using the flat table vs a properly normalized schema and see what you prefer working with.  When you normalize, you'll have better control over the values and you won't limit yourself to 40 options.
EirmanChief Operations ManagerAuthor Commented:
Dale Said ....  then use the main forms Form_Load event to loop through the Key_ID values and enter the captions of the labels associated with each Key_ID
That is exactly what I need to do!  Update the captions from a separate table

I'll create a simplified version of the database with sample data and post it here in about 6-8 hours.
Hopefully, all that you Dale, (or anyone else) will have to do, is add the VBA for the Form_Load event.
(That type of coding is beyond my present capabilities).
Helen - I'll study your archon file later. It may be of value in the future.

Pat - There is no repeating data in what is a simple database that could work with Rolodex system.
When you see the db, you'll see that normalisation is not required.
EirmanChief Operations ManagerAuthor Commented:
I have attached a sample single database.
Would it be possible to update the brown labels instead of the control's captions?

Dale FyeOwner, Developing Solutions LLCCommented:

I'm not able to open your db now (working from iPad).  Assuming that the checkbox labels all have names like "lbl_YNKeyWord_11, lbl_ynKeyWord_12, ..." ,
and the values in your table look like:
Key_ID    KeyWord
11              Test
12              Test2
13              Word

Then the form load event might look like:
Private Sub Form_Load

    Dim rs as dao.recordset
    Dim intLoop as integer 
    Dim strCriteria as string

    on error goto ProcError
    'OpenRecordset for searching
    set rs = currentdb.openrecordset("SELECT Key_ID, KeyWord FROM yourOtherTable", dbOpenDynaset)

    'loop through the controls for the labels, search the recordset for the value of the KeyID.
    'If value is found, set the caption of the label
   for intLoop = 11 to 50
       strCriteria = "Key_ID = " & intLoop
       rs.Findfirst strcriteria
       if rs.NoMatch
           'I would normally hide the associated checkbox and label here
           me.Controls("lbl_ynKeyWord_" & format(intLoop, "00")).Caption = rs!KeyWord    
       End If

   On Error resume next
   set rs = nothing
   Exit Sub

   debug.print Err.Number, Err.Description
   Resume ProcExit

End Sub

Open in new window

EirmanChief Operations ManagerAuthor Commented:
Thanks Dale - I'll be away tomorrow (Friday) and will give your code a try on Saturday.
You might get a chance to look at it on a PC in the meantime.
EirmanChief Operations ManagerAuthor Commented:
I gave it a try this morning .... No Joy!
if rs.NoMatch always shows as red.
I get a compile error when I open the form.

I'm in no hurry with this. Have a look when you have time at your PC.

A few minor fixes
Pat - There is no repeating data in what is a simple database that could work with Rolodex system.
When you see the db, you'll see that normalization is not required.
Perhaps you need to refresh your understanding of normalization.

PS - I don't see any of the recommended code in the latest version of the app.
Dale FyeOwner, Developing Solutions LLCCommented:

if rs.NoMatch


if rs.NoMatch Then

in my original code.  Otherwise take a look at the sample database attached.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
EirmanChief Operations ManagerAuthor Commented:
That works really well Dale - thank you.
(I should have spotted the If - Then  typo)
@Pat - I read this and I don't think that my simple db
has any of the attributes of an un-normalised database.
I will however, bear it in mind for the future as it develops.
Dale FyeOwner, Developing Solutions LLCCommented:
Personally, I agree with Pat that any time you have numbered columns, you have a non-normalized table.  

Your yes/no columns should all be in a second table which contains the PK from your main table, KeyID columns and a Status column (Y/N).  You would then display these as a subform, and would only display as many columns as are defined int your KeyWord table (KeyID, and KeyWord).  This would allow you to only display 3 rows in the subform if you only had 3 keywords defined, or 40 values (KeyID range 11-50), or 100 if you had that many keywords.
EirmanChief Operations ManagerAuthor Commented:
Thanks Dale @ Pat,
I'll stick with what I have for the moment (out of necessity)
and experiment the suggested normalisation later.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Office

From novice to tech pro — start learning today.