How do you hide Columns in a SubForm

  • Thread starter Thread starter Ayo
  • Start date Start date
A

Ayo

I am trying to hide some columns in a subform that I have on my form based
on the select in an Option Group Frame. I tried the visible property of the
textboxes in the subform but that doesn't work.
Any ideas.
Thanks
 
Private Sub frFunctionalArea_Click()
Dim ctrl As Control
Select Case Me.frFunctionalArea
Case 1
For Each ctrl In Me.tblMilestones_subform.Form.Controls
If ctrl.Tag = "General" Then
ctrl.Visible = False
End If
Next
Case 2
For Each ctrl In Me.tblMilestones_subform.Form.Controls
If ctrl.Tag = "SAC" Then
ctrl.Visible = False
End If
Next
Case 3
For Each ctrl In Me.tblMilestones_subform.Form.Controls
If ctrl.Tag = "Permitting" Then
ctrl.Visible = False
End If
Next
Case 4
For Each ctrl In Me.tblMilestones_subform.Form.Controls
If ctrl.Tag = "Pre-Con" Then
ctrl.Visible = False
End If
Next
Case 5
For Each ctrl In Me.tblMilestones_subform.Form.Controls
If ctrl.Tag = "Construction" Then
ctrl.Visible = False
End If
Next
Case 6
For Each ctrl In Me.tblMilestones_subform.Form.Controls
If ctrl.Tag = "Commission" Then
ctrl.Visible = False
End If
Next
Case 7
For Each ctrl In Me.tblMilestones_subform.Form.Controls
If ctrl.Tag = "Telco/MW" Then
ctrl.Visible = False
End If
Next
End Select
Call psBuildMilestoneSubformSQL

End Sub
 
Private Sub frFunctionalArea_Click()
Dim ctrl As Control
Select Case Me.frFunctionalArea
Case 1
For Each ctrl In Me.tblMilestones_subform.Form.Controls
If ctrl.Tag = "General" Then
ctrl.Visible = False
End If
Next
Case 2
For Each ctrl In Me.tblMilestones_subform.Form.Controls
If ctrl.Tag = "SAC" Then
ctrl.Visible = False
End If
Next
Case 3
For Each ctrl In Me.tblMilestones_subform.Form.Controls
If ctrl.Tag = "Permitting" Then
ctrl.Visible = False
End If
Next
Case 4
For Each ctrl In Me.tblMilestones_subform.Form.Controls
If ctrl.Tag = "Pre-Con" Then
ctrl.Visible = False
End If
Next
Case 5
For Each ctrl In Me.tblMilestones_subform.Form.Controls
If ctrl.Tag = "Construction" Then
ctrl.Visible = False
End If
Next
Case 6
For Each ctrl In Me.tblMilestones_subform.Form.Controls
If ctrl.Tag = "Commission" Then
ctrl.Visible = False
End If
Next
Case 7
For Each ctrl In Me.tblMilestones_subform.Form.Controls
If ctrl.Tag = "Telco/MW" Then
ctrl.Visible = False
End If
Next
End Select
Call psBuildMilestoneSubformSQL

End Sub
 
I just tried it using:
For Each ctrl In Me.tblMilestones_subform.Form.Controls
If ctrl.Tag = "General" Then
Me.tblMilestones_subform.Form.Controls(ctrl.Name).ColumnWidth = 0
End If
Next
It didn't work for me. The code ran with out any problem except the columns
are still in the sibform.

Dale_Fye via AccessMonster.com said:
Well,

You can obviously drag the margins of the column in the subform, but to do it
via code you are going to have to do something like the following. When I
opened a datasheet as the form, I was able to go to the immediate window and
type:

forms("frm_Datasheet").Controls("txt_Field1").columnwidth = 0

and it hid the column.

When I put the datasheet in a form as a subform, and gave the subform control
the name "sub_datasheet" I was able to hide the column with the following:

forms("frm_Datasheet_Parent").sub_Datasheet.controls("txt_Field1").
ColumnWidth = 0

To make it visible again, give the ColumnWidth property a value in twips
(inches * 1440)

HTH
Dale


Yes it is.
Is this subform a DataSheet?
Click to expand...
[quoted text clipped - 3 lines]
Any ideas.
Thanks
Click to expand...
Click to expand...

--
HTH

Dale Fye

Message posted via AccessMonster.com
Click to expand...
 
