• Status: Solved
  • Priority: High
  • Security: Public
  • Views: 46
  • Last Modified:

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?
  • 6
  • 4
  • 2
  • +1
2 Solutions
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 FyeCommented:
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 formaccarch198.zip
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

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 FyeCommented:

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 FyeCommented:

if rs.NoMatch


if rs.NoMatch Then

in my original code.  Otherwise take a look at the sample database attached.
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 FyeCommented:
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.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

  • 6
  • 4
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now