conditionally open subform

[blee via AccessMonster.com]

I'm sure there's an easy way to do this, but, being new to access, I haven't
figured out how. I have a form with a field that has combo box to select from
several options ("sports played"). I want a subform to open to allow the
user to select "level" (ie "varsity" or "JV") if a some of the "sports
played" are selected. For example, "track" does not have an option of varsity
or JV, so the subform would not need to open, but football could be either,
so the subform should open to allow the user to select "level".

Thank you very much for your help!

 

[Al Campagna]

blee,
Add a text field to your SportsPlayed table.
SportsPlayed SubForm
Baseball "Y"
Football "Y"
Track "N"

Include that field in your combo... (NoOfCols = 2)
Column0 Column1
SportsPlayed SubForm

On the AfterUpdate event of SportsPlayed...
If SportsPlayed.Column(1) = "Y" Then
Me.SubFormName.Visible = Yes
Else
Me.SubFormName.Visible = No
End If

Place this same code in the OnCurrent event of the form, so... as you browse from
record to record, the sub will show/hide accordingly
(the SubForm field could also be a Boolean checkbox, and then use True/False in the
above code.)

 

[blee via AccessMonster.com]

Thanks for your help. Seems simple enough, but something isn't working out. I
get an error message saying a variable isn't recognized. The debugger
highlights the name of the subform, which I've checked for spelling.

Any suggestions?
Thanks.

blee,
Add a text field to your SportsPlayed table.
SportsPlayed SubForm
Baseball "Y"
Football "Y"
Track "N"

Include that field in your combo... (NoOfCols = 2)
Column0 Column1
SportsPlayed SubForm

On the AfterUpdate event of SportsPlayed...
If SportsPlayed.Column(1) = "Y" Then
Me.SubFormName.Visible = Yes
Else
Me.SubFormName.Visible = No
End If

Place this same code in the OnCurrent event of the form, so... as you browse from
record to record, the sub will show/hide accordingly
(the SubForm field could also be a Boolean checkbox, and then use True/False in the
above code.)

I'm sure there's an easy way to do this, but, being new to access, I haven't
figured out how. I have a form with a field that has combo box to select from

[quoted text clipped - 5 lines]

Thank you very much for your help!

 

[blee via AccessMonster.com]

Making progress on this. Now, no error message comes up, but the subform
doesn't open as expected. Any suggestions?

Thank you.

Thanks for your help. Seems simple enough, but something isn't working out. I
get an error message saying a variable isn't recognized. The debugger
highlights the name of the subform, which I've checked for spelling.

Any suggestions?
Thanks.

blee,
Add a text field to your SportsPlayed table.

[quoted text clipped - 24 lines]

 

[John Vinson]

Making progress on this. Now, no error message comes up, but the subform
doesn't open as expected. Any suggestions?

Please post your current code. Also explain what you mean by "doesn't
open as expected" - if it's truly a Subform you don't need any code
and you don't "open" the subform at all!

John W. Vinson[MVP]

 

[blee via AccessMonster.com]

Thank you. I mean to say that the subform doesn't appear when it should (when
I select, for example, "football" from the "Sport" combo box). Here's the
code I've used:

Option Compare Database
Option Explicit
Dim cboOriginator As ComboBox

(There is some other code here for a calendar pop-up)

Private Sub Sport_AfterUpdate()
If Sport.Column(1) = "Y" Then
Me.Sport_Level.Visible = True
Else
Me.Sport_Level.Visible = False
End If

Thanks again for the assist.

 

[John Vinson]

Private Sub Sport_AfterUpdate()
If Sport.Column(1) = "Y" Then
Me.Sport_Level.Visible = True
Else
Me.Sport_Level.Visible = False
End If

If the combo box has "Y" or "N" in the first column, then you want to
use Column(0) - it's zero based. If that's not it... What is the
RowSource of the combo, and what's the datatype of the relevant
column?

John W. Vinson[MVP]

 

[blee via AccessMonster.com]

I only used Y (not N) in the table; it's in the second column. Row Source for
the combo is: SELECT tblSport.Sport FROM tblSport ORDER BY [Sport];

The datatype for the two fields ("Sport", "Subform?") in tblSport are both
"text", as is the field in tblLevel (only has one field, for varsity, JV,
modified).

Thank you.

 

[Al Campagna]

Blee,
Everything looks pretty good, so we're missing something here, or something is not
quite as described. It should work... as described.