I just tried it using:
For Each ctrl In Me.tblMilestones_subform.Form.Controls
If ctrl.Tag = "General" Then
Me.tblMilestones_subform.Form.Controls(ctrl.Name).ColumnWidth = 0
End If
Next
It didn't work for me. The code ran with out any problem except the columns
are still in the sibform.

Dale_Fye via AccessMonster.com said:
Well,

You can obviously drag the margins of the column in the subform, but to do it
via code you are going to have to do something like the following. When I
opened a datasheet as the form, I was able to go to the immediate window and
type:

forms("frm_Datasheet").Controls("txt_Field1").columnwidth = 0

and it hid the column.

When I put the datasheet in a form as a subform, and gave the subform control
the name "sub_datasheet" I was able to hide the column with the following:

forms("frm_Datasheet_Parent").sub_Datasheet.controls("txt_Field1").
ColumnWidth = 0

To make it visible again, give the ColumnWidth property a value in twips
(inches * 1440)

HTH
Dale


Yes it is.
Is this subform a DataSheet?
Click to expand...
[quoted text clipped - 3 lines]
Any ideas.
Thanks
Click to expand...
Click to expand...

--
HTH

Dale Fye

Message posted via AccessMonster.com
Click to expand...
 
Ayo,

It sure looks like your code should work. What version of Access are you
using?

Try loading the Form with subform, and then just playing around with the
syntax in the immediate window.

HTH
Dale

Ayo said:
I just tried it using:
For Each ctrl In Me.tblMilestones_subform.Form.Controls
If ctrl.Tag = "General" Then
Me.tblMilestones_subform.Form.Controls(ctrl.Name).ColumnWidth
= 0
End If
Next
It didn't work for me. The code ran with out any problem except the
columns
are still in the sibform.

Dale_Fye via AccessMonster.com said:
Well,

You can obviously drag the margins of the column in the subform, but to
do it
via code you are going to have to do something like the following. When
I
opened a datasheet as the form, I was able to go to the immediate window
and
type:

forms("frm_Datasheet").Controls("txt_Field1").columnwidth = 0

and it hid the column.

When I put the datasheet in a form as a subform, and gave the subform
control
the name "sub_datasheet" I was able to hide the column with the
following:

forms("frm_Datasheet_Parent").sub_Datasheet.controls("txt_Field1").
ColumnWidth = 0

To make it visible again, give the ColumnWidth property a value in twips
(inches * 1440)

HTH
Dale


Yes it is.

Is this subform a DataSheet?

[quoted text clipped - 3 lines]
Any ideas.
Thanks
Click to expand...

--
HTH

Dale Fye

Message posted via AccessMonster.com
Click to expand...
Click to expand...
 
Ayo,

It sure looks like your code should work. What version of Access are you
using?

Try loading the Form with subform, and then just playing around with the
syntax in the immediate window.

HTH
Dale

Ayo said:
I just tried it using:
For Each ctrl In Me.tblMilestones_subform.Form.Controls
If ctrl.Tag = "General" Then
Me.tblMilestones_subform.Form.Controls(ctrl.Name).ColumnWidth
= 0
End If
Next
It didn't work for me. The code ran with out any problem except the
columns
are still in the sibform.

Dale_Fye via AccessMonster.com said:
Well,

You can obviously drag the margins of the column in the subform, but to
do it
via code you are going to have to do something like the following. When
I
opened a datasheet as the form, I was able to go to the immediate window
and
type:

forms("frm_Datasheet").Controls("txt_Field1").columnwidth = 0

and it hid the column.

When I put the datasheet in a form as a subform, and gave the subform
control
the name "sub_datasheet" I was able to hide the column with the
following:

forms("frm_Datasheet_Parent").sub_Datasheet.controls("txt_Field1").
ColumnWidth = 0

To make it visible again, give the ColumnWidth property a value in twips
(inches * 1440)

HTH
Dale


Yes it is.

Is this subform a DataSheet?

[quoted text clipped - 3 lines]
Any ideas.
Thanks
Click to expand...

--
HTH

Dale Fye

Message posted via AccessMonster.com
Click to expand...
Click to expand...
 
Yes it is.
Click to expand...

Then use a Continous Form instead. Datasheet cells don't have a "visible"
property (you can shrink them to zero width), continuous form Textboxes do.
 
Yes it is.
Click to expand...

Then use a Continous Form instead. Datasheet cells don't have a "visible"
property (you can shrink them to zero width), continuous form Textboxes do.
 
well, first of all, make sure you're using the right reference for the
subform. you need the name of the *subform control within the mainform*. to
get that, open the mainform in Design view. within Design view, click *once*
on the subform, to select it. in the Properties box, click on the Other tab
and look at the Name property. that's the name of the subform control. then
try the following, as

