Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Apr 2001
    Location
    Warrington, Cheshire
    Posts
    355
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Referring to a label caption in a query (Access 97 sr2)

    As the title says - I have a query which returns the form names contained in a db.
    So basically, from my database collection (MSysObjects) I'm returning all forms in the database and I want the description equivalent of each form.
    The description for each form is a caption to the forms label lblDescription.
    By doing it this way I can use a drop down on a form which will allow the user to select the form and view it's description before opening it.
    I figured I'd be using a function as one field of the query but was unable to return the label caption.
    Any suggestions?

    TIA
    Alan
    Cheshire
    UK

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

    Re: Referring to a label caption in a query (Access 97 sr2)

    To retrieve the caption of a label, you'd have to use VBA code to open the form in design view unless it is already open, get the caption, and close it again if necessary. This is not very efficient, and you'd have to very careful not to interfere with the normal operation of the database.

    Instead, I would create a table with two fields: FormName and FormDescription. Use this as row source for the combo box. The advantage is that it is very easy to work with, not needing any code. The disadvantage is that the table is not dynamic, you'll have to keep it up to date yourself.

  3. #3
    5 Star Lounger
    Join Date
    Apr 2003
    Location
    Hampshire, United Kingdom
    Posts
    602
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Referring to a label caption in a query (Acces

    <P ID="edit" class=small>(Edited by Waggers on 13-Jan-06 13:07. Sorry, I took ages to compose this and posted it 10 minutes after HansV!)</P>The SQL <!t>[Forms]<!/t>!<!t>[(form name)]<!/t>!<!t>[lblDescription]<!/t>.<!t>[Caption]<!/t> will only work for open forms.

    What I would do is set up a new table, with two columns ("Form" and "Description"), and change the description labels to text boxes, setting their control source to =DLookUp("Description","(Name of new table)","Form='" & [Name] & "'"). Obviously you would need to populate the table with the names and descriptions of your forms.
    Waggers
    If at first you do succeed, you&#39;ve probably missed something.

  4. #4
    3 Star Lounger
    Join Date
    Apr 2001
    Location
    Warrington, Cheshire
    Posts
    355
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Referring to a label caption in a query (Acces

    Thanks Guys,
    The table wth two columns was my last resort really.
    I was probably trying to be futuristic beyond the current capabilities - I'll add it to my MS wish list for future apps.

    with respect to the text bax changing to a dlookup value - kind of defeats my object really because I was looking to just have the developer change the description to the form as and when it was edited, and then the table would automatically contain the uptodate descrip.

    Thanks anyway chaps - and have a nice weekend. It's poets day today so just half hour to go.

    Alan
    Cheshire
    UK

  5. #5
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Referring to a label caption in a query (Acces

    I have demos called "DisplayNames" in both 97 and 2000 versions that might give you some ideas. They allow the user to change the description of a form from the application interface. You can find them at Rogers Access Library if you're interested. If you're trying to keep some kind of log of changes in the description property of the form, it's a bad idea. I think the problem is that there are multiple "descriptions" for objects in Access.
    Charlotte

Posting Permissions

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