Changeable form labels

  • Thread starter Thread starter Phil
  • Start date Start date
P

Phil

HI,

I asked this several days ago and still don't have a
solution. Maybe there isn't one.

I have a form with about 25 fields on it all of which
comes from one table. Five of the fileds can be used for
whatever the user wants and I call
them "special1", "special2", etc. I want the user to be
able to change the labels on the form for those 5 fields.
For example if they want to store Locker Numbers in
special1 the label on the form would say "Locker Number".
Is this possible? Can the 5 labels be stored in a table
and be used as the "record source" in a text box or is
there some other way this can be done? Everything I've
tried has not worked. Of course, I don't want the user to
go into design view.

Thanks,
Phil
 
Hi Phil,

I think you've provided one solution. I would store the captions in the
table as it is probably the simplest way to persist the data. When your
form loads, have some code that reads the values in the table and assign
them to the labels. You will need to loop through the records and assign
the field values to the caption property of the labels:

Me.Special1.Caption = rs.Fields("Special1")

If you need help with the looping, post again.

Jamie
 
One other thing...When you allow users to name fields, you make an
assumption about the data type they require. A string may do in many cases,
but not always. A user could name (and use) a field inappropriately for the
supported data type, which will not serve them, or you, well at all. You
may be aware of this already but I thought I'd raise it just in case.


Jamie :o)
 
HI,

I asked this several days ago and still don't have a
solution. Maybe there isn't one.

I have a form with about 25 fields on it all of which
comes from one table. Five of the fileds can be used for
whatever the user wants and I call
them "special1", "special2", etc. I want the user to be
able to change the labels on the form for those 5 fields.
For example if they want to store Locker Numbers in
special1 the label on the form would say "Locker Number".
Is this possible? Can the 5 labels be stored in a table
and be used as the "record source" in a text box or is
there some other way this can be done? Everything I've
tried has not worked. Of course, I don't want the user to
go into design view.

Thanks,
Phil
Click to expand...

This is re-assembled from several previous posts of mine.
I think it should work as written.

Labels bound to a text control do not have an Event property.
If your labels for these 5 controls are bound to their control, cut
them. Then paste them back into the detail section.
They should now have an Event property.
Labels placed on the form directly from the tool box do have an Event
property and nothing else need be done.

*** Name these labels with a number at the end., i.e. Label1, Label2,
Label3, etc. They should be consecutive (but they don't have to start
at 1).
You only need to do this for those labels that you want the user to be
able to change. ***

Add a new table to the database.
FieldName Datatype
LabelName Text Indexed (No Duplicates) PrimeKey
CaptionText Text
Name this table "tblCaptions"

Have the LabelName field store the NAME of the label (i.e. Label1,
Label2, Label3, etc.).
For now, enter the starting names of the labels in the LabelName
field, and their starting captions in the CaptionText field.
(this need only be done once.)

Now the table has data like:
CaptionText LabelName
Transportation Label1
Employment Label2
Sales Label3
etc.

Next add a new Sub Procedure within the Form's code sheet:
' Watch the line breaks on the longer lines.

Public Sub NewCaptions(lblName As String)
Dim strNewLabel As String
strNewLabel = InputBox("New Text", "Caption change")
Dim L As Label
Set L = Me(lblName)
L.Caption = strNewLabel
DoCmd.SetWarnings False
DoCmd.RunSQL "Update tblCaptions Set CaptionText = '" &
strNewLabel & "' Where LabelName = '" & L.Name & "';"
DoCmd.SetWarnings True
End Sub
============

Then code EACH label's Double-click event:

NewCaptions "Label1"

Note.. Change the Label1 above to whatever the label's name is.
Make sure it's within Quotation marks.

To return the latest captions when opening the form:
Code the Form's Load event:

Dim intX As Integer
Dim L As Label
For intX = 1 To DCount("*", "tblCaptions")
Set L = Me("Label" & intX)
L.Caption = DLookup("[CaptionText]", "tblSaveCaption",
"[LabelName] = '" & L.Name & "'")
Next intX
========
' Note If the LabelNames starts at a different number, change the
above code as needed, i.e. For intX = 8 to DCount(etc.) + 7

I think that should be all you need.
 
Thanks Jamie,

I guess I do need some help with the looping and I must
confess that I don't understand the right side of your
code. What is rs.Fields("Special1")?

Lets say my table is called "Speical" and the table field
name is "Label" and the there are the 5 records. My label
names on the form are called lblLabel1, lblLabel2, etc...

What would the code be?

I really appreciate your help.

Thanks,
Phil
 
Hi Phil,

This code goes into the Form_Load event of the form that contains the labels
whose captions you want to set. The name of the table is tblLabels. It has
2 fields; lblName (primary key) and LabelCaption .

What you are doing is checking to see if the name of the field in the table
matches the name of the control (label in this case) on your form. If it
does you assign the LabelCaption field in your table to the controls caption
property. I have created and tested this in A2K and can email you the db if
it will help.

Private Sub Form_Load()
On Error GoTo Err_Form_Load
Dim rs As DAO.Recordset
Dim db As DAO.database
Dim ctl As Control

Set db = CurrentDb
Set rs = db.OpenRecordset("tblLabels")
'Assuming all the controls are in the Detail section of the form
'Loop through each control. For each control you find, test if the
'control name is the same as any of the names in your table. If so,
'set the controls caption to be the same as the LabelCaption field
'from your table!
For Each ctl In Me.Detail.Controls
rs.MoveFirst
Do
If (ctl.ControlType = acLabel And ctl.Name =
rs.Fields("LabelName")) Then
ctl.Caption = rs.Fields("LabelCaption")
End If
If Not rs.EOF Then 'Test to ensure there are more records or
you'll get an error
rs.MoveNext
Else
Exit Do
End If
Loop Until rs.EOF
Next ctl

'Clean up by closing what you opened
rs.Close
Set rs = Nothing
db.Close
Set db = Nothing
Set ctl = Nothing

Exit_Form_Load:
Exit Sub

Err_Form_Load:
MsgBox Err.Number & ": " & Err.Description
Exit Sub

End Sub


Jamie
 
Hi again Jamie,

I created the table and added the code. When I open the
form I get the error "Compile error - user-defined type
not defined" on the first Dim statement (Dim rs As
DAO.Recordset). Is there something I need to change? I'm
also using A2K.

Thanks,
Phil
 
Hi Jamie,

Thanks for all your help. I finally got it to work. I
had to go into references and select DAO. Once I did that
things all came together. It works slick and just what I
wanted.

Thanks again, your code was great.
Phil
 
C'mon now Phil you'll give me a fat head ;o) Glad it was useful. You did
well to work out the reference error, sorry I forgot to mention it.


Jamie :o)
 
Back
Top