can't set columnwidth in a subform

[brian.capel]

I have added VBA code in my application to set the columnwidth on a
field in my subform but it won't take, it's like the widths are frozen.
If I display the field width both before and after the columnwidth
statement the width stays the same. I have put this logic in the
subform Load and Open events. This same logic works in another Access
application I have but not this one. I can't seem to find what I am
doing wrong, any ideas?

 

[Biz Enhancer]

Post your code so others can see it including version of Access you're
working in. Then we may be able to shed some light on it.

regards,
Nick.

 

[Geoff]

Brian:

If you created the main form using the AutoForm toolbar icon, then the
subform control's Source Object property will contain something like
"Table.TableName". In this case the subform control is still a
"SubForm/SubReport" control, but it contains a direct reference to the
table. If you open the main form in design view and double-click the subform
control, the table will open in design view. In this case, there is no Open
or Load events for the "SubForm" when the main form is opened.

In contrast, if you create a subform yourself and drag it to the design
surface of the main form, then the subform control's Source Object property
will contain something like "TableName". In this case the subform control is
also a "SubForm/SubReport" control but now Open and Load events for the
subform will fire when the main form is opened.

Perhaps you've created a subform but didn't drag it to the main form in
design view. This can lead to confusion because, when you open the main form
in normal view, to all intents and purposes the subform may look like the
subform you created but in fact it may not be. I can't think why else the
subform's Open and Load events wouldn't fire.

Double-check that your main form really contains the subform you created.

Regards
Geoff

 

[Geoff]

Correction:

Paragraph 2 of my previous post should have said that, when the
"SubForm/SubReport" control contains a sub form, the Object Source property
of the control is the name of the subform, eg "frmFormName", not
"TableName". Hope that didn't throw you!

Geoff

 

[brian.capel]

Geoff,

The On open and On load events are firing (sorry about the confusion)
but it's the columnwidth command that's not working for some reason.
Here is the code attached to my subform:

Private Sub Form_Open(Cancel As Integer)

MsgBox Me.parentpart.ColumnWidth
Me![parentpart].ColumnWidth = 5000
MsgBox Me.parentpart.ColumnWidth

End Sub

Both MsgBox's display the same columnwidth (in this case 1120). If the
columnwidth command was working correctly the second Msgbox should
display 5000, but it isn't. And no columns are frozen either. What's
wierd is that this same type logic works in another Access application
I have so there must be some setting somewhere I'm missing?

Regards, Brian

 

[Geoff]

Brian,

Your code works fine on my PC (running Access 2002).

I can't think off-hand what the problem might be.

What happens if you run the main form and drag the right border of the
[parentpart] column selector? Can you change the column width that way?

And/or, can you run the main form, select the [parentpart] column, open the
Format menu, select Column Width, and change the column width that way?

Something must be different about your other applications that work and this
one that doesn't. I'm afraid I don't know what that might be. Perhaps
searching the Microsoft website might give you a clue.

Geoff.

Geoff,

The On open and On load events are firing (sorry about the confusion)
but it's the columnwidth command that's not working for some reason.
Here is the code attached to my subform:

Private Sub Form_Open(Cancel As Integer)

MsgBox Me.parentpart.ColumnWidth
Me![parentpart].ColumnWidth = 5000
MsgBox Me.parentpart.ColumnWidth

End Sub

Both MsgBox's display the same columnwidth (in this case 1120). If the
columnwidth command was working correctly the second Msgbox should
display 5000, but it isn't. And no columns are frozen either. What's
wierd is that this same type logic works in another Access application
I have so there must be some setting somewhere I'm missing?

Regards, Brian

Correction:

Paragraph 2 of my previous post should have said that, when the
"SubForm/SubReport" control contains a sub form, the Object Source
property
of the control is the name of the subform, eg "frmFormName", not
"TableName". Hope that didn't throw you!

Geoff

 

[brian.capel]

Geoff,

Yes, I can do all the things you mention.

Thought I would give it one last chance so I did a property by property
compare of my database that works to the one that doesn't work and I
found the problem. On the format tab of my text box I had the
"Visible" property set to "No". Once I changed it to Yes the
columnwidths command worked perfectly! Thought I would let you know in
case anybody else has this problem. Thanks for your help, this forum
is a wealth of information.

Regards, Brian

Brian,

Your code works fine on my PC (running Access 2002).

I can't think off-hand what the problem might be.

What happens if you run the main form and drag the right border of the
[parentpart] column selector? Can you change the column width that way?

And/or, can you run the main form, select the [parentpart] column, open the
Format menu, select Column Width, and change the column width that way?

Something must be different about your other applications that work and this
one that doesn't. I'm afraid I don't know what that might be. Perhaps
searching the Microsoft website might give you a clue.

Geoff.

Geoff,

The On open and On load events are firing (sorry about the confusion)
but it's the columnwidth command that's not working for some reason.
Here is the code attached to my subform:

Private Sub Form_Open(Cancel As Integer)

MsgBox Me.parentpart.ColumnWidth
Me![parentpart].ColumnWidth = 5000
MsgBox Me.parentpart.ColumnWidth

End Sub

Both MsgBox's display the same columnwidth (in this case 1120). If the
columnwidth command was working correctly the second Msgbox should
display 5000, but it isn't. And no columns are frozen either. What's
wierd is that this same type logic works in another Access application
I have so there must be some setting somewhere I'm missing?

