Opening form when desired record MAY not be there

[Gary Schuldt]

I'm dealing with displaying / modifying data that describes a subtype, and
my question has to do with a pattern that occurs in such situations.

The application deals with tracking information about (botanical) plants.
Table tPlant contains genus/species/variety/commonName info common to all
plants. tPlantID is the PK for such records.

Some plants in tPlant also belong to a subtype of interest--e.g., Rhody,
Conifer, Heather. Each subtype has its own particular attributes--i.e., the
fields in tPlantRhody are those used by the Rhody afficionadi to describe
rhodies and do not apply to Conifers. There'd be another table
tPlantConifer, etc. tPlantID is the PK for all these subtype tables.

The question is coming!

Say the form frmPlant is being displayed, and it includes a drop-down list
of all the subtype names (Rhody, Heather, Grass, etc). If the user clicks
on one of those list items, I want to popup the corresponding form for that
type, displaying the subtype record whose PK = tPlantID from frmPlant.

Problem is that that particular record may not exist, either because a new
tPlant record is being created or because the subtype info wasn't supplied
by the user when it was created.

Can I just open the subtype form (e.g., frmPlantRhody) and trust Access to
determine whether it's in Add or Edit mode, depending on the existence of
the tPlantID in tPlantRhody? Or do I have to include code to test for that
condition and use two OpenForm statements with different modes?

Since this could happen in any supertype / subtype situation, the solution
will definitely be reusable!

Gary

I have the I'm just not sure which "mode" to

 

[Steve Schapel]

Gary,

One way you could do this is have a subform on the frmPlant form, and
manipulate the Source Object property of the subform depending on the
subtype selection on the main form. For example...
Private Sub SubType_AfterUpdate()
Select Case Me.SubType
Case "Rhody"
Me.SubformName.SourceObject = "RhodyForm"
Case "Conifer"
Me.SubformName.SourceObject = ConiferForm"
etc
End Select
End Sub
You would probably also want this in the Current event of the main form.

 

[Gary Schuldt]

Steve,

Hmmm, didn't think of that. I've never dealt with laying out a "variable"
subform on a form: It's always been one whose control content I knew in
advance. But I guess you wouldn't suggest it if it didn't work, so I will
give it a go! Do you have a suggestion for style of subform layout in this
variable case?

If I also put similar code in the Current event of the main form, that will
populate whatever subtype form is appropriate whenever the main form is
opened, is that right?

Thanks for the idea.

Gary

 

[Gary Schuldt]

Actually, I think my code could be simpler, because the Listbox on the main
form frmPlant draws its values from a "lookup" table tPlantType, whose
fields are:

tPlantType:
ID
Name (that's what appears in the Listbox)
frmPlantSubtypeName (the name of the form for that subtype)
. . .

Then I can replace the Select-Case statement with something like

Me.SubformName.SourceObject = Me.lstboxSubtype.Column(?)

Not exactly sure of the syntax here, but the idea is to pick up the subform
name out of the tPlantType table above.

Could I also use the same statement for the Current event, even before an
item in the Listbox has been clicked?

Gary

 

[Steve Schapel]

Great idea, Gary. This will work. And yes, including the Current event
of the form, should be good... the listbox is bound to the ID field in
the tPlant table, right? So you normally wouldn't be clicking on the
listbox except in the case of a new record, right?. Syntax would be...
Me.SubformName.SourceObject = Me.lstboxSubtype.Column(2)
(column numbers start at 0, so 3rd column is 2)

 

[Gary Schuldt]

1. re: Great idea

Good!!

2. Listbox binding

The frmPlant displays the basic info for one record in tPlant. The listbox
is bound to the ID field in the tPlantType table so it can display the
tPlantType_Name.

3. When I'd click on the listbox

Yes, normally it would be in the context of a new plant record so you could
also record the subtype information about that plant. I guess if you did
similar for the Current event, the subtype info, if any, would come up with
the main for basic info.

I know I'd have to handle the case where someone might want to change the
subtype of a given tPlant record. I think the code we have would establish
the new subtype info by "refreshing" the subform, no? But then I need to
delete the old subtype instance record that is still there. That'll be
phase 2!

I'll get to work on this, and I'm sure I'll be back to you with some basic
questions about how to make this work.

Gary

 

[Gary Schuldt]

OK, Steve, I do have a question.

I've been identifying the steps in this design enhancement (there are more
than I expected!) and am so far puzzled about one thing:

