access vba function get form caption

[SELIM ZAIRI]

hi, to all
i was wondering if some one got this problem before and if he had any
solution
i got a query:

SELECT MSysObjects.Nam e, MSysObjects.Typ e, GETFRMCAPTION([Name]) AS
Expr1
FROM MSysObjects
WHERE (((MSysObjects. Type)=-32768));

and this function to get form caption

Public Function GETFRMCAPTION(F RMNM As String) As String

test = "Forms" & "!" & FRMNM & "." & "Caption"
GETFRMCAPTION = Eval("Forms" & "!" & FRMNM & "." & "Caption")

End Function

but it will not work

the final result i want to get the form caption in the the field expr1
in the query to be added later in the security form of the application

i will be more than thankfull if any one could help me in this problem

many thanks and regards in advance

[MacDermott]

Re: access vba function get form caption

Please recall that the Forms collection includes only those forms which are
open at the time that the code is run.
Therefore, unless you first open all the forms, this code will not return
their captions.
I don't think that the .Caption property is available without opening the
forms, anyhow.

I'm not sure what you want this information for.
Although far from perfect, the built-in security available for Access is
most certainly superior to most anything you can build yourself.

HTH
- Turtle

"SELIM ZAIRI" <SELIMZAIRI@HOT MAIL.COM> wrote in message
news:2db27eb2.0 403070250.9ec1c 1f@posting.goog le.com...[color=blue]
> hi, to all
> i was wondering if some one got this problem before and if he had any
> solution
> i got a query:
>
> SELECT MSysObjects.Nam e, MSysObjects.Typ e, GETFRMCAPTION([Name]) AS
> Expr1
> FROM MSysObjects
> WHERE (((MSysObjects. Type)=-32768));
>
> and this function to get form caption
>
> Public Function GETFRMCAPTION(F RMNM As String) As String
>
> test = "Forms" & "!" & FRMNM & "." & "Caption"
> GETFRMCAPTION = Eval("Forms" & "!" & FRMNM & "." & "Caption")
>
> End Function
>
> but it will not work
>
> the final result i want to get the form caption in the the field expr1
> in the query to be added later in the security form of the application
>
> i will be more than thankfull if any one could help me in this problem
>
> many thanks and regards in advance[/color]

[Trevor Best]

Re: access vba function get form caption

SELIM ZAIRI wrote:
[color=blue]
> hi, to all
> i was wondering if some one got this problem before and if he had any
> solution
> i got a query:
>
> SELECT MSysObjects.Nam e, MSysObjects.Typ e, GETFRMCAPTION([Name]) AS
> Expr1
> FROM MSysObjects
> WHERE (((MSysObjects. Type)=-32768));
>
> and this function to get form caption
>
> Public Function GETFRMCAPTION(F RMNM As String) As String
>
> test = "Forms" & "!" & FRMNM & "." & "Caption"
> GETFRMCAPTION = Eval("Forms" & "!" & FRMNM & "." & "Caption")
>
> End Function
>
> but it will not work
>
> the final result i want to get the form caption in the the field expr1
> in the query to be added later in the security form of the application
>
> i will be more than thankfull if any one could help me in this problem
>
> many thanks and regards in advance[/color]

The form has to be open to get it's caption.

Try:
Public Function GETFRMCAPTION(F RMNM As String) As String
DoCmd.OpenForm FRMNM, acDesign
GETFRMCAPTION = Forms(GETFRMCAP TION).Caption
Docmd.Close FRMNM, acSaveNo
End Function

Bit messy when running.
--
But why is the Rum gone?

[Douglas J. Steele]

Re: access vba function get form caption

"Trevor Best" <nospam@localho st> wrote in message
news:404b0eb1$0 $21236$afc38c87 @auth.uk.news.e asynet.net...[color=blue]
>
> The form has to be open to get it's caption.
>
> Try:
> Public Function GETFRMCAPTION(F RMNM As String) As String
> DoCmd.OpenForm FRMNM, acDesign
> GETFRMCAPTION = Forms(GETFRMCAP TION).Caption
> Docmd.Close FRMNM, acSaveNo
> End Function
>
> Bit messy when running.[/color]

It'll be a little less messy if you open each form as hidden:

Public Function GETFRMCAPTION(F RMNM As String) As String
DoCmd.OpenForm FRMNM, acDesign, , , , acHidden
GETFRMCAPTION = Forms(GETFRMCAP TION).Caption
Docmd.Close FRMNM, acSaveNo
End Function


--
Doug Steele, Microsoft Access MVP

http://I.Am/DougSteele

(No private e-mails, please)

[Trevor Best]

Re: access vba function get form caption

