Sometimes you want to give your Client the opportunity to 'create' their own labels. Name each Label, which is actually a Text Box, txt1Label thru txt9Label. The unassigned Labels are yellow. (The above subform is used in the Disciplinary Action Report.)
To keep the ones that are already filled in, locked from editing, place the below code on the Forms On_Current event procedure...
And just in case the Client wants to change the defaults, place this on the *permanent* Labels Double_Click event procedure, for this example txt2Label_DblClick event was used...
Let your Client customize their Form Labels
The difficult I do immediately, the impossible takes a little bit longer.
Dim LabelNumber As Long
LabelNumber = 1
Do Until LabelNumber > 9
If Not IsNull(Me("txt" & LabelNumber & "Label")) Then
Me("txt" & LabelNumber & "Label").Locked = True
Me("txt" & LabelNumber & "Label").Locked = False
Me("txt" & LabelNumber & "Label").BackColor = 13303807 'Yellow
LabelNumber = LabelNumber + 1
If LabelNumber > 9 Then 'Stops cycling after 9 resets to 1
LabelNumber = 1
If IsNull([txt2Label]) Then Exit Sub
Dim Msg, Style, Title, Response, MyString
Msg = "Did you want to change this Violation?"
Style = vbYesNo + vbQuestion + vbDefaultButton2
Title = "Violation Type"
Response = MsgBox(Msg, Style, Title)
If Response = vbYes Then
MyString = "Yes"
Me.txt2Label = ""
Me.txt2Label.Locked = False
Me.txt1Label.BackColor = 13303807 'Yellow
MyString = "No"
The code used is dependent on names of the Controls. Take care if changing those names!