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?
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?
Microsoft OfficeDatabasesMicrosoft Access
Last Comment
Eirman
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.
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.
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.
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 form]()
accarch198.zip
accarch198.zip
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.
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_IDThat 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.
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
Would it be possible to update the brown labels instead of the control's captions?
Thanks
LabelChangeDemo.accdb
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:
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
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.
You might get a chance to look at it on a PC in the meantime.
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
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
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
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.
(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.
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.
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.
ASKER
Thanks Dale @ Pat,
I'll stick with what I have for the moment (out of necessity)
and experiment the suggested normalisation later.
I'll stick with what I have for the moment (out of necessity)
and experiment the suggested normalisation later.