Connecting Tech Pros Worldwide Forums | Help | Site Map
bytes > topic > access > answers

access vba function get form caption

SELIM ZAIRI
Guest
  
Posts: n/a
#1: Nov 12 '05
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.Name, MSysObjects.Type, GETFRMCAPTION([Name]) AS
Expr1
FROM MSysObjects
WHERE (((MSysObjects.Type)=-32768));

and this function to get form caption

Public Function GETFRMCAPTION(FRMNM 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
Guest
  
Posts: n/a
#2: Nov 12 '05

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@HOTMAIL.COM> wrote in message
news:2db27eb2.0403070250.9ec1c1f@posting.google.co m...[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.Name, MSysObjects.Type, GETFRMCAPTION([Name]) AS
> Expr1
> FROM MSysObjects
> WHERE (((MSysObjects.Type)=-32768));
>
> and this function to get form caption
>
> Public Function GETFRMCAPTION(FRMNM 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
Guest
  
Posts: n/a
#3: Nov 12 '05

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.Name, MSysObjects.Type, GETFRMCAPTION([Name]) AS
> Expr1
> FROM MSysObjects
> WHERE (((MSysObjects.Type)=-32768));
>
> and this function to get form caption
>
> Public Function GETFRMCAPTION(FRMNM 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(FRMNM As String) As String
DoCmd.OpenForm FRMNM, acDesign
GETFRMCAPTION = Forms(GETFRMCAPTION).Caption
Docmd.Close FRMNM, acSaveNo
End Function

Bit messy when running.
--
But why is the Rum gone?
Douglas J. Steele
Guest
  
Posts: n/a
#4: Nov 12 '05

re: access vba function get form caption

"Trevor Best" <nospam@localhost> wrote in message
news:404b0eb1$0$21236$afc38c87@auth.uk.news.easyne t.net...[color=blue]
>
> The form has to be open to get it's caption.
>
> Try:
> Public Function GETFRMCAPTION(FRMNM As String) As String
> DoCmd.OpenForm FRMNM, acDesign
> GETFRMCAPTION = Forms(GETFRMCAPTION).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(FRMNM As String) As String
DoCmd.OpenForm FRMNM, acDesign, , , , acHidden
GETFRMCAPTION = Forms(GETFRMCAPTION).Caption
Docmd.Close FRMNM, acSaveNo
End Function


--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(No private e-mails, please)
Trevor Best
Guest
  
Posts: n/a
#5: Nov 12 '05

re: access vba function get form caption

Douglas J. Steele wrote:
[color=blue]
> "Trevor Best" <nospam@localhost> wrote in message
> news:404b0eb1$0$21236$afc38c87@auth.uk.news.easyne t.net...
>[color=green]
>>The form has to be open to get it's caption.
>>
>>Try:
>>Public Function GETFRMCAPTION(FRMNM As String) As String
>> DoCmd.OpenForm FRMNM, acDesign
>> GETFRMCAPTION = Forms(GETFRMCAPTION).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(FRMNM As String) As String
> DoCmd.OpenForm FRMNM, acDesign, , , , acHidden
> GETFRMCAPTION = Forms(GETFRMCAPTION).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
Guest
  
Posts: n/a
#6: Nov 12 '05

re: access vba function get form caption


"Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_canada.com> wrote in message
news:g_F2c.184031$Qg7.63431@news04.bloor.is.net.ca ble.rogers.com...[color=blue]
> "Trevor Best" <nospam@localhost> wrote in message
> news:404b0eb1$0$21236$afc38c87@auth.uk.news.easyne t.net...[color=green]
> >
> > The form has to be open to get it's caption.
> >
> > Try:
> > Public Function GETFRMCAPTION(FRMNM As String) As String
> > DoCmd.OpenForm FRMNM, acDesign
> > GETFRMCAPTION = Forms(GETFRMCAPTION).Caption
> > Docmd.Close FRMNM, acSaveNo
> > End Function[/color][/color]

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

GETFRMCAPTION = Forms(FRMNM).Caption
DoCmd.Close acForm, FRMNM, acSaveNo
Douglas J. Steele
Guest
  
Posts: n/a
#7: Nov 12 '05

re: access vba function get form caption

"rkc" <rkc@yabba.dabba.do.rochester.rr.bomb> wrote in message
news:41H2c.135670$%72.57943@twister.nyroc.rr.com.. .[color=blue]
>
> "Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_canada.com> wrote in message
> news:g_F2c.184031$Qg7.63431@news04.bloor.is.net.ca ble.rogers.com...[color=green]
> > "Trevor Best" <nospam@localhost> wrote in message
> > news:404b0eb1$0$21236$afc38c87@auth.uk.news.easyne t.net...[color=darkred]
> > >
> > > The form has to be open to get it's caption.
> > >
> > > Try:
> > > Public Function GETFRMCAPTION(FRMNM As String) As String
> > > DoCmd.OpenForm FRMNM, acDesign
> > > GETFRMCAPTION = Forms(GETFRMCAPTION).Caption
> > > Docmd.Close FRMNM, acSaveNo
> > > End Function[/color][/color]
>
> This function is in dire need of a "air code" warning.
>
> GETFRMCAPTION = Forms(FRMNM).Caption
> 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
Guest
  
Posts: n/a
#8: Nov 12 '05

re: access vba function get form caption

"rkc" <rkc@yabba.dabba.do.rochester.rr.bomb> wrote in
news:41H2c.135670$%72.57943@twister.nyroc.rr.com:
[color=blue]
> "Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_canada.com> wrote in
> message
> news:g_F2c.184031$Qg7.63431@news04.bloor.is.net.ca ble.rogers.com...[color=green]
>> "Trevor Best" <nospam@localhost> wrote in message
>> news:404b0eb1$0$21236$afc38c87@auth.uk.news.easyne t.net...[color=darkred]
>> >
>> > The form has to be open to get it's caption.
>> >
>> > Try:
>> > Public Function GETFRMCAPTION(FRMNM As String) As String
>> > DoCmd.OpenForm FRMNM, acDesign
>> > GETFRMCAPTION = Forms(GETFRMCAPTION).Caption
>> > Docmd.Close FRMNM, acSaveNo
>> > End Function[/color][/color]
>
> This function is in dire need of a "air code" warning.
>
> GETFRMCAPTION = Forms(FRMNM).Caption
> 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
Guest
  
Posts: n/a
#9: Nov 12 '05

re: access vba function get form caption

David W. Fenton wrote:[color=blue]
> "rkc" <rkc@yabba.dabba.do.rochester.rr.bomb> wrote in
> news:41H2c.135670$%72.57943@twister.nyroc.rr.com:
>
>[color=green]
>>"Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_canada.com> wrote in
>>message
>>news:g_F2c.184031$Qg7.63431@news04.bloor.is.net. cable.rogers.com...
>>[color=darkred]
>>>"Trevor Best" <nospam@localhost> wrote in message
>>>news:404b0eb1$0$21236$afc38c87@auth.uk.news.eas ynet.net...
>>>
>>>>The form has to be open to get it's caption.
>>>>
>>>>Try:
>>>>Public Function GETFRMCAPTION(FRMNM As String) As String
>>>> DoCmd.OpenForm FRMNM, acDesign
>>>> GETFRMCAPTION = Forms(GETFRMCAPTION).Caption
>>>> Docmd.Close FRMNM, acSaveNo
>>>>End Function[/color]
>>
>>This function is in dire need of a "air code" warning.
>>
>>GETFRMCAPTION = Forms(FRMNM).Caption
>>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
Guest
  
Posts: n/a
#10: Nov 12 '05

re: access vba function get form caption


"Trevor Best" <nospam@localhost> wrote in message
news:404b881a$0$15206$afc38c87@auth.uk.news.easyne t.net...[color=blue]
> David W. Fenton wrote:[color=green]
> > "rkc" <rkc@yabba.dabba.do.rochester.rr.bomb> wrote in
> > news:41H2c.135670$%72.57943@twister.nyroc.rr.com:
> >
> >[color=darkred]
> >>"Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_canada.com> wrote in
> >>message
> >>news:g_F2c.184031$Qg7.63431@news04.bloor.is.net. cable.rogers.com...
> >>
> >>>"Trevor Best" <nospam@localhost> wrote in message
> >>>news:404b0eb1$0$21236$afc38c87@auth.uk.news.eas ynet.net...
> >>>
> >>>>The form has to be open to get it's caption.
> >>>>
> >>>>Try:
> >>>>Public Function GETFRMCAPTION(FRMNM As String) As String
> >>>> DoCmd.OpenForm FRMNM, acDesign
> >>>> GETFRMCAPTION = Forms(GETFRMCAPTION).Caption
> >>>> Docmd.Close FRMNM, acSaveNo
> >>>>End Function
> >>
> >>This function is in dire need of a "air code" warning.
> >>
> >>GETFRMCAPTION = Forms(FRMNM).Caption
> >>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
Guest
  
Posts: n/a
#11: Nov 12 '05

re: access vba function get form caption

"rkc" <rkc@yabba.dabba.do.rochester.rr.bomb> wrote in
news:H6M2c.137182$%72.77031@twister.nyroc.rr.com:
[color=blue]
>
> "Trevor Best" <nospam@localhost> wrote in message
> news:404b881a$0$15206$afc38c87@auth.uk.news.easyne t.net...[color=green]
>> David W. Fenton wrote:[color=darkred]
>> > "rkc" <rkc@yabba.dabba.do.rochester.rr.bomb> wrote in
>> > news:41H2c.135670$%72.57943@twister.nyroc.rr.com:
>> >
>> >
>> >>"Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_canada.com> wrote
>> >>in message
>> >>news:g_F2c.184031$Qg7.63431[/color][/color][/color]
@news04.bloor.is.net.cable.rogers.com[color=blue][color=green][color=darkred]
>> >>...
>> >>
>> >>>"Trevor Best" <nospam@localhost> wrote in message
>> >>>news:404b0eb1$0$21236$afc38c87@auth.uk.news.eas ynet.net...
>> >>>
>> >>>>The form has to be open to get it's caption.
>> >>>>
>> >>>>Try:
>> >>>>Public Function GETFRMCAPTION(FRMNM As String) As String
>> >>>> DoCmd.OpenForm FRMNM, acDesign
>> >>>> GETFRMCAPTION = Forms(GETFRMCAPTION).Caption
>> >>>> Docmd.Close FRMNM, acSaveNo
>> >>>>End Function
>> >>
>> >>This function is in dire need of a "air code" warning.
>> >>
>> >>GETFRMCAPTION = Forms(FRMNM).Caption
>> >>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
Guest
  
Posts: n/a
#12: Nov 12 '05

re: access vba function get form caption

David W. Fenton wrote:
[color=blue]
> "rkc" <rkc@yabba.dabba.do.rochester.rr.bomb> wrote in
> news:H6M2c.137182$%72.77031@twister.nyroc.rr.com:
>
>[color=green]
>>"Trevor Best" <nospam@localhost> wrote in message
>>news:404b881a$0$15206$afc38c87@auth.uk.news.easy net.net...
>>[color=darkred]
>>>David W. Fenton wrote:
>>>
>>>>"rkc" <rkc@yabba.dabba.do.rochester.rr.bomb> wrote in
>>>>news:41H2c.135670$%72.57943@twister.nyroc.rr.c om:
>>>>
>>>>
>>>>
>>>>>"Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_canada.com> wrote
>>>>>in message
>>>>>news:g_F2c.184031$Qg7.63431[/color][/color]
>
> @news04.bloor.is.net.cable.rogers.com
>[color=green][color=darkred]
>>>>>...
>>>>>
>>>>>
>>>>>>"Trevor Best" <nospam@localhost> wrote in message
>>>>>>news:404b0eb1$0$21236$afc38c87@auth.uk.news. easynet.net...
>>>>>>
>>>>>>
>>>>>>>The form has to be open to get it's caption.
>>>>>>>
>>>>>>>Try:
>>>>>>>Public Function GETFRMCAPTION(FRMNM As String) As String
>>>>>>> DoCmd.OpenForm FRMNM, acDesign
>>>>>>> GETFRMCAPTION = Forms(GETFRMCAPTION).Caption
>>>>>>> Docmd.Close FRMNM, acSaveNo
>>>>>>>End Function
>>>>>
>>>>>This function is in dire need of a "air code" warning.
>>>>>
>>>>>GETFRMCAPTION = Forms(FRMNM).Caption
>>>>>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.
>>>>
>>>
>>>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?
Closed Thread

« previous question | next question »

Similar Microsoft Access / VBA bytes

/bytes/development

/bytes/it

Forums
Visit our community forums for general discussions and latest on Bytes

/bytes/about

We are a network of experts and professionals in IT and software development that help one another with answers to tough questions and share insights. Get the best answers to your questions from over 226,587 network members.

dev questions:
algorithms | asp | asp.net | c/c++ | coldfusion | c# | flash | html/css | java | javascript |
mobile dev | .net | perl | php | python | ruby | software dev | vb.net | visual basic | xml
it questions:
access | apache | careers | db2 | iis | macosx | mysql | networking | oracle | postgresql | sql server | unix | windows

About Bytes | Copyright 1995-2009 BYTES. All rights Reserved