Douglas J. Steele wrote:
[color=blue]
> "Trevor Best" <nospam@localho st> wrote in message
> news:404b0eb1$0 $21236$afc38c87 @auth.uk.news.e asynet.net...
>[color=green]
>>The form has to be open to get it's caption.
>>
>>Try:
>>Public Function GETFRMCAPTION(F RMNM As String) As String
>> DoCmd.OpenForm FRMNM, acDesign
>> GETFRMCAPTION = Forms(GETFRMCAP TION).Caption
>> Docmd.Close FRMNM, acSaveNo
>>End Function
>>
>>Bit messy when running.[/color]
>
>
> It'll be a little less messy if you open each form as hidden:
>
> Public Function GETFRMCAPTION(F RMNM As String) As String
> DoCmd.OpenForm FRMNM, acDesign, , , , acHidden
> GETFRMCAPTION = Forms(GETFRMCAP TION).Caption
> Docmd.Close FRMNM, acSaveNo
> End Function
>
>[/color]
Well spotted, now pay attention as next time I will test you without
warning :-)

And of course the close command should be:
Docmd.Close acForm, FRMNM, acSaveNo

--
But why is the Rum gone?

[rkc]

Re: access vba function get form caption


"Douglas J. Steele" <NOSPAM_djsteel e@NOSPAM_canada .com> wrote in message
news:g_F2c.1840 31$Qg7.63431@ne ws04.bloor.is.n et.cable.rogers .com...[color=blue]
> "Trevor Best" <nospam@localho st> wrote in message
> news:404b0eb1$0 $21236$afc38c87 @auth.uk.news.e asynet.net...[color=green]
> >
> > The form has to be open to get it's caption.
> >
> > Try:
> > Public Function GETFRMCAPTION(F RMNM As String) As String
> > DoCmd.OpenForm FRMNM, acDesign
> > GETFRMCAPTION = Forms(GETFRMCAP TION).Caption
> > Docmd.Close FRMNM, acSaveNo
> > End Function[/color][/color]

This function is in dire need of a "air code" warning.

GETFRMCAPTION = Forms(FRMNM).Ca ption
DoCmd.Close acForm, FRMNM, acSaveNo

[Douglas J. Steele]

Re: access vba function get form caption

"rkc" <rkc@yabba.dabb a.do.rochester. rr.bomb> wrote in message
news:41H2c.1356 70$%72.57943@tw ister.nyroc.rr. com...[color=blue]
>
> "Douglas J. Steele" <NOSPAM_djsteel e@NOSPAM_canada .com> wrote in message
> news:g_F2c.1840 31$Qg7.63431@ne ws04.bloor.is.n et.cable.rogers .com...[color=green]
> > "Trevor Best" <nospam@localho st> wrote in message
> > news:404b0eb1$0 $21236$afc38c87 @auth.uk.news.e asynet.net...[color=darkred]
> > >
> > > The form has to be open to get it's caption.
> > >
> > > Try:
> > > Public Function GETFRMCAPTION(F RMNM As String) As String
> > > DoCmd.OpenForm FRMNM, acDesign
> > > GETFRMCAPTION = Forms(GETFRMCAP TION).Caption
> > > Docmd.Close FRMNM, acSaveNo
> > > End Function[/color][/color]
>
> This function is in dire need of a "air code" warning.
>
> GETFRMCAPTION = Forms(FRMNM).Ca ption
> DoCmd.Close acForm, FRMNM, acSaveNo[/color]

Ironically, I copied my change to the OpenForm statement from a tested
function. I just didn't bother looking at the rest of the code!


--
Doug Steele, Microsoft Access MVP

http://I.Am/DougSteele

(No private e-mails, please)

[David W. Fenton]

Re: access vba function get form caption

"rkc" <rkc@yabba.dabb a.do.rochester. rr.bomb> wrote in
news:41H2c.1356 70$%72.57943@tw ister.nyroc.rr. com:
[color=blue]
> "Douglas J. Steele" <NOSPAM_djsteel e@NOSPAM_canada .com> wrote in
> message
> news:g_F2c.1840 31$Qg7.63431@ne ws04.bloor.is.n et.cable.rogers .com...[color=green]
>> "Trevor Best" <nospam@localho st> wrote in message
>> news:404b0eb1$0 $21236$afc38c87 @auth.uk.news.e asynet.net...[color=darkred]
>> >
>> > The form has to be open to get it's caption.
>> >
>> > Try:
>> > Public Function GETFRMCAPTION(F RMNM As String) As String
>> > DoCmd.OpenForm FRMNM, acDesign
>> > GETFRMCAPTION = Forms(GETFRMCAP TION).Caption
>> > Docmd.Close FRMNM, acSaveNo
>> > End Function[/color][/color]
>
> This function is in dire need of a "air code" warning.
>
> GETFRMCAPTION = Forms(FRMNM).Ca ption
> DoCmd.Close acForm, FRMNM, acSaveNo[/color]

