How to stop column changes in table in subform.

[Guest]

Hi all,

I need to find a way to stop user from changing the width of columns in
table of a subform.

Any ideas?

Thanks in advance,
Emilio

 

[Brendan Reynolds]

Here's a reply I posted recently to a similar question ...

http://groups.google.ie/group/microsoft.public.access.gettingstarted/msg/7850745fdcbe42da

 

[Guest]

Thanks for your help, I tried but the problem is that I previously made
adjustments to the columns since they need to be a certain size and they all
seem to change size after adding the code, I am assuming to the original
width.
Is there any way to work around this.

Thanks again,
Emilio

 

[Brendan Reynolds]

The example sets the ColumnWidth property to -1, which sets them to the
default width. To restore them to their previous width, you need to save
that somewhere when opening the form. There are various options for where
you could save those values. I like to use the AccessObjectProperties
collection of the AccessObject object for the form, as it keeps the data
together with the form to which it applies. The code below would be entered
in the code module for the main form that contains the subform ...

Option Compare Database
Option Explicit

Private Sub Form_Open(Cancel As Integer)

Dim aob As AccessObject
Dim ctls As Controls
Dim ctl As Control

'if testing with a new form, be sure to save the form before running
this code -
'the AccessObject object doesn't exist until the form has been saved.
Set aob = CurrentProject.AllForms(Me.Name)
'sfrTest is the name of the subform control
Set ctls = Me.sfrTest.Controls
For Each ctl In ctls
If ctl.ControlType = acTextBox Or ctl.ControlType = acComboBox Then
aob.Properties.Add ctl.Name & "ColumnWidth", ctl.ColumnWidth
End If
Next ctl
Me.TimerInterval = 250

End Sub

Private Sub Form_Timer()

Dim aob As AccessObject
Dim ctls As Controls
Dim ctl As Control

'Make sure the timer event doesn't get
'called again while we're still processing.
Me.TimerInterval = 0
Set aob = CurrentProject.AllForms(Me.Name)
Set ctls = Me.sfrTest.Controls
For Each ctl In ctls
If ctl.ControlType = acTextBox Or ctl.ControlType = acComboBox Then
ctl.ColumnWidth = aob.Properties(ctl.Name & "ColumnWidth")
End If
Next ctl
Me.TimerInterval = 250

End Sub

 

[Guest]

Hi Brendan,

I made the chnges below, the form I am working on is a Calendar and has 7
subforms, it works only up to 2 subforms and slows the showing of conditional
formatting I tried different timer settings but don't know if that is the
answer.

Thanks for your patience,
Emilio


Private Sub Form_Open(Cancel As Integer)

Dim aob As AccessObject
Dim ctls As Controls
Dim ctl As Control

'if testing with a new form, be sure to save the form before running
'this code -
'the AccessObject object doesn't exist until the form has been saved.
Set aob = CurrentProject.AllForms(Me.Name)

Set ctls = Me.JobScheduleSubformFriday.Controls
Set ctls = Me.JobScheduleSubformMonday.Controls
Set ctls = Me.JobScheduleSubformSaturday.Controls
Set ctls = Me.JobScheduleSubformSunday.Controls
Set ctls = Me.JobScheduleSubformThursday.Controls
Set ctls = Me.JobScheduleSubformTuesday.Controls
Set ctls = Me.JobScheduleSubformWednesday.Controls

For Each ctl In ctls
If ctl.ControlType = acTextBox Or ctl.ControlType = acComboBox Then
aob.Properties.Add ctl.Name & "ColumnWidth", ctl.ColumnWidth
End If
Next ctl
Me.TimerInterval = 250

End Sub

Private Sub Form_Timer()

Dim aob As AccessObject
Dim ctls As Controls
Dim ctl As Control

'Make sure the timer event doesn't get
'called again while we're still processing.
Me.TimerInterval = 0
Set aob = CurrentProject.AllForms(Me.Name)

Set ctls = Me.JobScheduleSubformFriday.Controls
Set ctls = Me.JobScheduleSubformMonday.Controls
Set ctls = Me.JobScheduleSubformSaturday.Controls
Set ctls = Me.JobScheduleSubformSunday.Controls
Set ctls = Me.JobScheduleSubformThursday.Controls
Set ctls = Me.JobScheduleSubformTuesday.Controls
Set ctls = Me.JobScheduleSubformWednesday.Controls

For Each ctl In ctls
If ctl.ControlType = acTextBox Or ctl.ControlType = acComboBox Then
ctl.ColumnWidth = aob.Properties(ctl.Name & "ColumnWidth")
End If
Next ctl
Me.TimerInterval = 250

End Sub

 

[John Vinson]

I need to find a way to stop user from changing the width of columns in
table of a subform.

One way that I prefer is to avoid the use of Datasheet forms
altogether.

You can use a Continuous form instead, and (if you like the look) make
it resemble a Datasheet pretty easily; but it gives you a lot more
flexibility with the subform headers and footers, and does not allow
the users to change or hide columns.

John W. Vinson[MVP]

 

[Guest]

Thanks for your help, unfortunately it takes more room than datasheet and I
am already tight as it is.
Thanks,
Emilio

 

[John Vinson]

Thanks for your help, unfortunately it takes more room than datasheet and I
am already tight as it is.

Um?

You can make the textboxes on a continuous Form any size you like; you
would slide them to the very top of the detail section, with no gap
between them; you can turn off scroll bars; if you're really really
tight because of the number of fields, you can stack fields in two
rows, which you cannot do in a datasheet.

Take another look. I can't imagine anything that will fit on a
datasheet that cannot be fit just as well using a continuous form,
with a little work!

John W. Vinson[MVP]

 

[Brendan Reynolds]

We only need to process each form when it is active - there's no need to
have the code in the timer event of subform3 running when subform1 is
active. The example that follows has quite a bit of code that is repeated
for each subform. It would be more elegant to move the repeated code into a
form-level procedure that could be called from the Enter event of each
subform control. But this is just quick-and-dirty example code - hopefully
you can take it yourself from here.

Behind the main form ..

Option Compare Database
Option Explicit

Private Sub sfrTest1_Enter()

'Save the initial column widths of each subform in the enter event of
the subform
'control, and start the timer for that subform only.

Dim aob As AccessObject
Dim ctls As Controls
Dim ctl As Control

Set aob = CurrentProject.AllForms(Me.sfrTest1.Form.Name)
Set ctls = Me.sfrTest1.Form.Controls
For Each ctl In ctls
If ctl.ControlType = acTextBox Or ctl.ControlType = acComboBox Then
aob.Properties.Add ctl.Name & "ColumnWidth", ctl.ColumnWidth
End If
Next ctl
Me.sfrTest1.Form.TimerInterval = 250

End Sub

Private Sub sfrTest1_Exit(Cancel As Integer)

'Stop the timer in the exit event of the subform control. No need to
process
'a subform when it doesn't have the focus.
Me.sfrTest1.Form.TimerInterval = 0

End Sub

Private Sub sfrTest2_Enter()

Dim aob As AccessObject
Dim ctls As Controls
Dim ctl As Control

Set aob = CurrentProject.AllForms(Me.sfrTest2.Form.Name)
Set ctls = Me.sfrTest2.Form.Controls
For Each ctl In ctls
If ctl.ControlType = acTextBox Or ctl.ControlType = acComboBox Then
aob.Properties.Add ctl.Name & "ColumnWidth", ctl.ColumnWidth
End If
Next ctl
Me.sfrTest2.Form.TimerInterval = 250

End Sub

Private Sub sfrTest2_Exit(Cancel As Integer)

Me.sfrTest2.Form.TimerInterval = 0

End Sub

Private Sub sfrTest3_Enter()

Dim aob As AccessObject
Dim ctls As Controls
Dim ctl As Control

Set aob = CurrentProject.AllForms(Me.sfrTest3.Form.Name)
Set ctls = Me.sfrTest3.Form.Controls
For Each ctl In ctls
If ctl.ControlType = acTextBox Or ctl.ControlType = acComboBox Then
aob.Properties.Add ctl.Name & "ColumnWidth", ctl.ColumnWidth
End If
Next ctl
Me.sfrTest3.Form.TimerInterval = 250

End Sub

Private Sub sfrTest3_Exit(Cancel As Integer)

Me.sfrTest3.Form.TimerInterval = 0

End Sub

Behind each subform ...

Private Sub Form_Timer()

Dim aob As AccessObject
Dim ctls As Controls
Dim ctl As Control

'Make sure the timer event doesn't get
'called again while we're still processing.
Me.TimerInterval = 0
Set aob = CurrentProject.AllForms(Me.Name)
Set ctls = Me.Controls
For Each ctl In ctls
If ctl.ControlType = acTextBox Or ctl.ControlType = acComboBox Then
ctl.ColumnWidth = aob.Properties(ctl.Name & "ColumnWidth")
End If
Next ctl
Me.TimerInterval = 250

End Sub