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?

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.

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.

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.

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.