How would you go about adding the generalized subform to the main form?
Could I just drag one of the specific subforms (e.g., frmPlantRhody) to the
main form and set up the links? Then when the event code runs, it'll set
the SourceObject to the appropriate subform at run-time.

The subforms won't all have the same number of fields, but I guess I should
make them all as similar in format as possible--such as all ViewProperties =
Datasheet. Is that what you'd do?

Gary

 

[Steve Schapel]

Gary,

Yep. That's exactly how I would do it.

After setting up the sibform, I would then leave its Source Object
property blank.

Of course, if the size/configuration of the subforms vary widely one
from another, you might need to fiddle around a bit ot get them looking
pretty. But from a functionality point of view, it should work nicely.

 

[Gary Schuldt]

Steve,

I finally got around to implementing the basics of this, and it's working
fine! Really slick. Now for the gold-plating . . . <grin>

Thanks again.

Gary

 

[Steve Schapel]

Fantastic, Gary!

 

[Gary Schuldt]

OK, Steve, I promised follow-on questions!

Yes, it is fantastic ;-)

Remember that these subforms represent subtypes of the plant info in the
main form--the special info to describe each plant form (unfortunately,
that's what the plant people call special types of these plants--FORMS!)

Anyway, before I implemented the subform approach you suggested, I had
already started putting some of the subtype info into the main form. Now I
need to get it into the subform for that particular subtype.

For example, I have two fields that need to be copied from the main form
into the frmGSVRhody subform (then I'll delete those fields entirely from
the main form and its table, don't worry!).

I thought it would be cool to put a temporary cmdButton on the main form
that I could click to copy the two fields over into the Rhody subform when
it was displayed. (I realize that I could probably run something like an
append query to accomplish the same thing in one fell swoop on the
underlying subtype table, but this forms-based approach will be a learning
experience.)

So I set up a cmdTransfer button on the main form, with On Click set to
perform a macro with the statement (test case transferring only one field of
the two):

SetValue
(item): [Forms]![frm???]![BloomTime]
(expression): [Forms]![frmMainPlant]![RhodyBloomTime]

Problem is (as indicated in the code above) that I can't get anything to
work in the frm??? part of the statement. First I tried frmGSVRhody, which
is the subtype subform being displayed when I click the button. But I get
an error message that

==MS Access can't find the form 'frmGSVRhody' referred to in the macro or VB
code.==

So I tried using the reference frmGSVSpecifics, which is the name of the
subform control (on the main form), but I get the same message.

It seems that I don't know how to refer to a control on a subform whose name
was set via the SourceObject at run-time. Or maybe the problem is something
else I don't see.

Again, I appreciate your help!

Gary

 

[Steve Schapel]

Gary,

Here's a concept which you may or may not already understand, but if
not, it will help... Forms don't have data! The *only* place your data
ever sits is in the tables. All your forms do is provide a periscope in
order to facilitate the seeing and touching of the data, but the data
nevertheless is never in the form, even though you can do some stuff
that makes it look like it is Not only that, but sometimes one form
will show data some of which lives in one table and some in another. So
when it comes to procedures like changing the structure of the data
design, it is advisable to think of this at the table level. In this
sense, I liked your idea of Append Query (or more likely, Update Query).

Now to get to your question ...
The problem is because a subform is not a form. The main form contains
a subform control, which displays a representation of the form wqhose
Source Object is specified in the properties of the subform control.
But that form which is represented in the subform control is not open.
This is the meaning of the "can't find the form 'frmGSVRhody'" error
message that you received. The answer is that you have to refer to the
full syntax of the control on the subform, which looks like this...
[Forms]![frmMainPlant]![frm????].[Form]![BloomTime]
.... where frm???? is the *name of the subform control* on the main form
- this will remain the same regardless of the source object setting at
the time.

--
Steve Schapel, Microsoft Access MVP

OK, Steve, I promised follow-on questions!

Yes, it is fantastic ;-)

Remember that these subforms represent subtypes of the plant info in the
main form--the special info to describe each plant form (unfortunately,
that's what the plant people call special types of these plants--FORMS!)

Anyway, before I implemented the subform approach you suggested, I had
already started putting some of the subtype info into the main form. Now I
need to get it into the subform for that particular subtype.

For example, I have two fields that need to be copied from the main form
into the frmGSVRhody subform (then I'll delete those fields entirely from
the main form and its table, don't worry!).

I thought it would be cool to put a temporary cmdButton on the main form
that I could click to copy the two fields over into the Rhody subform when
it was displayed. (I realize that I could probably run something like an
append query to accomplish the same thing in one fell swoop on the
underlying subtype table, but this forms-based approach will be a learning
experience.)

So I set up a cmdTransfer button on the main form, with On Click set to
perform a macro with the statement (test case transferring only one field of
the two):

SetValue
(item): [Forms]![frm???]![BloomTime]
(expression): [Forms]![frmMainPlant]![RhodyBloomTime]

Problem is (as indicated in the code above) that I can't get anything to
work in the frm??? part of the statement. First I tried frmGSVRhody, which
is the subtype subform being displayed when I click the button. But I get
an error message that

==MS Access can't find the form 'frmGSVRhody' referred to in the macro or VB
code.==

So I tried using the reference frmGSVSpecifics, which is the name of the
subform control (on the main form), but I get the same message.

It seems that I don't know how to refer to a control on a subform whose name
was set via the SourceObject at run-time. Or maybe the problem is something
else I don't see.

Again, I appreciate your help!

Gary

 

[Gary Schuldt]

Another quick and patient reply from SS--thanks.

Forms don't have data! The *only* place your data
ever sits is in the tables.

What about an unbound control on a form? Can't I say, for example:

Me!unbtxtbox = Me!comboBox

It would seem that neither of these two values are in a table . . .

when it comes to procedures like changing the structure of the data
design, it is advisable to think of this at the table level.

I agree with you there. But I'm not changing the structure of tGSVRhody,
which already has the BloomTime field defined for it. I'm only adding a new
record to tGSVRhody via the subform frmGSVRhody with a value for
tGSVRhody.BloomTime.

I liked your idea of Append Query (or more likely, Update Query).

Because I'm adding new (subtype) records, wouldn't it be Append instead of
Update?

The problem is because a subform is not a form. . . . .
But that form which is represented in the subform control is not open.

WOW! Major epiphany here! Since you can get a subform on a main form by
first designing the subform AS A FORM (!) and then dragging it to the main
form, of course I tended to think of the subform as a form. But you're
saying the form more like a "template" for the subform (i.e., just a
representation), right?

full syntax of the control on the subform, which looks like this...
[Forms]![frmMainPlant]![frm????].[Form]![BloomTime]
... where frm???? is the *name of the subform control* on the main form

. . . and the [Form] above is just as written? . . . so, replacing the
???, it would read:
[Forms]![frmMainPlant]![frmGSVSpecifics].[Form]![BloomTime]

(Is there some significance to using the "." instead of the "!" before
[Form]? All the other qualifiers are "!")

Gary (up late Saturday night but making good progress)
===========================================================

Gary,

Here's a concept which you may or may not already understand, but if
not, it will help... Forms don't have data! The *only* place your data
ever sits is in the tables. All your forms do is provide a periscope in
order to facilitate the seeing and touching of the data, but the data
nevertheless is never in the form, even though you can do some stuff
that makes it look like it is Not only that, but sometimes one form
will show data some of which lives in one table and some in another. So
when it comes to procedures like changing the structure of the data
design, it is advisable to think of this at the table level. In this
sense, I liked your idea of Append Query (or more likely, Update Query).

Now to get to your question ...
The problem is because a subform is not a form. The main form contains
a subform control, which displays a representation of the form wqhose
Source Object is specified in the properties of the subform control.
But that form which is represented in the subform control is not open.
This is the meaning of the "can't find the form 'frmGSVRhody'" error
message that you received. The answer is that you have to refer to the
full syntax of the control on the subform, which looks like this...
[Forms]![frmMainPlant]![frm????].[Form]![BloomTime]
... where frm???? is the *name of the subform control* on the main form
- this will remain the same regardless of the source object setting at
the time.

--
Steve Schapel, Microsoft Access MVP

OK, Steve, I promised follow-on questions!

Yes, it is fantastic ;-)

Remember that these subforms represent subtypes of the plant info in the
main form--the special info to describe each plant form (unfortunately,
that's what the plant people call special types of these plants--FORMS!)

Anyway, before I implemented the subform approach you suggested, I had
already started putting some of the subtype info into the main form. Now I
need to get it into the subform for that particular subtype.

For example, I have two fields that need to be copied from the main form
into the frmGSVRhody subform (then I'll delete those fields entirely from
the main form and its table, don't worry!).

I thought it would be cool to put a temporary cmdButton on the main form
that I could click to copy the two fields over into the Rhody subform when
it was displayed. (I realize that I could probably run something like an
append query to accomplish the same thing in one fell swoop on the
underlying subtype table, but this forms-based approach will be a learning
experience.)

So I set up a cmdTransfer button on the main form, with On Click set to
perform a macro with the statement (test case transferring only one field of
the two):

SetValue
(item): [Forms]![frm???]![BloomTime]
(expression): [Forms]![frmMainPlant]![RhodyBloomTime]

Problem is (as indicated in the code above) that I can't get anything to
work in the frm??? part of the statement. First I tried frmGSVRhody, which
is the subtype subform being displayed when I click the button. But I get
an error message that

==MS Access can't find the form 'frmGSVRhody' referred to in the macro or VB
code.==

So I tried using the reference frmGSVSpecifics, which is the name of the
subform control (on the main form), but I get the same message.

It seems that I don't know how to refer to a control on a subform whose name
was set via the SourceObject at run-time. Or maybe the problem is something
else I don't see.

Again, I appreciate your help!

Gary

 

[Steve Schapel]

Gary,

From Steve up late on *Sunday* night, but about to call it quits!...

Well, I wouldn't want to get caught up in a discussion of semantics, but
.... yes, you can assign a value to a form control, and if that control
is bound, then that value is then transferred to the data stored in the
table field. This is I suppose what I was alluding to when I said "even
though you can do some stuff that makes it look like it is". But in the
example of Me!unbtxtbox = Me!comboBox if unbtxtbox and comboBox are
unbound, then in my book it's not data! Close the form and ... pfffttt!

All that aside, I did miss your full meaning before, in that I imagined
you were trying to move a field from one table to another. It sounds
like you have got this under control now, and maybe you are going to use
an Append query.

For more information on referencing controls on subforms, see
http://www.mvps.org/access/forms/frm0031.htm

--
Steve Schapel, Microsoft Access MVP

Another quick and patient reply from SS--thanks.

Forms don't have data! The *only* place your data
ever sits is in the tables.

What about an unbound control on a form? Can't I say, for example:

Me!unbtxtbox = Me!comboBox

It would seem that neither of these two values are in a table . . .

when it comes to procedures like changing the structure of the data
design, it is advisable to think of this at the table level.

I agree with you there. But I'm not changing the structure of tGSVRhody,
which already has the BloomTime field defined for it. I'm only adding a new
record to tGSVRhody via the subform frmGSVRhody with a value for
tGSVRhody.BloomTime.

I liked your idea of Append Query (or more likely, Update Query).

Because I'm adding new (subtype) records, wouldn't it be Append instead of
Update?

The problem is because a subform is not a form. . . . .
But that form which is represented in the subform control is not open.

WOW! Major epiphany here! Since you can get a subform on a main form by
first designing the subform AS A FORM (!) and then dragging it to the main
form, of course I tended to think of the subform as a form. But you're
saying the form more like a "template" for the subform (i.e., just a
representation), right?

full syntax of the control on the subform, which looks like this...
[Forms]![frmMainPlant]![frm????].[Form]![BloomTime]
... where frm???? is the *name of the subform control* on the main form

. . . and the [Form] above is just as written? . . . so, replacing the
???, it would read:
[Forms]![frmMainPlant]![frmGSVSpecifics].[Form]![BloomTime]

(Is there some significance to using the "." instead of the "!" before
[Form]? All the other qualifiers are "!")

Gary (up late Saturday night but making good progress)
===========================================================

 

[Gary Schuldt]

Steve,

I would call the table data "persistent" data, because it persists beyond
the closing of even the application. I guess the unbound controls would
represent non-persistent data, because it's there and you can manipulate it
in every way imaginable . . . before it goes . . . Pfffffftttt!

Thanks for the link to the mvp site and the syntax for referencing form
data. If all those combinations have to be tabularized, then it appears
that there are no rules or patterns to make it easier to remember or apply.
At least now I have no excuse for making stupid mistakes!

Gary

Gary,

From Steve up late on *Sunday* night, but about to call it quits!...

Well, I wouldn't want to get caught up in a discussion of semantics, but
... yes, you can assign a value to a form control, and if that control
is bound, then that value is then transferred to the data stored in the
table field. This is I suppose what I was alluding to when I said "even
though you can do some stuff that makes it look like it is". But in the
example of Me!unbtxtbox = Me!comboBox if unbtxtbox and comboBox are
unbound, then in my book it's not data! Close the form and ... pfffttt!

All that aside, I did miss your full meaning before, in that I imagined
you were trying to move a field from one table to another. It sounds
like you have got this under control now, and maybe you are going to use
an Append query.

For more information on referencing controls on subforms, see
http://www.mvps.org/access/forms/frm0031.htm

--
Steve Schapel, Microsoft Access MVP

Another quick and patient reply from SS--thanks.

Forms don't have data! The *only* place your data
ever sits is in the tables.

What about an unbound control on a form? Can't I say, for example:

Me!unbtxtbox = Me!comboBox

It would seem that neither of these two values are in a table . . .

when it comes to procedures like changing the structure of the data
design, it is advisable to think of this at the table level.

I agree with you there. But I'm not changing the structure of tGSVRhody,
which already has the BloomTime field defined for it. I'm only adding a new
record to tGSVRhody via the subform frmGSVRhody with a value for
tGSVRhody.BloomTime.

I liked your idea of Append Query (or more likely, Update Query).

Because I'm adding new (subtype) records, wouldn't it be Append instead of
Update?

The problem is because a subform is not a form. . . . .
But that form which is represented in the subform control is not open.

WOW! Major epiphany here! Since you can get a subform on a main form by
first designing the subform AS A FORM (!) and then dragging it to the main
form, of course I tended to think of the subform as a form. But you're
saying the form more like a "template" for the subform (i.e., just a
representation), right?

full syntax of the control on the subform, which looks like this...
[Forms]![frmMainPlant]![frm????].[Form]![BloomTime]
... where frm???? is the *name of the subform control* on the main form

. . . and the [Form] above is just as written? . . . so, replacing the
???, it would read:
[Forms]![frmMainPlant]![frmGSVSpecifics].[Form]![BloomTime]

(Is there some significance to using the "." instead of the "!" before
[Form]? All the other qualifiers are "!")

Gary (up late Saturday night but making good progress)
===========================================================