Dim ctl As Control frm As Form

Set frm = Me!SubformControlName.Form

For Each ctl In frm.Controls
ctl.ColumnHidden = (ctl.Tag = "General")
Next

replace SubformControlName with the correct name that you got from the
exercise in the previous paragraph, of course.

hth


Ayo said:
I just tried it using:
For Each ctrl In Me.tblMilestones_subform.Form.Controls
If ctrl.Tag = "General" Then
Me.tblMilestones_subform.Form.Controls(ctrl.Name).ColumnWidth = 0
End If
Next
It didn't work for me. The code ran with out any problem except the columns
are still in the sibform.

Dale_Fye via AccessMonster.com said:
Well,

You can obviously drag the margins of the column in the subform, but to do it
via code you are going to have to do something like the following. When I
opened a datasheet as the form, I was able to go to the immediate window and
type:

forms("frm_Datasheet").Controls("txt_Field1").columnwidth = 0

and it hid the column.

When I put the datasheet in a form as a subform, and gave the subform control
the name "sub_datasheet" I was able to hide the column with the following:

forms("frm_Datasheet_Parent").sub_Datasheet.controls("txt_Field1").
ColumnWidth = 0

To make it visible again, give the ColumnWidth property a value in twips
(inches * 1440)

HTH
Dale


Yes it is.

Is this subform a DataSheet?

[quoted text clipped - 3 lines]
Any ideas.
Thanks
Click to expand...

--
HTH

Dale Fye

Message posted via AccessMonster.com
Click to expand...
Click to expand...
 
well, first of all, make sure you're using the right reference for the
subform. you need the name of the *subform control within the mainform*. to
get that, open the mainform in Design view. within Design view, click *once*
on the subform, to select it. in the Properties box, click on the Other tab
and look at the Name property. that's the name of the subform control. then
try the following, as

Dim ctl As Control frm As Form

Set frm = Me!SubformControlName.Form

For Each ctl In frm.Controls
ctl.ColumnHidden = (ctl.Tag = "General")
Next

replace SubformControlName with the correct name that you got from the
exercise in the previous paragraph, of course.

hth


Ayo said:
I just tried it using:
For Each ctrl In Me.tblMilestones_subform.Form.Controls
If ctrl.Tag = "General" Then
Me.tblMilestones_subform.Form.Controls(ctrl.Name).ColumnWidth = 0
End If
Next
It didn't work for me. The code ran with out any problem except the columns
are still in the sibform.

Dale_Fye via AccessMonster.com said:
Well,

You can obviously drag the margins of the column in the subform, but to do it
via code you are going to have to do something like the following. When I
opened a datasheet as the form, I was able to go to the immediate window and
type:

forms("frm_Datasheet").Controls("txt_Field1").columnwidth = 0

and it hid the column.

When I put the datasheet in a form as a subform, and gave the subform control
the name "sub_datasheet" I was able to hide the column with the following:

forms("frm_Datasheet_Parent").sub_Datasheet.controls("txt_Field1").
ColumnWidth = 0

To make it visible again, give the ColumnWidth property a value in twips
(inches * 1440)

HTH
Dale


Yes it is.

Is this subform a DataSheet?

[quoted text clipped - 3 lines]
Any ideas.
Thanks
Click to expand...

--
HTH

Dale Fye

Message posted via AccessMonster.com
Click to expand...
Click to expand...
 
I get an "Object required" error on ctl.ColumnHidden = (ctl.Tag = "General")
line.

tina said:
well, first of all, make sure you're using the right reference for the
subform. you need the name of the *subform control within the mainform*. to
get that, open the mainform in Design view. within Design view, click *once*
on the subform, to select it. in the Properties box, click on the Other tab
and look at the Name property. that's the name of the subform control. then
try the following, as

Dim ctl As Control frm As Form

Set frm = Me!SubformControlName.Form

For Each ctl In frm.Controls
ctl.ColumnHidden = (ctl.Tag = "General")
Next

replace SubformControlName with the correct name that you got from the
exercise in the previous paragraph, of course.

hth


Ayo said:
I just tried it using:
For Each ctrl In Me.tblMilestones_subform.Form.Controls
If ctrl.Tag = "General" Then
Me.tblMilestones_subform.Form.Controls(ctrl.Name).ColumnWidth = 0
End If
Next
It didn't work for me. The code ran with out any problem except the columns
are still in the sibform.

Dale_Fye via AccessMonster.com said:
Well,

