Opening additional (non-default) form instances

[Bob Bridges]

I first tackled VBA/Access a few years ago when I wrote a Contacts
database...but I did it alone and I'm pretty sure I'm doing some things the
hard way, so feel free to challenge me on more than just this question.

I think I'm about ready to learn how to open up a form more than once --
that is, how to use "instances" of forms, or to open "non-default" instances
of them. Up 'til now, I've done it like this:

DoCmd.OpenForm "clCoSub"
DoCmd.Requery

That code is connected to a button on a record in the master form, and it
gets me the subform with the right child records in them. The problem is
that if I then click the same button on a different record, it doesn't get me
a new instance of the subform, it just replaces the data in the same form
with new records.

I read that I can open up a new form like this:

Set x = New Form_clCoSub
x.Visible=True

This new form disappears as soon as the button-Click module has finished
executing, but by declaring a Collection object at the Module level and
saving the new form object in the collection, I can get it to hang around.
And some of the controls in the form aren't correctly populated with data
when I use this method, but I'm sure I can populated them manually in the
code. What I'm trying to figure out right now is why Access insists on
asking me, at the time it's bringing up the form, to fill in the value of a
checkbox on the form as it's getting ready to open it.

Can anyone point me to a good technical article about non-default instances
of forms so I can learn some of the things I've been only guessing at until
now?

 

[Allen Browne]

Sounds like you have the New keyword working, and you are able to manage the
instances via your collection. My personal preference is to use the hWnd of
the form as its unique identifier. Details (and sample database) in:
Managing Multiple Instances of a Form
at:
http://allenbrowne.com/ser-35.html

If Access is asking you for a value when the form opens, there is a name
somewhere that it can't resolve. If the form is bound to a query, the name
could be there, either:
- a reference such [Forms].[Form1].[Text0], referring to something that is
not yet open.
- a name that doesn't come from a table;
- an alias that Access doesn't recognise;
- a problem with Name AutoCorrect.

If it's not there, perhaps it is in the Filter or OrderBy property of the
source query, or of the form.

(Hopefully this isn't a cyclic depencency, where the form depends on a query
that refers to the form.)

 

[Bob Bridges]

So far I don't anticipate needing to track and control the individual
instances of the subforms; they're opened only as long as the user needs
them, and as soon as he's done with an instance he hits <Esc> and it
disappears. At least, that's what I did when there was only one of
everything, and I expect to do the same with the multiple instances. So for
now I think just putting each instance in the collection is all I need. But
I was sure I'll want to do better later, so I'll keep your hWnd suggestion in
mind for later, thanks.