First, change the Name of your field [SubForm?] to [HasSubform] (or whatever makes
sense to you). Avoid the ? in the name, as it might be indicating a wildcard character,
or an input mask character. Probably not a problem, but let's get rid of it anyway.
Next, make sure the NAME of the subform is Sport_Level (vs. the SourceObject name)
Are the widths of both columns of the combo > 0 (like 1"; .5")
Make sure there are no duplicate sports.
In your Sports table, make sure there are no "Lookup" fields.

If these last suggestions don't work, use my website below (via Contact) to zip and
send me the mdb file/s.
Indicate in the note the Access version, and the Form involved.
Put "Newsgroup" somewhere in the subject, and post back here that you have sent the
file. I'll look out for it...
No charge... All confidential.

--
hth
Al Campagna
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions

I only used Y (not N) in the table; it's in the second column. Row Source for
the combo is: SELECT tblSport.Sport FROM tblSport ORDER BY [Sport];

The datatype for the two fields ("Sport", "Subform?") in tblSport are both
"text", as is the field in tblLevel (only has one field, for varsity, JV,
modified).

Thank you.

If the combo box has "Y" or "N" in the first column, then you want to
use Column(0) - it's zero based. If that's not it... What is the
RowSource of the combo, and what's the datatype of the relevant
column?

John W. Vinson[MVP]

 

[blee via AccessMonster.com]

Hello

Tried these things to no avail. Even tried re-doing relevant tables, forms
from scratch, but I must be messing something up. So, I just sent the file to
you and am hoping for the best!

Thanks again for your help.

Blee,
Everything looks pretty good, so we're missing something here, or something is not
quite as described. It should work... as described.

First, change the Name of your field [SubForm?] to [HasSubform] (or whatever makes
sense to you). Avoid the ? in the name, as it might be indicating a wildcard character,
or an input mask character. Probably not a problem, but let's get rid of it anyway.
Next, make sure the NAME of the subform is Sport_Level (vs. the SourceObject name)
Are the widths of both columns of the combo > 0 (like 1"; .5")
Make sure there are no duplicate sports.
In your Sports table, make sure there are no "Lookup" fields.

If these last suggestions don't work, use my website below (via Contact) to zip and
send me the mdb file/s.
Indicate in the note the Access version, and the Form involved.
Put "Newsgroup" somewhere in the subject, and post back here that you have sent the
file. I'll look out for it...
No charge... All confidential.

I only used Y (not N) in the table; it's in the second column. Row Source for
the combo is: SELECT tblSport.Sport FROM tblSport ORDER BY [Sport];

[quoted text clipped - 18 lines]

 

[Al Campagna]

Fixed, and on it's on the way back to you.
You didn't include the Subform field in the query behind your Sport Combo, so there was no
Col(1) value for the IF to operate on.
--
hth
Al Campagna
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions

Hello

Tried these things to no avail. Even tried re-doing relevant tables, forms
from scratch, but I must be messing something up. So, I just sent the file to
you and am hoping for the best!

Thanks again for your help.

Blee,
Everything looks pretty good, so we're missing something here, or something is not
quite as described. It should work... as described.

First, change the Name of your field [SubForm?] to [HasSubform] (or whatever makes
sense to you). Avoid the ? in the name, as it might be indicating a wildcard character,
or an input mask character. Probably not a problem, but let's get rid of it anyway.
Next, make sure the NAME of the subform is Sport_Level (vs. the SourceObject name)
Are the widths of both columns of the combo > 0 (like 1"; .5")
Make sure there are no duplicate sports.
In your Sports table, make sure there are no "Lookup" fields.

If these last suggestions don't work, use my website below (via Contact) to zip and
send me the mdb file/s.
Indicate in the note the Access version, and the Form involved.
Put "Newsgroup" somewhere in the subject, and post back here that you have sent the
file. I'll look out for it...
No charge... All confidential.

I only used Y (not N) in the table; it's in the second column. Row Source for
the combo is: SELECT tblSport.Sport FROM tblSport ORDER BY [Sport];

[quoted text clipped - 18 lines]

John W. Vinson[MVP]

 

[John Vinson]

I only used Y (not N) in the table; it's in the second column. Row Source for
the combo is: SELECT tblSport.Sport FROM tblSport ORDER BY [Sport];

This query has only one field. You're trying to use the the second of
one columns.

John W. Vinson[MVP]