You can obviously drag the margins of the column in the subform, but to do it
via code you are going to have to do something like the following. When I
opened a datasheet as the form, I was able to go to the immediate window and
type:

forms("frm_Datasheet").Controls("txt_Field1").columnwidth = 0

and it hid the column.

When I put the datasheet in a form as a subform, and gave the subform control
the name "sub_datasheet" I was able to hide the column with the following:

forms("frm_Datasheet_Parent").sub_Datasheet.controls("txt_Field1").
ColumnWidth = 0

To make it visible again, give the ColumnWidth property a value in twips
(inches * 1440)

HTH
Dale



Ayo wrote:
Yes it is.

Is this subform a DataSheet?

[quoted text clipped - 3 lines]
Any ideas.
Thanks

--
HTH

Dale Fye

Message posted via AccessMonster.com
Click to expand...
Click to expand...
Click to expand...
 
I get an "Object required" error on ctl.ColumnHidden = (ctl.Tag = "General")
line.

tina said:
well, first of all, make sure you're using the right reference for the
subform. you need the name of the *subform control within the mainform*. to
get that, open the mainform in Design view. within Design view, click *once*
on the subform, to select it. in the Properties box, click on the Other tab
and look at the Name property. that's the name of the subform control. then
try the following, as

Dim ctl As Control frm As Form

Set frm = Me!SubformControlName.Form

For Each ctl In frm.Controls
ctl.ColumnHidden = (ctl.Tag = "General")
Next

replace SubformControlName with the correct name that you got from the
exercise in the previous paragraph, of course.

hth


Ayo said:
I just tried it using:
For Each ctrl In Me.tblMilestones_subform.Form.Controls
If ctrl.Tag = "General" Then
Me.tblMilestones_subform.Form.Controls(ctrl.Name).ColumnWidth = 0
End If
Next
It didn't work for me. The code ran with out any problem except the columns
are still in the sibform.

Dale_Fye via AccessMonster.com said:
Well,

You can obviously drag the margins of the column in the subform, but to do it
via code you are going to have to do something like the following. When I
opened a datasheet as the form, I was able to go to the immediate window and
type:

forms("frm_Datasheet").Controls("txt_Field1").columnwidth = 0

and it hid the column.

When I put the datasheet in a form as a subform, and gave the subform control
the name "sub_datasheet" I was able to hide the column with the following:

forms("frm_Datasheet_Parent").sub_Datasheet.controls("txt_Field1").
ColumnWidth = 0

To make it visible again, give the ColumnWidth property a value in twips
(inches * 1440)

HTH
Dale



Ayo wrote:
Yes it is.

Is this subform a DataSheet?

[quoted text clipped - 3 lines]
Any ideas.
Thanks

--
HTH

Dale Fye

Message posted via AccessMonster.com
Click to expand...
Click to expand...
Click to expand...
 
Now I am getting "Object doesn't support this property or method" error

tina said:
well, first of all, make sure you're using the right reference for the
subform. you need the name of the *subform control within the mainform*. to
get that, open the mainform in Design view. within Design view, click *once*
on the subform, to select it. in the Properties box, click on the Other tab
and look at the Name property. that's the name of the subform control. then
try the following, as

Dim ctl As Control frm As Form

Set frm = Me!SubformControlName.Form

For Each ctl In frm.Controls
ctl.ColumnHidden = (ctl.Tag = "General")
Next

replace SubformControlName with the correct name that you got from the
exercise in the previous paragraph, of course.

hth


Ayo said:
I just tried it using:
For Each ctrl In Me.tblMilestones_subform.Form.Controls
If ctrl.Tag = "General" Then
Me.tblMilestones_subform.Form.Controls(ctrl.Name).ColumnWidth = 0
End If
Next
It didn't work for me. The code ran with out any problem except the columns
are still in the sibform.

Dale_Fye via AccessMonster.com said:
Well,

You can obviously drag the margins of the column in the subform, but to do it
via code you are going to have to do something like the following. When I
opened a datasheet as the form, I was able to go to the immediate window and
type:

forms("frm_Datasheet").Controls("txt_Field1").columnwidth = 0

and it hid the column.

When I put the datasheet in a form as a subform, and gave the subform control
the name "sub_datasheet" I was able to hide the column with the following:

forms("frm_Datasheet_Parent").sub_Datasheet.controls("txt_Field1").
ColumnWidth = 0

To make it visible again, give the ColumnWidth property a value in twips
(inches * 1440)

HTH
Dale



Ayo wrote:
Yes it is.

