Reference: many to one

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

0
alex
6/29/2007 5:58:32 PM
access 16762 articles. 3 followers. Follow

8 Replies
602 Views

Similar Articles

[PageSpeed] 7

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

"alex" <sql_aid@yahoo.com> wrote in message 
news:1183139912.576277.39740@o61g2000hsh.googlegroups.com...
>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
> 


0
Jeff
6/29/2007 6:35:39 PM
On Jun 29, 2:35 pm, "Jeff Boyce" <nonse...@nonsense.com> wrote:
> 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
>
> "alex" <sql_...@yahoo.com> wrote in message
>
> news:1183139912.576277.39740@o61g2000hsh.googlegroups.com...
>
>
>
> >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- Hide quoted text -
>
> - Show quoted text -

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.

0
alex
6/29/2007 6:50:24 PM
alex <sql_aid@yahoo.com> wrote:

>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?

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/
0
Tony
6/29/2007 7:24:52 PM
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

"alex" <sql_aid@yahoo.com> wrote in message 
news:1183143024.499517.172340@c77g2000hse.googlegroups.com...
> On Jun 29, 2:35 pm, "Jeff Boyce" <nonse...@nonsense.com> wrote:
>> 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
>>
>> "alex" <sql_...@yahoo.com> wrote in message
>>
>> news:1183139912.576277.39740@o61g2000hsh.googlegroups.com...
>>
>>
>>
>> >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- Hide quoted text -
>>
>> - Show quoted text -
>
> 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.
> 


0
Jeff
6/29/2007 7:26:35 PM
On Fri, 29 Jun 2007 11:50:24 -0700, alex <sql_aid@yahoo.com> wrote:

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

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]
0
John
6/29/2007 9:09:11 PM
"Tony Toews [MVP]" <ttoews@telusplanet.net> wrote:

>        If ctl.Tag = "Lock" Then

That should use an Instr should you have multiple tags for different purposes.

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/
0
Tony
6/29/2007 9:52:16 PM
alex <sql_aid@yahoo.com> wrote:

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

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/
0
Tony
7/1/2007 8:48:50 PM
On Jul 1, 4:48 pm, "Tony Toews [MVP]" <tto...@telusplanet.net> wrote:
> alex <sql_...@yahoo.com> wrote:
> >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.
>
> 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/

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

alex

0
alex
7/2/2007 10:08:42 AM
Reply:

Similar Artilces:

What OL2003 does after you clear many messages from Deleted Items
I find this annoying and am wondering if anyone else has noticed it. I use OL2003 with a PST (no Exchange) on XPSP2. My PST is usually not much over 100MB. When going into Deleted Items and clearing out, I don't know, on the order of at least 500 or 1000 messages, I notice a sudden amount of disk activity. I've used Sysinternal's Filemon to find that it's Outlook, presumably doing some kind of subtle behind-the-scenes compaction. (Note that I have AutoArchive disabled, so it's not that.) Problem is that this stealth compaction goes on for 10, 15, 20 minutes or more,...

How do I make two lines in one cell?
since you cant hit enter to make 2 lines in one cell how do you do it? Press alt + enter together. It's easy when you know how! :-) Alan P. "kpeters" <kpeters@discussions.microsoft.com> wrote in message news:CAEFD0EB-26F1-4AAB-9D4F-86DF4E013B9F@microsoft.com... > since you cant hit enter to make 2 lines in one cell how do you do it? if you mean that you want to add more text into the cell, then increase the row height and "wrap the text" "kpeters" <kpeters@discussions.microsoft.com> wrote in message news:CAEFD0EB-26F1-4AAB-9D4F-86DF...

Using icon sets with relative references
I am using Excel 2007 in the Vista OS. I am trying to use conditional formatting with an icon set in a column but Excel will not allow relative references. Example: A1=10 min B1=20 max C1=30 oh D1=a formula { =IF(C1=0,B1,(B1-C1)) } The condtional formatting of D1 would be: if the value showing in D1 is >B1 the cell would show a red 'X', if the value showing in D1 is <A1 the cell would show a green 'check mark', and if the value showing in D1 is between the values in A1 and B1 it would show a yellow 'exclamation point'. I can get the D1 ...

merging 3 spreadsheets into one based upon a single common data field
OK I hope you guys can help me out. Here is the scenario. I have 3 spreadsheets. The main spreadsheet I working with has 500 lines with a unique number string. The next two spreadsheets have about 12000 lines but also contain the same unique string as the first spreadsheet. I want to search the 2nd and 3rd spreadsheet using the first spreadsheets unique id number and then take the information from the 2nd and 3rd spreadsheet and then merge it into the first. Does that make since? Any help would be great. -- xchosen --------------------------------------------------------------------...

Office 2008
I have an iMac g4 and am planning to install Office 2008. Is there any way that I can install Office 2008, using the same product key, on a Macbook that I am planning to buy? (I won't need Office on the iMac after I purchase the Macbook). On 3/5/08 3:18 PM, in article ee8fdf3.-1@webcrossing.caR9absDaxw, "dcronin90@officeformac.com" <dcronin90@officeformac.com> wrote: > I have an iMac g4 and am planning to install Office 2008. Is there any way > that I can install Office 2008, using the same product key, on a Macbook that > I am planning to buy? (I won't need ...

macro to save worksheet using a cell reference
I'm working from a template and would like to save the speadsheet using a cell reference, or even better to a notepad using the a cell reference. Thank you!!! Posted via: http://www.ozgrid.com Excel Templates Training Add-ins. Free Excel Forum & Business Software how about... Sub SaveTest() Dim TheName TheName = Range("B2").Text Application.DisplayAlerts = False ActiveWorkbook.SaveAs _ Filename:="C:\My Documents\Desired Folder\" & TheName, _ FileFormat:=xlNormal, _ Password:="", _ WriteResPassword:="", _ ReadOnlyRecommended:=Fal...

Reference Footnotes
How do I enter a footnote at the bottom of a page? The footnotes are important--it's for a college internship assignment. Thanks! -- kasper kasper <kasper@discussions.microsoft.com> was very recently heard to utter: > How do I enter a footnote at the bottom of a page? The footnotes are > important--it's for a college internship assignment. Thanks! You have to create footnotes manually. -- Ed Bennett - MVP Microsoft Publisher ...

moving large amounts of data from one db to another
I have a table that has millions of rows of data in it, which then references another table with millions of referenced (matching by foreign key constraints)... I need to move this data from database A to a new database B, is there any way to do this and preserve the identity columns and their referential integrity?) Thanks! You can't have integrated RI across databases so that part is out. But you can certainly insert and keep the identities. Take a look at SET IDENTITY INSERT in BOL. -- Andrew J. Kelly SQL MVP Solid Quality Mentors "Smokey Grindel"...

2 POS as one Register
Is there a way two treat both my POS Registers as one. Have only one cash drawer and one Z Report. Is this possible? No. "G E" wrote: > Is there a way two treat both my POS Registers as one. > Have only one cash drawer and one Z Report. > Is this possible? GE, One drawer and printer - yes, use Windows printer drivers One Z-report - no -- = Get Secure! - www.microsoft.com/security You must be using Outlook Express or some other type of newsgroup reader to see and download the file attachment. If you are not using a reader, follow the link below to setup Outlook ...

Looping with more than one condition
I have a very large spreadsheet that loops through cells and updates them if they are blank. I have a new conditon to add to that where if the cell is blank and another is not blank then move to next cell. I'm a little stuck on how to write that part. Can I put an "And" prior to the Then? Dim mycell As Range For Each mycell In Range("TO_Test") If IsEmpty(mycell) Then mycell = "something" End If Next mycell -- Message posted via http://www.officekb.com Sub test2() Dim mycell As Range ' If TO_Range i...

to open a set of folders with one click
Is there a program that you can set the folders you need to open and click on one button it opens all in one mouse-click? It should have the ability to save mutlple sets. I don't want to save a lot of shortcuts and open by clicking these shortcuts. Thanks for the help, On 1/27/2010 5:42 PM, liu wrote: > Is there a program that you can set the folders you need to open and > click on one button it opens all in one mouse-click? It should have > the ability to save mutlple sets. I don't want to save a lot of > shortcuts and open by clicking these shortcuts. > &...

Help to set up multi options in one cell
hello I have two problems 1 i have a spread sheet and in one of the cells there can be different options. "A" "B" "C" i would like them all to come up different colors when i use the conditional format way the first one always comes u white "A" if i put any other letter in there it will work ok. help?? 2.with the same spreadsheet i would like to use 4 or 5 colors say with "A" "B" "C" "D" "E" "F" all to have different colors. i was told to use a micro. so if yo...

How do I link many cells to one particular cell?
I am trying to link multiple cells to one particular cell and can't seem to do it. I want the multiple cells not just to have the same information but be connected to the same cell. I want multiple cells to take the information from ex. cell D4. So everytime i change cell D4 every cell that is linked to it changes. Do I have to input =D4 into every single cell that I want to link or is there an easy way to mass link? You have to enter =D4 in each cell. Select all the cells you want to link, then type =D4 and hit CTRL-Enter to put the formula in each cell. In article <C7E824...

Help with an expression to calculate a profit from one of three fi
Using Access 2003 SP3 - in Forms I know I'm probably asking a very complex question, so a very big THANK YOU to whomever can help. I am doing a detailed inventory and need help with an expression in my form that will calculate my profit based on one of three fields. Only one field would have the end data. To clarify what I am working with: Most stock items come by the "unit" and contain multiple "subunits" which, in turn, contain smaller "pieces." However, some "units" only come with "subunits" and some only come as &quo...

Passing a reference to a textbox to a function
I wrote a generic module so I could reuse it. I pass a textbox to the function, and later on the function updates the value in the textbox that is passed into it. What I can't understand is that Me.txtPressIdLink is Null in Form_Current(). I am trying to pass a reference to the text box, not the Me.txtPressIdLink.value. I guess it uses the default (which is the value), but I want it to pass a reference to the txtPressIdLink control. Thanks. FORM CODE: Private Sub Form_Current() 'Init the Press Navigation variables Call init_PressNavigation(Me!pkJobID, Me.txtPressIdLink) ...

Copy Multiple Sheets Into One Sheet
Hi Folks - I track monthly sales in separate worksheets (Jan, Feb, Mar, etc.). At the end of the year, I need to analyze the monthly sales figures on a yearly basis. So, I create a new sheet and copy the individual monthly sheet data to the new sheet. This is a little time consuming. Is there a more efficient way to basically merge and append multiple sheets into one sheet? Thanks in advance. Michael Hi Michael See my site for a few examples http://www.rondebruin.nl/copy3.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Michael" <info@homekeyinc.com...

Get a reference to control from its hWnd
In .Net there is a function to retrieve a reference to a control from its hWnd (Control.FromHandle). Is there any way to do this in VB6? Thanks, Steve "Steve" <sredmyer@yahoo.com> wrote in message news:9723edc2-47e1-42ed-bb38-12e5d54ad729@h2g2000vbd.googlegroups.com... > In .Net there is a function to retrieve a reference to a control from > its hWnd (Control.FromHandle). Is there any way to do this in VB6? I don't believe there's a built-in function, but you can loop through all the control in Me.Controls and compare their hWnds. Throw in some...

Standard tool to change user profiles from one server to another in diff Orgs?
Hello, We're moving users from an old Ex5.5 server in one Exchange Organisation to a new Ex2003 server in another Organisation. The old server was only used for Public Folders with specific ACLs. We'd like to move the data and change all the user profiles over a weekend. What's the easiset way to automatically update all the user profiles to reflect the new server (+/- 300) using standard tools? The client is Outlook 2002. We have SMS installed, but I'd rather use a login script if possible. Thanks, - Alan. Try this: http://www.microsoft.com/downloads/details.aspx?Famil...

Can I calculate a field with 2 different filter criteria in one q
Hi, I have a data source table with a customer name field and various metrics fields, I'd like to sum metric "a" based on customer name being like "abc" and another sum of metric "a" where the customer name is NOT LIKE "abc" in one query, but I can't seem to figure this out, but, I'm a novice at this. Best I can figure is 2 different queries, then a 3rd query to bring the two together. Thanks -- Jim jimd wrote: >Hi, I have a data source table with a customer name field and various metrics >fields, I'd like to sum metric &...

references to old servers
In ADUC, I see references to old servers. I have Advanced Features checked, and I am looking under SYSTEM - RpcServices. There are 2 references to Arcserve backup in the format BrightStorEB@Oldserver1 and BrightStorEB@Oldserver2. What is the proper way to remove these references? -- Thanks! Howdie! On 06.04.2010 15:08, Saucer Man wrote: > In ADUC, I see references to old servers. I have Advanced Features checked, > and I am looking under SYSTEM - RpcServices. There are 2 references to > Arcserve backup in the format BrightStorEB@Oldserver1 and > Bright...

too many formats error message
I have several spreadsheets that are quite large. Recently, I have gotten the "too many cell formats" error message. Does this format limit apply to a particular worksheet, or the workbook as a whole? Also, how do I correct it? Once I get the message, I am unable to remove any formatting. Take a look here: XL: Error Message: Too Many Different Cell Formats http://support.microsoft.com/default.aspx?scid=kb;en-us;213904 You might also want to take a look here: http://google.com/groups?threadm=OxP9cgi1AHA.1572%40tkmsftngp02 In article <06b601c38c3f$f6f5fb90...

how many bytes?
a student asked me this about data storage...i read that a text field is a string data type that uses 10 bytes plus 2 bytes per character... does this mean that the size of the text field does not matter (five characters in a field uses 20 bytes, no matter if the field size is 5 or 50 or 255); or does the field size use all bytes and assigns empty values to all unused portions of the field (a two character field size uses 10 + 2 x 2 = 14, and a 200 character field uses 10 + 2 x 200 = 410, no matter how many actual characters are stored)? If the former is true, then why not make your ...

One Note-Vista OS--
I receive the following message "this operation has been cancelled due to restrictions in effect on this computer. Please contact your system administrator." All of my hyperlinks are disabled! I have tried several suggestions but am not am to fix the problem. I can not put my finger on any situation that would have caused it. I would appreciate any help that one would offer. One note is not too effective with out the use of hyperlinks... Dick Adams email: rsajr1@bellsouth.net Vista OS--Internet Explorer 8. -------- Original-Nachricht -------- &g...

Deleting Cells, but not ones with formulars
Hello, I have a large sheet that people enter data into each week. I want t be able to hightlight a large area of the sheet and delete the data i those selected cells, but not the formulars that are in some of th cells. Anyone know how I can do this? Any help apreciated! -- scriblesvur ----------------------------------------------------------------------- scriblesvurt's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2439 View this thread: http://www.excelforum.com/showthread.php?threadid=38001 This worked for me: Sub DeleteCells() Set rng = Range("...

E-mail can be read by Every one
Hi: We have SBS 2000. All Service Paks for Exchange and Win 2000 Server are current. I just found out that every one can read each other e-mail by opening other user's mail box in Outlook. I checked permissions on MailBox Store (First Storage Goup\mail Box Store) Everyone Group has all permissions except Full Control and Delete and allow inheritable permissions was checked. This is critica. Please assist. Many Thanks JM "JBM" <JBM@nowhere.com> wrote in message news:ugqEHcYxFHA.904@tk2msftngp13.phx.gbl... > Hi: > We have SBS 2000. All Service Paks for Exc...