The subform's records are populated from a query. Two of the three controls
in the header are filled in from controls in the active record of the parent
form. The third is a checkbox with nothing in the Control Source (in the
subform's Design mode) but a default value of False. This works fine for a
"default" open, if that's the term.

When I try to open the subform as a non-default New instance, before
anything else happens Access hands me an "Enter Parameter Value" prompt
asking for a value to put in the checkbox, ignoring that control's default
setting. When I first posted this question, the prompt accepted only <Esc>
or the Close button (the X at the upper right); anything else and it accused
me of having canceling the previous operation. But it's hours later and I've
since closed and reopened the database; now it requires <Enter> or the Ok
button, but otherwise is the same. My question, of course, is how to tell it
what value to use for the checkbox so it won't feel it has to prompt the user
for it.

Since it seemed to be ignoring the Default value, I tried putting =False in
the Control Source field. That got rid of the Parameter prompt, but I wasn't
allowed to manually check or uncheck the box thereafter.

I tried filling in a value during the Form_Open event, but it turns out this
prompt occurs BEFORE the event, so that was no help.

I imagine you already know what I need to do, but in case it's germane:
When the subform opens after the Parm prompt, the detail sections are
correctly filled in from the query. Earlier today the first two controls in
the header displayed as #Err, which I expected I could fill in by a couple of
statements in the code; but now they're filled in properly also. Go figure.
I can also open multiple copies of the subform, so it appears the only
problem is the Parameter prompt. How do I tell it what value to use for the
checkbox when I'm opening it as a non-default instance?

Sounds like you have the New keyword working, and you are able to
manage the instances via your collection. My personal preference is to
use the hWnd of the form as its unique identifier. Details (and sample
database) in "Managing Multiple Instances of a Form" at
http://allenbrowne.com/ser-35.html.

If Access is asking you for a value when the form opens, there is a
name somewhere that it can't resolve. If the form is bound to a query,
the name could be there, either:
- a reference such [Forms].[Form1].[Text0], referring to something
that is not yet open.
- a name that doesn't come from a table;
- an alias that Access doesn't recognise;
- a problem with Name AutoCorrect.

If it's not there, perhaps it is in the Filter or OrderBy property of the
source query, or of the form.

(Hopefully this isn't a cyclic depencency, where the form depends on a query
that refers to the form.)

I think I'm about ready to learn how to open up a form more than once --
that is, how to use "instances" of forms, or to open "non-default"
instances of them. Up 'til now, I've done it like this:

DoCmd.OpenForm "clCoSub"
DoCmd.Requery

I read that I can open up a new form like this:

Set x = New Form_clCoSub
x.Visible=True

....some of the controls in the form aren't correctly populated with
data when I use this method, but I'm sure I can populated them
manually in the code. What I'm trying to figure out right now is
why Access insists on asking me, at the time it's bringing up the
form, to fill in the value of a checkbox on the form as it's getting
ready to open it.

Can anyone point me to a good technical article about non-default
instances of forms so I can learn some of the things I've been only
guessing at until now?

 

[Bob Bridges]

By the way, I used the term "subform" both just now and in my original post.
I think a real subform is a form emplanted in an area of another form, which
is not what I mean; I'm opening up a form that displays child records of
something displayed on the parent form. Child form might be a better term,
therefore. Sorry for any confusion.

 

[Allen Browne]

I'm not completely clear about this, but I suggest you remove the parameter
from the source query.

Instead, use the Load event of the from to filter it in the desired way,
e.g.:
Private Sub Form_Load()
Dim strWhere As String
If CurrentProject.AllForms(Me.Name).IsLoaded Then
strWhere = "[SomeField] = " &
Nz(Forms(Me.Name)!SomeCheckBox.Value, False)
Me.Filter = strWhere
Me.FilerOn = True
End If
End Sub

If this is another instance, it will look for the check box on one of the
other instances, and filter itself accordingly.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

So far I don't anticipate needing to track and control the individual
instances of the subforms; they're opened only as long as the user needs
them, and as soon as he's done with an instance he hits <Esc> and it
disappears. At least, that's what I did when there was only one of
everything, and I expect to do the same with the multiple instances. So
for
now I think just putting each instance in the collection is all I need.
But
I was sure I'll want to do better later, so I'll keep your hWnd suggestion
in
mind for later, thanks.

The subform's records are populated from a query. Two of the three
controls
in the header are filled in from controls in the active record of the
parent
form. The third is a checkbox with nothing in the Control Source (in the
subform's Design mode) but a default value of False. This works fine for
a
"default" open, if that's the term.

When I try to open the subform as a non-default New instance, before
anything else happens Access hands me an "Enter Parameter Value" prompt
asking for a value to put in the checkbox, ignoring that control's default
setting. When I first posted this question, the prompt accepted only
<Esc>
or the Close button (the X at the upper right); anything else and it
accused
me of having canceling the previous operation. But it's hours later and
I've
since closed and reopened the database; now it requires <Enter> or the Ok
button, but otherwise is the same. My question, of course, is how to tell
it
what value to use for the checkbox so it won't feel it has to prompt the
user
for it.

Since it seemed to be ignoring the Default value, I tried putting =False
in
the Control Source field. That got rid of the Parameter prompt, but I
wasn't
allowed to manually check or uncheck the box thereafter.

I tried filling in a value during the Form_Open event, but it turns out
this
prompt occurs BEFORE the event, so that was no help.

I imagine you already know what I need to do, but in case it's germane:
When the subform opens after the Parm prompt, the detail sections are
correctly filled in from the query. Earlier today the first two controls
in
the header displayed as #Err, which I expected I could fill in by a couple
of
statements in the code; but now they're filled in properly also. Go
figure.
I can also open multiple copies of the subform, so it appears the only
problem is the Parameter prompt. How do I tell it what value to use for
the
checkbox when I'm opening it as a non-default instance?

Sounds like you have the New keyword working, and you are able to
manage the instances via your collection. My personal preference is to
use the hWnd of the form as its unique identifier. Details (and sample
database) in "Managing Multiple Instances of a Form" at
http://allenbrowne.com/ser-35.html.

If Access is asking you for a value when the form opens, there is a
name somewhere that it can't resolve. If the form is bound to a query,
the name could be there, either:
- a reference such [Forms].[Form1].[Text0], referring to something
that is not yet open.
- a name that doesn't come from a table;
- an alias that Access doesn't recognise;
- a problem with Name AutoCorrect.

If it's not there, perhaps it is in the Filter or OrderBy property of the
source query, or of the form.

(Hopefully this isn't a cyclic depencency, where the form depends on a
query
that refers to the form.)

I think I'm about ready to learn how to open up a form more than
once --
that is, how to use "instances" of forms, or to open "non-default"
instances of them. Up 'til now, I've done it like this:

DoCmd.OpenForm "clCoSub"
DoCmd.Requery

I read that I can open up a new form like this:

Set x = New Form_clCoSub
x.Visible=True

....some of the controls in the form aren't correctly populated with
data when I use this method, but I'm sure I can populated them
manually in the code. What I'm trying to figure out right now is
why Access insists on asking me, at the time it's bringing up the
form, to fill in the value of a checkbox on the form as it's getting
ready to open it.

Can anyone point me to a good technical article about non-default
instances of forms so I can learn some of the things I've been only
guessing at until now?

 

[Bob Bridges]

There's no parameter in the source query. It reads, in its entirety:

SELECT *
FROM Who AS parent INNER JOIN Who AS child ON child.Owner=parent.ID
WHERE child.Owner=Forms![Company List]!ID And child.defunct In
(False,Forms!clCoSub!NclDefunct)
ORDER BY child.Name;

....Oops. I think I see what's happening. You said earlier "Hopefully this
isn't a cyclic depencency, where the form depends on a query that refers to
the form", which concern I discarded without a second thought because I would
never do such a newbie thing. But I did, didn't I? Ok, I've been
programming 35 years, but in VBA/Access only a few years so I really AM a
newbie. Forms!clCoSub!NclDefunct is the checkbox; when I was opening clCoSub
as a default form, the checkbox's default value worked fine, but for some
reason (perhaps you can explain it) when I open the form as a non-default
instance the checkbox's default value isn't being used; therefore the query
doesn't know whan value to use for Forms!clCoSub!NclDefunct.

So I can see two solutions:

1) Find some way of supplying the proper value to Forms!clCoSub!NclDefunct
BEFORE the query is run. I don't think that can work, because it says here
the form isn't opened until the query has already been run. (But then, it
worked when the form was default, so maybe there's a way.) Or

2) Two different queries for the Record Source, one when the form is first
opened ("where not child.Defunct") and one to use when the user changes the
checkbox ("WHERE child.Defunct In (False,Forms!clCoSub!NclDefunct)").