Is this subform a DataSheet?

[quoted text clipped - 3 lines]
Any ideas.
Thanks

--
HTH

Dale Fye

Message posted via AccessMonster.com
Click to expand...
Click to expand...
Click to expand...
 
Now I am getting "Object doesn't support this property or method" error

tina said:
well, first of all, make sure you're using the right reference for the
subform. you need the name of the *subform control within the mainform*. to
get that, open the mainform in Design view. within Design view, click *once*
on the subform, to select it. in the Properties box, click on the Other tab
and look at the Name property. that's the name of the subform control. then
try the following, as

Dim ctl As Control frm As Form

Set frm = Me!SubformControlName.Form

For Each ctl In frm.Controls
ctl.ColumnHidden = (ctl.Tag = "General")
Next

replace SubformControlName with the correct name that you got from the
exercise in the previous paragraph, of course.

hth


Ayo said:
I just tried it using:
For Each ctrl In Me.tblMilestones_subform.Form.Controls
If ctrl.Tag = "General" Then
Me.tblMilestones_subform.Form.Controls(ctrl.Name).ColumnWidth = 0
End If
Next
It didn't work for me. The code ran with out any problem except the columns
are still in the sibform.

Dale_Fye via AccessMonster.com said:
Well,

You can obviously drag the margins of the column in the subform, but to do it
via code you are going to have to do something like the following. When I
opened a datasheet as the form, I was able to go to the immediate window and
type:

forms("frm_Datasheet").Controls("txt_Field1").columnwidth = 0

and it hid the column.

When I put the datasheet in a form as a subform, and gave the subform control
the name "sub_datasheet" I was able to hide the column with the following:

forms("frm_Datasheet_Parent").sub_Datasheet.controls("txt_Field1").
ColumnWidth = 0

To make it visible again, give the ColumnWidth property a value in twips
(inches * 1440)

HTH
Dale



Ayo wrote:
Yes it is.

Is this subform a DataSheet?

[quoted text clipped - 3 lines]
Any ideas.
Thanks

--
HTH

Dale Fye

Message posted via AccessMonster.com
Click to expand...
Click to expand...
Click to expand...
 
hmm, yeah, me too. i'd never tried this before, so i'm surprised to see that
apparently Access doesn't recognize that the subform is in Datasheet view.
both ColumnHidden and ColumnWidth properties are specific to form Datasheet
view - they're not valid in SingleForm view or ContinuousForms view. let me
play with this a bit, and see if i can come up with a solution. i'll post
back today.


Ayo said:
Now I am getting "Object doesn't support this property or method" error

tina said:
well, first of all, make sure you're using the right reference for the
subform. you need the name of the *subform control within the mainform*. to
get that, open the mainform in Design view. within Design view, click *once*
on the subform, to select it. in the Properties box, click on the Other tab
and look at the Name property. that's the name of the subform control. then
try the following, as

Dim ctl As Control frm As Form

Set frm = Me!SubformControlName.Form

For Each ctl In frm.Controls
ctl.ColumnHidden = (ctl.Tag = "General")
Next

replace SubformControlName with the correct name that you got from the
exercise in the previous paragraph, of course.

hth


Ayo said:
I just tried it using:
For Each ctrl In Me.tblMilestones_subform.Form.Controls
If ctrl.Tag = "General" Then
Click to expand...
Me.tblMilestones_subform.Form.Controls(ctrl.Name).ColumnWidth
= 0
End If
Next
It didn't work for me. The code ran with out any problem except the columns
are still in the sibform.

:

Well,

You can obviously drag the margins of the column in the subform, but
Click to expand...
to
do it
via code you are going to have to do something like the following.
Click to expand...
When
I
opened a datasheet as the form, I was able to go to the immediate
Click to expand...
window
and
type:

forms("frm_Datasheet").Controls("txt_Field1").columnwidth = 0

and it hid the column.

When I put the datasheet in a form as a subform, and gave the
Click to expand...
subform
control
the name "sub_datasheet" I was able to hide the column with the following:

forms("frm_Datasheet_Parent").sub_Datasheet.controls("txt_Field1").
ColumnWidth = 0

To make it visible again, give the ColumnWidth property a value in twips
(inches * 1440)

HTH
Dale



Ayo wrote:
Yes it is.

Is this subform a DataSheet?

[quoted text clipped - 3 lines]
Any ideas.
Thanks

--
HTH

Dale Fye

Message posted via AccessMonster.com
Click to expand...
Click to expand...
Click to expand...
 
Back
Top