Problem with ColumnWidth property setting

[Jim Franklin]

Hi,

I have a form which opens as a datasheet. The form contains a number of
unbound controls which have their controlsource property set in the forms
Open event. (This is because the list of fields can potentially change every
time the form is opened.)

My problem is that I want to fix some of the column widths of the datasheet
so that any adjustments to widths made previously by a user are removed when
the form is opened. I do this by setting the control's .columnwidth property
in the form's Load event.

This has always worked for me (and indeed works for other forms in the same
app.) but for some reason here it has no effect.

The following is an example just for control txtbox1 to simplify things.
every time the form opens, txtbox1 retains the column width of when it was
last closed. Here is all my code for the form:

Private Sub Form_Load()

Me.txtbox1.ColumnWidth = 1000

End Sub

Private Sub Form_Open(Cancel As Integer)

Dim i As Integer
Dim fld As Field

For i = 1 To 100
Me.Controls("txtbox" & i).ColumnHidden = True
Next i

i = 1

For Each fld In Me.RecordsetClone.Fields
If fld.Name = "ProjectID" Or fld.Name = "ProjectDescription" Or
fld.Name = "OracleNumber" Or fld.Name = "PONumber" Then 'do nothing
Else
Me.Controls("lbl" & i).Caption = fld.Name
Me.Controls("txtbox" & i).ControlSource = fld.Name
Me.Controls("txtbox" & i).ColumnHidden = False
i = i + 1
End If
Next fld

End Sub

Note: I have also tried opening the form WITHOUT the Open event code, to see
if something in here is affecting it, but makes no difference.

If anyone has any ideas, I would be very grateful. Thank you!

Jim

 

[Klatuu]

Move the Open event code to the Load event. When the form first opens, it
does not have all it's objects set up yet. It is too early to reference form
objects. It is useful for checking recordset information, or external
values, but not good for form objects.

 

[Jim Franklin]

Thanks Dave, I will give this a go.

Jim

Move the Open event code to the Load event. When the form first opens, it
does not have all it's objects set up yet. It is too early to reference
form
objects. It is useful for checking recordset information, or external
values, but not good for form objects.

 

[Jim Franklin]

Dave, thanks for your suggestion. I have tried moving all the code to the
Load event for the form, but this seems to make no difference. In any case,
the rest of the code seems to work fine, the only problem I have is with
setting the ColumnWidth. The line of code for this is already in the Load
event.

My form is opening as datasheet within a subform control of another form.
Would this make any difference?

I cannot understand why Access is ignoring the command to set ColumnWidth to
1000. Can anyone shed any light on this?

Many thanks,

Jim

 

[Jim Franklin]

As a follow up, I have even tried putting the following code into a command
button on my main form, to be clicked after the subform is loaded.

Me.sfrm_Financials.Form.Controls("txtbox1").ColumnWidth = 1000
MsgBox Me.sfrm_Financials.Form.Controls("txtbox1").ColumnWidth

Once again, the first line is ignored, the width does not change and the
Msgbox returns whatever width the column is currently set to.

If anyone has any ideas, they are very much appreciated! I am completely
stumped!!!

Cheers,
Jim

 

[Jim Franklin]

Just to prevent everyone racking their brains over this.....! I have found
the problem.

Just in case anyone else ever gets this...

The reason my columnwidths were not changing was because the Visible setting
for the controls on my form were set to false. Even though the Visible
setting is NOT supposed to have any effect in Datasheet view (according to
MS Access HELP), changing the property in Form design to YES allows the
column width to be altered in code.

Thank you Microsoft! Grrrrr.....!

Jim