Not only that, but it assumes the form is not open when it runs. I'd
be pretty annoyed if I had the form open and it suddenly
disappeared.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[Trevor Best]

Re: access vba function get form caption

David W. Fenton wrote:[color=blue]
> "rkc" <rkc@yabba.dabb a.do.rochester. rr.bomb> wrote in
> news:41H2c.1356 70$%72.57943@tw ister.nyroc.rr. com:
>
>[color=green]
>>"Douglas J. Steele" <NOSPAM_djsteel e@NOSPAM_canada .com> wrote in
>>message
>>news:g_F2c.18 4031$Qg7.63431@ news04.bloor.is .net.cable.roge rs.com...
>>[color=darkred]
>>>"Trevor Best" <nospam@localho st> wrote in message
>>>news:404b0eb 1$0$21236$afc38 c87@auth.uk.new s.easynet.net.. .
>>>
>>>>The form has to be open to get it's caption.
>>>>
>>>>Try:
>>>>Public Function GETFRMCAPTION(F RMNM As String) As String
>>>> DoCmd.OpenForm FRMNM, acDesign
>>>> GETFRMCAPTION = Forms(GETFRMCAP TION).Caption
>>>> Docmd.Close FRMNM, acSaveNo
>>>>End Function[/color]
>>
>>This function is in dire need of a "air code" warning.
>>
>>GETFRMCAPTI ON = Forms(FRMNM).Ca ption
>>DoCmd.Close acForm, FRMNM, acSaveNo[/color]
>
>
> Not only that, but it assumes the form is not open when it runs. I'd
> be pretty annoyed if I had the form open and it suddenly
> disappeared.
>[/color]
Oh well, I'm sorry I didn't spend at least a couple of hours testing the
function under all possible circumstances to answer a fecking newsgroup
post. Sheesh, I dunno why I bother sometimes.

--
But why is the Rum gone?

[rkc]

Re: access vba function get form caption


"Trevor Best" <nospam@localho st> wrote in message
news:404b881a$0 $15206$afc38c87 @auth.uk.news.e asynet.net...[color=blue]
> David W. Fenton wrote:[color=green]
> > "rkc" <rkc@yabba.dabb a.do.rochester. rr.bomb> wrote in
> > news:41H2c.1356 70$%72.57943@tw ister.nyroc.rr. com:
> >
> >[color=darkred]
> >>"Douglas J. Steele" <NOSPAM_djsteel e@NOSPAM_canada .com> wrote in
> >>message
> >>news:g_F2c.18 4031$Qg7.63431@ news04.bloor.is .net.cable.roge rs.com...
> >>
> >>>"Trevor Best" <nospam@localho st> wrote in message
> >>>news:404b0eb 1$0$21236$afc38 c87@auth.uk.new s.easynet.net.. .
> >>>
> >>>>The form has to be open to get it's caption.
> >>>>
> >>>>Try:
> >>>>Public Function GETFRMCAPTION(F RMNM As String) As String
> >>>> DoCmd.OpenForm FRMNM, acDesign
> >>>> GETFRMCAPTION = Forms(GETFRMCAP TION).Caption
> >>>> Docmd.Close FRMNM, acSaveNo
> >>>>End Function
> >>
> >>This function is in dire need of a "air code" warning.
> >>
> >>GETFRMCAPTI ON = Forms(FRMNM).Ca ption
> >>DoCmd.Close acForm, FRMNM, acSaveNo[/color]
> >
> >
> > Not only that, but it assumes the form is not open when it runs. I'd
> > be pretty annoyed if I had the form open and it suddenly
> > disappeared.
> >[/color]
> Oh well, I'm sorry I didn't spend at least a couple of hours testing the
> function under all possible circumstances to answer a fecking newsgroup
> post. Sheesh, I dunno why I bother sometimes.[/color]

Was so touchy?

I only replied after an expert corrected an expert and the function
still had problems even running.

DWF's point is a real killer though, isn't it?

[David W. Fenton]

Re: access vba function get form caption

