Is Form Open? Using SysCmd

[gray]

Hi All

WinXPPro
Access 2002 SP3

I have a Main form with a series of SubForms. I've had lot of fun trying to run commands against the SubForms from the Main form... Is the form open? Does it have a valid recordsource? Are there any records in it? Do I test for Nulls etc etc ... I'm sure you've been there!

I thought I'd write myself a little module based on some great advice I've seen in this forum and other web pages, using SysCmd. This will save me a ot of time I thought ... but I can't get the expected results. The SysCmd call against my forms always returns 0 (closed object) even when I am staring at the open form! And my eyesight is not so good!

Here's the code I am using:

---------------------------------------------------------
Public Function My_Form_Check(Form_To_Test As Form, Mode As String) As Long
On Error GoTo err_My_Form_Check

Dim Form_Open_Value As Integer


My_Form_Check = -99 'Not open


Form_Open_Value = SysCmd(acSysCmdGetObjectState, acForm, Form_To_Test.Name)


If Form_Open_Value = 0 Then 'Not open
'leave at -99
GoTo exit_My_Form_Check
End If

Further code .......................

-----------------------------------------------

I have Msgbox'd the form.name and it returns (what I think is) a valid name, e.g.

"System_Settings_History_Notes_SubForm"

Any idea why SysCmd cannot see the form as open?

Thanks

 

[RuralGuy]

SubForms are not members of the Forms collection. Only the MainForm is there. You must reference a SubForm by going through the MainForm. Have you seen this link before?

 

[gray]

Hi RuralGuy

Yes, I often use that link for reference thanks but I couldn't see how the syntax would be formed in the SysCmd call for subforms since it takes a String for the form name? I can't see how compound Mainform/Subform names could be passed? Any ideas? Incidentally, I tried using a "type" of acSubForm in the SysCmd call but that didn't produce the results either.

The reason I chose this route rather then the isLoaded method is that I read that the SysCmd method is more reliable and that error handling upon checking of non-exist forms is easier. I guess if it can't see subForms then it's game over?

Thanks..... Your advice is always appreciated.

 

[gemma-the-husky]

just to clarify

isopen function (can be used for form (default), or report/query/table etc

needs this syntax

Function IsOpen(strName As String, Optional objtype As Integer = acForm)
    IsOpen = (SysCmd(acSysCmdGetObjectState, objtype, strName) <> 0)
End Function

usage is

isopen("form1")

or

isopen("report1",acReport)

 

[RuralGuy]

Have you tried using the syntax in the link?
FORMS.YourFormName.SubFormControlName.FORM.Name

 

[gray]

Hi RuralGuy,

I hard-coded the SysCmd name field i.e. :-

Form_Open_Value = SysCmd(acSysCmdGetObjectState, acForm, Forms.System_Settings_Form.History_Notes_SubForm.Form.Name)

Again with the form open (and with records displayed) it still returns 0...

As a check, I msgbox'd the same hard-coded name and it returns both the correct name of the form and the correct recordsource... that is to say the name string looks good (to msgbox at least).

I think you are on the right track as when I tried running the SysCMd against my Mainform it worked correctly, so it would seem that it's something to do using it against subForms


Hi Gemma_the_Husky

Thanks for your idea... it looks as though its using the same SysCmd call as mine however?

rgds

 

[RuralGuy]

You are going to want to put that name in a String Variable and use the Variable as the argument. You are also going to want to use the actual Name of the form instead of ending with Name.

 

[gemma-the-husky]

sorry gray - i realise its the subform you are bothered about

i dont think you need to bother - its a control on the main form

so if you examine it from the main form, it must be avaialble, surely

 

[gray]

Hi Rural Guy

Thanks for your reply. As suggested, I've Dimmed a String called 'Form_Name' and set it as follows:-

Form_Name = Forms.System_Settings_Form.History_Notes_SubForm.Form.Name

Msgbox resolves this as :-

"System_Settings_History_Notes_SubForm" (no quotes)

which is the name of the actual form in question (i.e. not the subForm container) but is not the 'compound' name of form/subform.

I then passed it to SysCmd thus:-

Form_Open_Value = SysCmd(acSysCmdGetObjectState, acForm, Form_Name)

But the little blighter still returns 0 (not open)...

I'm surprised since SysCmd is meant to work on any Access Object. My guess is that deep-down Access knows the form as something else (a form index number?)?

Appreciate your help chap!


Hi Gemma

Some time ago I started manipulating various subform items from the main form modules.. I then discovered subforms don't open at startup if they have no records. This caused errors when I tried to reference them. I seem to recall trying to use the 'isLoaded' technique in the mainForm to check for the presence of subForms but abandoned that for some reason (can't quite remember why but probably it did not seem reliable?). I finally got around this by using global booleans which are set by the subform-open events to indicate their presence to the main form. Erksome.. but so far so good...

I set the recordsource of my subForms dependent on factors in the Main but subforms would fail on load if they had 'phantom' recordsources (ones that would no longer resolve on initial opening). Hah! I thought, I'll set the recordsources of the subforms to "blank" upon opening... sadly this was thwarted... subforms suffering unresolvable recordsources caused errors before they got to my "blanking" code. Undeterred, I thought I'd use a 'prepare' module to set my forms' recordsources to 'blank' before opening them at all but had problems with name referencing.

I then started to experience a separate but similar issue where I would get a ton of horrible Null errors when the form was open but had zero records ... so now each time I reference a subform control or variable I check (long-handed) for form presence and record counts... yikes! More code = more maintenance.

Sorry for the lengthy explanation but, ultimately, I've ended up with a ton of inelegant code all over the place and decided I could replace it all with my My_Form_Check module...and, well, here I am ... The Access maze continues...

It's starting to look as though I am going to have to abandon the form-check idea...

Thanks for your help... always appreciated...

 

[RuralGuy]

Try:
Form_Name = "Forms.System_Settings_Form.History_Notes_SubForm." & _
"Form.System_Settings_History_Notes_SubForm"
...as a string variable and leave your SysCmd() as it is.

 

[gray]

Hi RuralGuy

I tried the name syntax as suggested i.e. setting the string variable 'form_name' and using it in the SysCmd but no joy.. I also tried hard-coding that name syntax directly into SysCmd (with the quotes).... but still no joy.


What I find rather strange about the whole concept is that although the form in question is a subForm of my Main form, it could actually run standalone (with a minor modification).. surely it must therefore exist as an object in its own right? Is there a subForms collection I wonder?

rgds

 

[gemma-the-husky]

Some time ago I started manipulating various subform items from the main form modules.. I then discovered subforms don't open at startup if they have no records. This caused errors when I tried to reference them. I seem to recall trying to use the 'isLoaded' technique in the mainForm to check for the presence of subForms but abandoned that for some reason (can't quite remember why but probably it did not seem reliable?). I finally got around this by using global booleans which are set by the subform-open events to indicate their presence to the main form. Erksome.. but so far so good...

i see what you are getting at - but its not that the forms dont open, its just that every control is undefined, and any attempt to read such fields gives a trappable run time error

you can see the same sort of effect if you have a form with no records, based on a non-updateable query. you can't dereference any of the fields, and you get a strange error number. But the form is definitely open! (eg try a data entry form, based on a union query)


try using this sort of code to deal with the situation

on error goto norecord

readvalue = subdetails!subfield
exit sub 'ie don't fall into the error handler

norecord:
msgbox("Error: " & err & " Desc: " & err.description)