Function to Enable/Disable Field

[swalsh84]

I have a form that is used for data entry. I give the user the ability to edit the record and save the record. Based on the whether the field meets certain criteria (mainly filled in or not filled in), the field is available for edit. For example, if the customer name is filled in, the field is disabled and vice versa. Currently, I have code that does the following:

 
Me.fieldName.Enabled = True
Me.fieldName.Locked = False
Me.fieldName.BackStyle = 1
Me.fieldName.SpecialEffect = 2

I am attempting to write a VBA function that will do this for each field that needs to be enabled when the user clicks on the "Edit Record" button. The trouble I'm running into is how to pass the field name to the function and utilize it within the function. Here's what I have so far:

 
Public Function enableField(field As String)
 
    field.Enabled = True
 
 '...etc.
 
    'Me.fieldName.Enabled = True
    'Me.fieldName.Locked = False
    'Me.fieldName.BackStyle = 1
    'Me.fieldName.SpecialEffect = 2
 
End Function

I know it's not much, but it doesn't seem to be a difficult thing to accomplish. I want to save a ton of lines of code because I have about 30 fields per record. Any thoughts?



TIA

[pbaldy]

Presuming the function is in the form's module, try:

Me(field).Enabled = True

[ssanfu]

Maybe this will help. I stole the code from Allen Browne's site at

http://allenbrowne.com/ser-56.html

and modified it. I put a button on a form and named it "LockBoundControls". The code goes in the click event:

Sub LockBoundControls_Click()
   On Error GoTo Err_Handler
   'Purpose:   Lock/Unlock/Enable/Disable controls
   Dim frm As Form
   Dim ctl As Control      'Each control on the form
   Dim lngI As Long        'Loop controller.
   Set frm = Me
   For Each ctl In frm.Controls
      Select Case ctl.ControlType
      'remove any control types you don't want to Lock/Unlock/Enable/Disable 
      Case acTextBox, acComboBox, acListBox, acOptionGroup, acCheckBox, acOptionButton, acToggleButton
         'Lock/unlock these controls if bound to fields.
         If Len(ctl.ControlSource) > 0 And Not ctl.ControlSource Like "=*" Then
            ctl.Enabled = True
            ctl.Locked = False
            ctl.BackStyle = 1
            ctl.SpecialEffect = 2
         End If
      Case acSubform
         'Do nothing
      Case acLabel, acLine, acRectangle, acCommandButton, acTabCtl, acPage, acPageBreak, acImage, acObjectFrame
         'Do nothing
      Case Else
         'Do nothing
      End Select
   Next
Exit_Handler:
   Set ctl = Nothing
   Exit Sub
Err_Handler:
   MsgBox "Error " & Err.Number & " - " & Err.Description
   Resume Exit_Handler
End Sub

.

[pbaldy]

You can also use Allen's code with the Tag property of the controls. Rather than examining the type, you put something in the Tag property of the controls you want affected.

Love the "Veni, Vidi, Velcro" thing!

[swalsh84]

Thanks to both of you. I do have some fields on the form that I did not want to disable so ssanfu's solution (while awesome) doesn't work in my specific case. Final function turned out as such:

 
Public Function enableField(field As String)
 
        Me(field).Enabled = True
        Me(field).Locked = False
        Me(field).BackStyle = 1
        Me(field).SpecialEffect = 2
End Function
Public Function disableField(field As String)
        Me(field).Enabled = False
        Me(field).Locked = True
        Me(field).BackStyle = 0
        Me(field).SpecialEffect = 0
 
End Function

A call to the function would like this:

    'Enable / Disable Source
    If IsNull(Me.cboSource.Value) Then
        enableField (Me.cboSource.NAME)
    Else
        disableField (Me.cboSource.NAME)
    End If

A quick follow up question: If I wanted to place these functions outside the form and use them elsewhere in other forms, how would I go about calling the function? I'm assuming the same way?

Thanks!

[pbaldy]

I suspect you can still pass the field name and use ActiveForm to determine which form is open and active.