Using IsLoaded to determine if second subform is loaded

[Giles]

I realise that both the IsLoaded function and the issue of how to refer to sorms and subforms are covered extensively in these forums, but I've trawled through and tested many of the suggestions and still cannot get Access (I'm using Access 2002) to do what I'd have thought should be a pretty simple task. Please bear with me if you think I should have looked harder!

All I want to do is: I have a main form (Main Form) which contains two separate subforms; when the "On Current" event occurs in one of the subforms (Subform 1), I need to find out if the other (Subform 2) is loaded.

I know that the IsLoaded function is working because when I try the following code in Subform 1's On Current event I get a positive result:

If IsLoaded("Main Form") Then
MsgBox ("It's loaded")
End If

I've tried several variations of references to Subform 2, resulting in almost as many different error messages. For instance:

- If IsLoaded(Me.Parent("Subform 2")) Then : error 13 Type mismatch
- If IsLoaded(Me.Parent!("Subform 2")) Then : Type declaration character does not match declared data type
- If IsLoaded(Forms![Main Form]![Subform 2]) Then : error 450 Wrong number of arguments or invalid property assignment
- If IsLoaded(Forms![Main Form].[Subform 2]) Then : error 13 Type mismatch

As I say, I suspect this is actually very simple. Any ideas what I'm doing wrong (and, more importantly, what I should be doing to get it right!)?

 

[dcx693]

Try this reference:
Me.Parent.Form("SubForm 2")

If that doesn't work, I'd make certain your subform is really called "Subform 2".

 

[Giles]

Thanks for the idea of using Me.Parent.Form("SubForm 2"). Unfortunately, though, it results in another "error 13 Type mismatch".

You rightly surmised that "Subform 2" is not the real name of the subform, but I've double-checked the real name to make sure it's correct in the code and have also tried creating a new dummy subform called simply "test" - and I get the same error.

I know I said earlier that the IsLoaded function was working, but I'm beginning to wonder. I've tried creating a button on the main form which, when pushed, gives a message box saying whether or not the test subform is loaded. The code is:

If IsLoaded("test") Then
MsgBox ("It's loaded")
Else: MsgBox ("It's not loaded")
End If

Sure enough, when I click the button it claims that the subform is not loaded - even though I know it is because not only can I see it, I also set up a confirmation message that appears when the test subform goes through its On Load event.

You can probably tell that desparation is setting in! Does the way Access is behaving make sense - or can you think of a way to determine if there really is something wrong with the IsLoaded function?

 

[dcx693]

I think you're on the right track now. "IsLoaded" is not a built-in Access function. It's a very commonly-used custom function. Most likely it's looking for forms that are opened, but not as subforms. Post the code for your version of the function, though I'm not sure off-hand how to make it work for subforms.

 

[dcx693]

Actually, it shouldn't be too hard to extend it to cover subforms. You can't use the Allforms collection, but you can use the open forms collection, cycling through the subforms within those open forms to look for a name match.

 

[Giles]

In case it helps, the following code (which was already present - I had nothing to do with it) appears in the Global Code module and seems to match what's suggested in various postings about the IsLoaded function:

Function IsLoaded(ByVal strFormName As String) As Integer
' Returns True if the specified form is open in Form view or Datasheet view.
Const conObjStateClosed = 0
Const conDesignView = 0
If SysCmd(acSysCmdGetObjectState, acForm, strFormName) <> conObjStateClosed Then
If Forms(strFormName).CurrentView <> conDesignView Then
IsLoaded = True
End If
End If
End Function

I'm well out of my depth here - but then I have been since day 1, and somehow I haven't drowned yet. If you have any suggestions for making this work with subforms (assuming that that's what the problem is) I'd be very grateful. Well, actually I already am.

Thanks,

Giles.

 

[Rich]

Why would the second subForm not be loaded?

 

[Giles]

That's a fair question. The short answer is that I got an error message telling me that the form wasn't open - but I can't account for why.

The brief synopsis of what I'm ultimately trying to do is that I want the record in Subform 2 to match the record that's selected in Subform 1. I'm therefore using the On Current event in Subform 1 to identify when the user moves between records, and I set a variable (ChosenRecord) to identify the record selected. I then attempt to move to the equivalent record in Subform 2 using the following code (which I copied from somewhere, changing the names appropriately):

DoCmd.GoToRecord acDataForm, "Forms![Main Form].[Subform 2]", acGoTo, ChosenRecord

This is where the error message popped up - when I opened the Main Form or when I subsequently moved between records on Subform 1 I got:

Run-time error '2489': The object 'Forms![Main Form].[Subform 2]' isn't open.

That's what led me to trying to use the IsLoaded function to determine whether Subform 2 really is open. I was hoping to pin down the reason for the error before thinking about what to do to combat it.

 

[Rich]

Link the Master/Childs fields between sub 1 and two then you won't have the problem

 

[Giles]

Not sure about making that link; like Subform 1, Subform 2 is a child of the Main Form, and the only options available for links to the Master are the fields in the Main Form - as far as I can tell, I can't link to any field in Subform 1.

 

[Rich]

It sound from your description as though subForm1 has a OneToMany with SubForm2.
If that's the case then sub2 should be linked to sub1 and not the main form

 

[Giles]

Rich,

I don't know if this is going to help, but here's a little more meat on the bone:

Main Form contains companies.
Subform 1 lists, in Continuous Forms view, every meeting we've had with the company selected in Main Form.
Subform 2, in Single Form view, gives a buch of details about the particular meeting selected in Subform 1, such as who was present from that company, from other companies and from our company.

Does that make it a OneToMany relationship between Subform 1 and Subform 2?

I'd certainly like to be able to do what I think you're suggesting. My original intention was to have Subform 2 as a child within Subform 1, but I couldn't because I need Subform 1 to be in Continuous Forms view, and that doesn't allow further nested subforms. I want to avoid Datasheet view for Subform 1 because it doesn't have the right appearance and the user has to click very specifically on the '+' to open the subform, which then obscures the other details.

Do you know if there's a way to make a link between two forms that are both contained within a master, or does one have to be the child of the other?

 

[Rich]

If many people can attend one meeting then it must be a one to many with meetings and a MeetingID as a common field. You can have a subForm on cont. form, drag it into the cont. form footer. Either that or load subform two as a stand alone popup form

 

[Giles]

Rich,

That's brilliant - having Subform 2 in the footer of Subform 1 solves all the problems (well, until the next set at least). When I think of all the time and frustration I could have saved if I'd come to the forum sooner..... or, indeed, if Access had said anything about subforms being allowed in footers.....

Anyway, thanks a million.

Giles.

 

[dcx693]

Thank you Rich, for getting to the root of the problem.