asked on

Restricting form fields in MS Access 2007

Hello,

I have a MS Access 2007 DB; in the forms, whenever the user saves/exits and brings the same information back up in the form, I want all of the populated fields to be disabled from editing.

Is this possible without restricting access to the empty fields? I want the user to be able to edit those fields after the original fields are disabled.

Thanks

ASKER CERTIFIED SOLUTION

omgang

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

omgang

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

--TripWire--

ASKER

Thanks!

--TripWire--

ASKER

I've noticed that some of the CheckBox fields will allow user interaction, while others will not.
If the CheckBox is blank on the form, I would the user to be able to check it. But if it's already checked, I don't want the user to be able to uncheck it. How do I go about this?

omgang

I don't think I tested the checkbox controls because the routine doesn't work as-is for them.

Here's updated sub that includes proper check for Checkbox control values - Checkbox is Boolean (Yes/No, True/False, -1/0)
OM Gang

Private Sub Form_Current()

On Error GoTo Err_Form_Current

Dim ctl As Control

For Each ctl In Me.Form.Controls

'Debug.Print ctl.Name & " : " & ctl.ControlType

Select Case ctl.ControlType
Case 106 '106 = CheckBox
If Me(ctl.Name) = 0 Then
Me(ctl.Name).Locked = False
Else
Me(ctl.Name).Locked = True
End If

Case 109 '109 = TextBox
If Nz(Me(ctl.Name), "") <> "" Then
Me(ctl.Name).Locked = True
Else
Me(ctl.Name).Locked = False
End If

Case 110, 111 '110 = ListBox, 111 = ComboBox
If Nz(Me(ctl.Name).Column(0), "") <> "" Then
Me(ctl.Name).Locked = True
Else
Me(ctl.Name).Locked = False
End If

Case Else

End Select

Next

Exit_Form_Current:
Set ctl = Nothing
Exit Sub

Err_Form_Current:
MsgBox Err.Number & " (" & Err.Description & ") in procedure Form_Current of VBA Document Form_frmCategory"
Resume Exit_Form_Current
End Sub

--TripWire--

ASKER

For some reason it only works when I code it like this...

If Me(ctl.Name) = -1 Then
                    Me(ctl.Name).Locked = True
'skip else statement
End If

Open in new window

Not sure why.
Thanks anyway

omgang

If you don't include the Else part then once the control is locked you have no means to unlock it. That's why I evaluate the control each time and either Lock or Unlock.

OM Gang

--TripWire--

ASKER

Yes, once the checkbox has been locked, I don't want it to be unchecked once you've closed the form.

omgang

On my test form I am able to cycle through records so I set the procedure to run OnCurrent (each time the record changes) and lock/unlock controls each time. If you're only working with one record when the form opens then your change will work just fine.
OM Gang

--TripWire--

ASKER

You're right.
I was able to add the else without problem. *fingers crossed*