Regards, Brian

Correction:

Paragraph 2 of my previous post should have said that, when the
"SubForm/SubReport" control contains a sub form, the Object Source
property
of the control is the name of the subform, eg "frmFormName", not
"TableName". Hope that didn't throw you!

Geoff

Brian:

If you created the main form using the AutoForm toolbar icon, then the
subform control's Source Object property will contain something like
"Table.TableName". In this case the subform control is still a
"SubForm/SubReport" control, but it contains a direct reference to the
table. If you open the main form in design view and double-click the
subform control, the table will open in design view. In this case,
there
is no Open or Load events for the "SubForm" when the main form is
opened.

In contrast, if you create a subform yourself and drag it to the design
surface of the main form, then the subform control's Source Object
property will contain something like "TableName". In this case the
subform
control is also a "SubForm/SubReport" control but now Open and Load
events
for the subform will fire when the main form is opened.

Perhaps you've created a subform but didn't drag it to the main form in
design view. This can lead to confusion because, when you open the main
form in normal view, to all intents and purposes the subform may look
like
the subform you created but in fact it may not be. I can't think why
else
the subform's Open and Load events wouldn't fire.

Double-check that your main form really contains the subform you
created.

Regards
Geoff


I have added VBA code in my application to set the columnwidth on a
field in my subform but it won't take, it's like the widths are
frozen.
If I display the field width both before and after the columnwidth
statement the width stays the same. I have put this logic in the
subform Load and Open events. This same logic works in another Access
application I have but not this one. I can't seem to find what I am
doing wrong, any ideas?

 

[Geoff]

Brian

Many thanks for letting the group know what you turned up. That is
unexpected - well it was to me.

I never gave the Visible property a thought because I assumed (wrongly)
that, if the Visible property of a Textbox on a Form were set to False,
Access would automatically set the ColumnHidden property to True when in
Datasheet view. But it seems the Visible and ColumnHidden properties are
established to work independently for (Form and Datasheet view
respectively), which makes perfect sense after you realise it works this
way. (It would be nice if the Help topic on the Visible, ColumnWidth and
ColumnHidden properties went on to offer a bit more information in their
Remarks paragraphs concerning this behaviour.)

I'm glad you sorted it out. It's another nice piece of information
Developers need in their armoury.

Regards
Geoff

Geoff,

Yes, I can do all the things you mention.

Thought I would give it one last chance so I did a property by property
compare of my database that works to the one that doesn't work and I
found the problem. On the format tab of my text box I had the
"Visible" property set to "No". Once I changed it to Yes the
columnwidths command worked perfectly! Thought I would let you know in
case anybody else has this problem. Thanks for your help, this forum
is a wealth of information.

Regards, Brian

Brian,

Your code works fine on my PC (running Access 2002).

I can't think off-hand what the problem might be.

What happens if you run the main form and drag the right border of the
[parentpart] column selector? Can you change the column width that way?

And/or, can you run the main form, select the [parentpart] column, open
the
Format menu, select Column Width, and change the column width that way?

Something must be different about your other applications that work and
this
one that doesn't. I'm afraid I don't know what that might be. Perhaps
searching the Microsoft website might give you a clue.

Geoff.

Geoff,

The On open and On load events are firing (sorry about the confusion)
but it's the columnwidth command that's not working for some reason.
Here is the code attached to my subform:

Private Sub Form_Open(Cancel As Integer)

MsgBox Me.parentpart.ColumnWidth
Me![parentpart].ColumnWidth = 5000
MsgBox Me.parentpart.ColumnWidth

End Sub

Both MsgBox's display the same columnwidth (in this case 1120). If the
columnwidth command was working correctly the second Msgbox should
display 5000, but it isn't. And no columns are frozen either. What's
wierd is that this same type logic works in another Access application
I have so there must be some setting somewhere I'm missing?

Regards, Brian

Geoff wrote:
Correction:

Paragraph 2 of my previous post should have said that, when the
"SubForm/SubReport" control contains a sub form, the Object Source
property
of the control is the name of the subform, eg "frmFormName", not
"TableName". Hope that didn't throw you!

Geoff

Brian:

If you created the main form using the AutoForm toolbar icon, then
the
subform control's Source Object property will contain something like
"Table.TableName". In this case the subform control is still a
"SubForm/SubReport" control, but it contains a direct reference to
the
table. If you open the main form in design view and double-click the
subform control, the table will open in design view. In this case,
there
is no Open or Load events for the "SubForm" when the main form is
opened.

In contrast, if you create a subform yourself and drag it to the
design
surface of the main form, then the subform control's Source Object
property will contain something like "TableName". In this case the
subform
control is also a "SubForm/SubReport" control but now Open and Load
events
for the subform will fire when the main form is opened.

Perhaps you've created a subform but didn't drag it to the main form
in
design view. This can lead to confusion because, when you open the
main
form in normal view, to all intents and purposes the subform may
look
like
the subform you created but in fact it may not be. I can't think why
else
the subform's Open and Load events wouldn't fire.

Double-check that your main form really contains the subform you
created.

Regards
Geoff


I have added VBA code in my application to set the columnwidth on a
field in my subform but it won't take, it's like the widths are
frozen.
If I display the field width both before and after the columnwidth
statement the width stays the same. I have put this logic in the
subform Load and Open events. This same logic works in another
Access
application I have but not this one. I can't seem to find what I
am
doing wrong, any ideas?