Or maybe you see a more elegant solution?

 

[Allen Browne]

Your query statement, has 2 parameters:
Forms![Company List]!ID
Forms!clCoSub!NclDefunct

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

There's no parameter in the source query. It reads, in its entirety:

SELECT *
FROM Who AS parent INNER JOIN Who AS child ON child.Owner=parent.ID
WHERE child.Owner=Forms![Company List]!ID And child.defunct In
(False,Forms!clCoSub!NclDefunct)
ORDER BY child.Name;

...Oops. I think I see what's happening. You said earlier "Hopefully
this
isn't a cyclic depencency, where the form depends on a query that refers
to
the form", which concern I discarded without a second thought because I
would
never do such a newbie thing. But I did, didn't I? Ok, I've been
programming 35 years, but in VBA/Access only a few years so I really AM a
newbie. Forms!clCoSub!NclDefunct is the checkbox; when I was opening
clCoSub
as a default form, the checkbox's default value worked fine, but for some
reason (perhaps you can explain it) when I open the form as a non-default
instance the checkbox's default value isn't being used; therefore the
query
doesn't know whan value to use for Forms!clCoSub!NclDefunct.

So I can see two solutions:

1) Find some way of supplying the proper value to Forms!clCoSub!NclDefunct
BEFORE the query is run. I don't think that can work, because it says
here
the form isn't opened until the query has already been run. (But then, it
worked when the form was default, so maybe there's a way.) Or

