Reference: many to one

A

alex

I have a form in which users enter information...I keep all controls
locked until the user hits a command button to unlock particular bound
controls.

This works really well, but becomes cumbersome to code when new
controls are added; e.g., adding the new control to the several event
procedures attached to the form.

Can anyone tell me how to refer to a group of controls once, e.g.,
give them a name; and then only have to refer to that name in the
future?

alex
 
J

Jeff Boyce

Alex

I'm having trouble visualizing your situation.

It sounds like you are describing a form that is being regularly modified.
If the underlying data is well-normalized, you would not need to be adding
fields, so I'm not sure why you would need to be adding controls on your
form.

Can you provide a bit more explanation of "why", not "how"?

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
A

alex

Alex

I'm having trouble visualizing your situation.

It sounds like you are describing a form that is being regularly modified.
If the underlying data is well-normalized, you would not need to be adding
fields, so I'm not sure why you would need to be adding controls on your
form.

Can you provide a bit more explanation of "why", not "how"?

Regards

Jeff Boyce
Microsoft Office/Access MVP









- Show quoted text -
Click to expand...

Thanks for responding Jeff...
I add controls from time to time beacuse of changes to what's being
captured. Sometimes mgt decides it wants to capture information that
wasn't thought of during design time...regardless, I need to be able
to meet their expectations.

What I'm specifically talking about is naming controls in VBA.
E.g., if I want to unlock 10 controls in on click event of a command
button, I name all of them in my code.
I'm wondering if you can list all 10 somewhere, give them a group
name, and then refer to that name everytime you wanted to call the
controls in an event procedure. Even if the data are well normalized,
I need to call every control in 4 or 5 different event procedures,
e.g., after update, on current, on click, etc. If I could group them,
and then refer to the group name, that would save time.

Does that sound strange? It seems like it would save a lot of time.
 
T

Tony Toews [MVP]

alex said:
Can anyone tell me how to refer to a group of controls once, e.g.,
give them a name; and then only have to refer to that name in the
future?
Click to expand...

I've done this myself a number of times in the past. You want to use the Tag
property of a control. Every control that should be locked will have a value in
there such a "Lock"

Then in the Open event you pass in the OpenArgs from the calling form telling the
form if this is a new record.

If Me.OpenArgs = "New" Then
Call EnOrDis_AbleControlsOnForms(Me, tglLocked, True)
Else
Call EnOrDis_AbleControlsOnForms(Me, tglLocked, False)
End If

I also added a Lock/Unlock command button which has the following code

Call EnOrDis_AbleControlsOnForms(Me, tglLocked, tglLocked.Value)

This routine is in turn in a public module. It's a generic routine that will work
on any form.

Public Sub EnOrDis_AbleControlsOnForms(frm As Form, tgl As Control, _
Optional Override As Boolean)
' Enable & unlock or disable and lock controls as required. Controls must
; have Lock in the tag
' tgl is assumed to be a toggle control. Note that the caption will be overwritten
' If Override value is true then the field will be unlocked

Dim ctl As Control, ctlsbf As Control
Dim Locked As Boolean

On Error GoTo tagError

If Not IsMissing(Override) Then
Locked = Override
Else
Locked = tgl.Value
End If

For Each ctl In frm.Controls
If ctl.Tag = "Lock" Then
ctl.Enabled = Locked
ctl.Locked = Not Locked
End If
If ctl.ControlType = acSubform Then
For Each ctlsbf In ctl.Form.Controls
If ctlsbf.Tag = "Lock" Then
ctlsbf.Enabled = Locked
ctlsbf.Locked = Not Locked
End If
Next ctlsbf
End If
Next ctl

If Locked Then
tgl.Caption = "Unlocked"
Else
tgl.Caption = "Locked"
End If

On Error GoTo 0
Exit Sub

tagError:

Select Case Err.Number
' This message happens on a subform control for unknown reasons
' something to do with the subform <shrug>
Case 2164 ' You can't disable a control while it has the focus.
' ignore
Resume Next
Case Else
MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure
tglLocked_Click of VBA Document Form_Transaction Header"
End Select
Exit Sub
Resume

End Sub

And thanks for reminding me that I should create a web page on this topic.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
 
J

Jeff Boyce

Alex

One approach that might work for you would be to use the controls' "Tag"
property. You could use a single groupname (or a string of groupnames) in
that property and check it in your code to see if what you are doing applies
to that control.

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
J

John W. Vinson

I'm wondering if you can list all 10 somewhere, give them a group
name, and then refer to that name everytime you wanted to call the
controls in an event procedure. Even if the data are well normalized,
I need to call every control in 4 or 5 different event procedures,
e.g., after update, on current, on click, etc. If I could group them,
and then refer to the group name, that would save time.
Click to expand...

One way to do this is to store the name of the group (or groups, if you want
to get fancy) in each control's Tag property. It's a text string that you can
use freely.

Your code could loop through the Controls collection and change the properties
of every control with a particular value in Tag.

John W. Vinson [MVP]
 
T

Tony Toews [MVP]

alex said:
This works really well, but becomes cumbersome to code when new
controls are added; e.g., adding the new control to the several event
procedures attached to the form.
Click to expand...

I decided to create a web page on this topic:

Locking fields on a form in Microsoft Access
http://www.granite.ab.ca/access/locking_fields_on_a_form.htm

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
 
A

alex

I decided to create a web page on this topic:

Locking fields on a form in Microsoft Accesshttp://www.granite.ab.ca/access/locking_fields_on_a_form.htm

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems athttp://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog -http://msmvps.com/blogs/access/
Click to expand...

Thank you Tony, Jeff, and John for your help. I really appreciate it.

alex
 
Top