asked on

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).
KEY_ID KEYWORD
11 English
12 Irish
13 Male
14 Female
Etc.
==================================================
How can this be done?
Would it best to use a text box beside the checkbox or the label attached to the checkbox?

Helen Feddema

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 Fye

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 Feddema

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.

accarch198.zip

PatHartman

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.

Eirman

ASKER

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.

Eirman

ASKER

I have attached a sample single database.
Would it be possible to update the brown labels instead of the control's captions?

Thanks
LabelChangeDemo.accdb

Dale Fye

Eirman,

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
       Else
           me.Controls("lbl_ynKeyWord_" & format(intLoop, "00")).Caption = rs!KeyWord    
       End If
   Next

ProcExit:
   On Error resume next
   rs.close
   set rs = nothing
   Exit Sub

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

End Sub

Open in new window

Eirman

ASKER

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.

Eirman

ASKER

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
LabelChangeDemo.accdb

SOLUTION

PatHartman

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER CERTIFIED SOLUTION

Dale Fye

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Eirman

ASKER

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 Fye

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.

Eirman

ASKER

Thanks Dale @ Pat,
I'll stick with what I have for the moment (out of necessity)
and experiment the suggested normalisation later.