2) Two different queries for the Record Source, one when the form is first
opened ("where not child.Defunct") and one to use when the user changes
the
checkbox ("WHERE child.Defunct In (False,Forms!clCoSub!NclDefunct)").

Or maybe you see a more elegant solution?

I'm not completely clear about this, but I suggest you remove the
parameter
from the source query.

Instead, use the Load event of the from to filter it in the desired way,
e.g.:
Private Sub Form_Load()
Dim strWhere As String
If CurrentProject.AllForms(Me.Name).IsLoaded Then
strWhere = "[SomeField] = " &
Nz(Forms(Me.Name)!SomeCheckBox.Value, False)
Me.Filter = strWhere
Me.FilerOn = True
End If
End Sub

If this is another instance, it will look for the check box on one of the
other instances, and filter itself accordingly.

 

[Bob Bridges]

After more thought, I think you're about to tell me I should work it like
this: Change the query for clCoSub to omit the child.Defunct consideration
entirely:

SELECT *
FROM Who AS parent INNER JOIN Who AS child ON child.Owner=parent.ID
WHERE child.Owner=Forms![Company List]!ID
ORDER BY child.Name;

Then use the filter to make the determination; set it to "not child.Defunct"
when the form is opened, but "child.Defunct In
(False,Forms!clCoSub!NclDefunct)" when the user clicks the checkbox.

If you disagree, of course please straighten me out. Also, any comment as
to why the default value doesn't work for this control when using this method
to open the form?

 

[Bob Bridges]

You must have read only the first sentence or two. I STARTED OUT saying
there's no parm, and quoted the query -- and promptly saw the problem, and
admitted so. "Sure, it gets values from the parent form [I thought], but
those values are working correctly." But I was wrong; one of the values was
supposed to be from the table that the query was populating, which
possibility you mentioned at the start. It's just been so long since I
thought about that query...

Well, I still don't understand why the default value for that checkbox works
when I use OpenForm but not when I use New Form_clCoSub, and if you can
explain that it would help me have a better mental picture of what's going
on, which in turn would help me be a better programmer. But failing that, I
think you've steered me toward the right path, or at least the solution I
want to try next. I'm still open to suggestions, though, because (as I said
before) I strongly suspect there are a lot of things I'm doing the hard way
simply from lack of exposure to other VBA programmers. For instance, I'm
only just now beginning to try RecordSets, and I'm still not sure when
they're better than queries...

Meanwhile, thanks a heap, Allen.

 

[tina]

I think a real subform is a form emplanted in an area of another form

correct.

I'm opening up a form that displays child records of
something displayed on the parent form. Child form might be a better term,
therefore.

when you open a form in a separate window, and that form is dependent on
another already-open form, there really isn't a formal name for the second
form. as long as it's clear that the second form is being opened in a
separate window, "child form" is as good a term as any.

hth