Results 1 to 10 of 10
  1. #1
    4 Star Lounger
    Join Date
    Dec 2002
    Location
    Kitchener, Ontario, Canada
    Posts
    421
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Looking for code for controls (2000(SP3))

    I have a database I want to upgrade from being organization-specific to one that can be used with other organizations. Specifically, I want to loop through all the reports and forms and accomplish the following:
    1) Look at labels and textboxes
    2) if the item in question is a label AND it has the text I'm looking for (the organization name), convert the label to a textbox and put the control source as a reference to a field in a global info table e.g. =DLookUp("[ShopName]","tblGlobalInfo","[ID#]=1")
    3) if the item is a textbox, change the control source as above

    Can anyone point me to some straightforward code to adapt to this purpose either here or elsewhere? All the examples I can find work within a single form and I'm not sure how to adapt them to go through ALL the forms and reports. I think I can probably handle the control loop myself as there are lots of examples of that.

  2. #2
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Looking for code for controls (2000(SP3))

    The code to loop through all the report is relatively simple:

    Dim N As Integer

    For N = 0 To CurrentProject.AllForms.Count - 1
    docmd.openform currentproject.allforms(n), view:=acDesign
    ' your code here to loop thru controls

    docmd.close acform, currentproject.allforms(n)
    Next


    You will want to exclude from being processed your form that is running the code.

    Is this what you needed?
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  3. #3
    4 Star Lounger
    Join Date
    Dec 2002
    Location
    Kitchener, Ontario, Canada
    Posts
    421
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Looking for code for controls (2000(SP3))

    Thanks Mark. That is exactly what I was looking for. I won't even bother running it from a form. I'll just run it directly from a VBA module. My main goal is to save a lot of tedious opening, hunting, typing and checking.

    One assumes there is also a collection called AllReports?

  4. #4
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Looking for code for controls (2000(SP3))

    >>One assumes there is also a collection called AllReports?<<

    Yes there is. I forgot to mention that.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  5. #5
    4 Star Lounger
    Join Date
    Dec 2002
    Location
    Kitchener, Ontario, Canada
    Posts
    421
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Looking for code for controls (2000(SP3))

    Hi Mark:

    I've finally had a chance to play with this and I am getting an error message on the docmd.openform line of code. It is error 2498:"An expression you entered is the wrong data type for one of the arguments." I tried playing around with N being of variant rather than integer data type and I tried hardcoding a form name and can't get past this bit. Any suggestions?

  6. #6
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Looking for code for controls (2000(SP3))

    Can you find the exact line that is causing the problem?
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Looking for code for controls (2000(SP3))

    Add <code>.Name</code> after both occurrences of AllForms(n):
    <code>
    DoCmd.OpenForm CurrentProject.AllForms(n).Name, acDesign
    ...
    DoCmd.Close acForm, CurrentProject.AllForms(n).Name, acSaveYes</code>

  8. #8
    4 Star Lounger
    Join Date
    Dec 2002
    Location
    Kitchener, Ontario, Canada
    Posts
    421
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Looking for code for controls (2000(SP3))

    I got it to work. Here is the final code with some nonsense in the replacement for controlsource. It took me a while to come up with Screen.ActiveForm.Controls <img src=/S/hairout.gif border=0 alt=hairout width=31 height=23>

    Public Sub testCtl()
    Dim N As Integer
    Dim frm As Form
    Dim ctl As Control
    For N = 0 To CurrentProject.AllForms.Count - 1
    DoCmd.OpenForm CurrentProject.AllForms(N).Name, acDesign

    ' Enumerate Controls collection.
    For Each ctl In Screen.ActiveForm.Controls
    ' Check to see if control is text box.
    If ctl.ControlType = acTextBox Then
    ' Set control properties.
    With ctl
    If .ControlSource = "FirstName" Then .ControlSource = "=[XXXX]"
    End With
    End If
    Next ctl

    DoCmd.Close acForm, CurrentProject.AllForms(N).Name, acSaveYes
    Next

    End Sub

    Thanks to both of you. A good excercise in working in this more esoteric area.

  9. #9
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Looking for code for controls (2000(SP3))

    Here's a bit of more generic code that searches forms in an Access Database for Labels which contain specific text
    It then converts them to Text Boxes (protected) and replaces the text with a reference of your choice

    It isn't the definitive code BUT enough for others to be able to tinker with
    It is simple to convert to All Reports

    Very long piece of code (almost 8,000 characters!) moved to attachment by HansV
    Attached Files Attached Files
    Andrew

  10. #10
    4 Star Lounger
    Join Date
    Dec 2002
    Location
    Kitchener, Ontario, Canada
    Posts
    421
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Looking for code for controls (2000(SP3))

    Thanks for this. It will save me having to figure out the changing of labels into textboxes. It's so easy to do by hand. Just a nice little menu item!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •