AllowEdits only for specify Column

[dand00s]

Hello everyone,

I have some problem with my form. I set property in main form at non editable but I want set allowedits in VBA code when form open only for specify column.

My code with set focus for specify column and enable editing for all form is below but i want enable editing only for one column "M12"

Private Sub Form_Open(Cancel As Integer)
Dim strPeriod As String
Dim strColname As String
' in this example Me.mPeriod = 201312
strPeriod = Right(Me.mPeriod, 2) 'result 12
strColname = "M" & strPeriod ' result M12

' column in forms name like M01, M02, M03,...., M12

Me.Controls(strColname).SetFocus

Select Case strColname

Case "M12"
Me.AllowEdits = True

End Select

End Sub

Thanks
DT

 

[Mihail]

As far as I know, what you try is not possible.
You can workaround:
The form itself should allow edits at all.
Each control (field in your terminology) where the edits are not allowed should be locked.
So you need something like this (not tested):

Dim ctl As Control
For Each ctl in Controls
  ctl.Locked = (ctl.Name <> strColName)
  If ctl.Name = strColName Then
    ctl.SetFocus
  End If
Next ctl

 

[gbnz]

I don't know if this would help...but I would change Form property to Allow Edits= Yes and then lock down the fields that you want locked from the property page.

 

[dand00s]

Thanks for your replay.

Ok I tried this but when I debug I have Run-time error '438' Object doesn't support this method. I mean ctl.Locked .
My Access it not recognized this method. SetFocus work fine.

Any suggestion?

Private Sub Form_Open(Cancel As Integer)

 Dim strPeriod As String
 Dim strColname As String
 Dim ctl As Control
 
 strPeriod = Right(Me.mPeriod, 2)
 strColname = "M" & strPeriod
 
 
 
    For Each ctl In Me.Controls
     If ctl.name <> strColname Then
        ctl.Locked
     End If
    If ctl.name = strColname Then
        ctl.SetFocus
    End If
    
    Next ctl
    
 'Me.Controls(strColname).SetFocus
    
 
End Sub

Thanks

 

[Mihail]

I don't know if this would help...but I would change Form property to Allow Edits= Yes and then lock down the fields that you want locked from the property page.

Can you be a bit more explicit ?
How to do this at run time for a calculated control name ?

 

[Mihail]

Thanks for your replay.

Ok I tried this but when I debug I have Run-time error '438' Object doesn't support this method. I mean ctl.Locked .
My Access it not recognized this method. SetFocus work fine.

Any suggestion?

Private Sub Form_Open(Cancel As Integer)

 Dim strPeriod As String
 Dim strColname As String
 Dim ctl As Control
 
 strPeriod = Right(Me.mPeriod, 2)
 strColname = "M" & strPeriod
 
 
 
    For Each ctl In Me.Controls
     If ctl.name <> strColname Then
        ctl.Locked
     End If
    If ctl.name = strColname Then
        ctl.SetFocus
    End If
    
    Next ctl
    
 'Me.Controls(strColname).SetFocus
    
 
End Sub

Thanks

Your code is not like my.

 

[CJ_London]

have Run-time error '438' Object doesn't support this method

This is because you are going through each control - some controls, e.g. labels, do not have a locked property.

You can solve this either by putting

On Error Resume Next

before the start of your loop which will ignore the error or by modifying your code so that only those controls with a lock property are processed.

This is also required for Mihail's solution as well

 

[Mihail]

Other solution that avoid the On Error Resume Next (is a very dangerous statement, in my opinion).
Locate the "tag" property in the Property Sheet (Other tab).
For each control that should be Locked/Unlocked write the same text (i.e "LU") in the Tag property.

Then use this code:

Dim ctl As Control
 For Each ctl in Controls
  If ctl.Tag = "LU" then
    ctl.Locked = (ctl.Name <> strColName)
    If ctl.Name = strColName Then
      ctl.SetFocus
    End If
  End If
 Next ctl

 

[dand00s]

Hi,

Mihail thank you very much for your advice but your solution not work for me. I have changed it a little bit and now works well.
All controls in form I set to Locked and only one in code unlocked when I need

 Private Sub Form_Open(Cancel As Integer)
On Error Resume Next
  Dim strPeriod As String
 Dim strColname As String
  
 strPeriod = Right(Me.mPeriod, 2)
 strColname = "M" & strPeriod
 Dim ctl As Control
 
    For Each ctl In Me.Controls
    
    If ctl.name = strColname Then
      ctl.Locked = False
      ctl.SetFocus
    End If
   
   Next ctl

    
 
End Sub

 

[Mihail]

I'm happy for you.

But be aware: your code will lock any control in your form (except the one that has the name you calculate).
So, put a bookmark in your browser to this thread. If you will have troubles, take a look to my previous post.

Good luck !