"rkc" <rkc@yabba.dabb a.do.rochester. rr.bomb> wrote in
news:H6M2c.1371 82$%72.77031@tw ister.nyroc.rr. com:
[color=blue]
>
> "Trevor Best" <nospam@localho st> wrote in message
> news:404b881a$0 $15206$afc38c87 @auth.uk.news.e asynet.net...[color=green]
>> David W. Fenton wrote:[color=darkred]
>> > "rkc" <rkc@yabba.dabb a.do.rochester. rr.bomb> wrote in
>> > news:41H2c.1356 70$%72.57943@tw ister.nyroc.rr. com:
>> >
>> >
>> >>"Douglas J. Steele" <NOSPAM_djsteel e@NOSPAM_canada .com> wrote
>> >>in message
>> >>news:g_F2c.18 4031$Qg7.63431[/color][/color][/color]
@news04.bloor.i s.net.cable.rog ers.com[color=blue][color=green][color=darkred]
>> >>...
>> >>
>> >>>"Trevor Best" <nospam@localho st> wrote in message
>> >>>news:404b0eb 1$0$21236$afc38 c87@auth.uk.new s.easynet.net.. .
>> >>>
>> >>>>The form has to be open to get it's caption.
>> >>>>
>> >>>>Try:
>> >>>>Public Function GETFRMCAPTION(F RMNM As String) As String
>> >>>> DoCmd.OpenForm FRMNM, acDesign
>> >>>> GETFRMCAPTION = Forms(GETFRMCAP TION).Caption
>> >>>> Docmd.Close FRMNM, acSaveNo
>> >>>>End Function
>> >>
>> >>This function is in dire need of a "air code" warning.
>> >>
>> >>GETFRMCAPTI ON = Forms(FRMNM).Ca ption
>> >>DoCmd.Close acForm, FRMNM, acSaveNo
>> >
>> >
>> > Not only that, but it assumes the form is not open when it
>> > runs. I'd be pretty annoyed if I had the form open and it
>> > suddenly disappeared.
>> >[/color]
>> Oh well, I'm sorry I didn't spend at least a couple of hours
>> testing the function under all possible circumstances to answer a
>> fecking newsgroup post. Sheesh, I dunno why I bother sometimes.[/color]
>
> Was so touchy?
>
> I only replied after an expert corrected an expert and the
> function still had problems even running.
>
> DWF's point is a real killer though, isn't it?[/color]

I don't know if it's a killer or not, but it does suggest that
perhaps the proper way to accomplish this is through the Documents
collection (assuming A97) or via whatever the name of the new
collection is in A2K+.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[Trevor Best]

Re: access vba function get form caption

David W. Fenton wrote:
[color=blue]
> "rkc" <rkc@yabba.dabb a.do.rochester. rr.bomb> wrote in
> news:H6M2c.1371 82$%72.77031@tw ister.nyroc.rr. com:
>
>[color=green]
>>"Trevor Best" <nospam@localho st> wrote in message
>>news:404b881a $0$15206$afc38c 87@auth.uk.news .easynet.net...
>>[color=darkred]
>>>David W. Fenton wrote:
>>>
>>>>"rkc" <rkc@yabba.dabb a.do.rochester. rr.bomb> wrote in
>>>>news:41H2c. 135670$%72.5794 3@twister.nyroc .rr.com:
>>>>
>>>>
>>>>
>>>>>"Douglas J. Steele" <NOSPAM_djsteel e@NOSPAM_canada .com> wrote
>>>>>in message
>>>>>news:g_F2c .184031$Qg7.634 31[/color][/color]
>
> @news04.bloor.i s.net.cable.rog ers.com
>[color=green][color=darkred]
>>>>>...
>>>>>
>>>>>
>>>>>>"Trevor Best" <nospam@localho st> wrote in message
>>>>>>news:404b 0eb1$0$21236$af c38c87@auth.uk. news.easynet.ne t...
>>>>>>
>>>>>>
>>>>>>>The form has to be open to get it's caption.
>>>>>>>
>>>>>>>Try:
>>>>>>>Public Function GETFRMCAPTION(F RMNM As String) As String
>>>>>>> DoCmd.OpenForm FRMNM, acDesign
>>>>>>> GETFRMCAPTION = Forms(GETFRMCAP TION).Caption
>>>>>>> Docmd.Close FRMNM, acSaveNo
>>>>>>>End Function
>>>>>
>>>>>This function is in dire need of a "air code" warning.
>>>>>
>>>>>GETFRMCAPT ION = Forms(FRMNM).Ca ption
>>>>>DoCmd.Clos e acForm, FRMNM, acSaveNo
>>>>
>>>>
>>>>Not only that, but it assumes the form is not open when it
>>>>runs. I'd be pretty annoyed if I had the form open and it
>>>>suddenly disappeared.
>>>>
>>>
>>>Oh well, I'm sorry I didn't spend at least a couple of hours
>>>testing the function under all possible circumstances to answer a
>>>fecking newsgroup post. Sheesh, I dunno why I bother sometimes.[/color]
>>
>>Was so touchy?
>>
>>I only replied after an expert corrected an expert and the
>>function still had problems even running.
>>
>>DWF's point is a real killer though, isn't it?[/color]
>
>
> I don't know if it's a killer or not, but it does suggest that
> perhaps the proper way to accomplish this is through the Documents
> collection (assuming A97) or via whatever the name of the new
> collection is in A2K+.
>[/color]
Same as 97 in A2K+ and yes you're right about using the collection and
not undocumented MSys tables and had thought about that at the time but
I wasn't in the mood for picking at the OP's methods :-).

--
But why is the Rum gone?