Disabling controls on existing records only

[Pat Dools]

Hello,

I would like to implement code that will disable controls on a given form
only for the existing records. I have a form that opens on database startup
with the following code behind a Command Button to allow me to open a blank
form ("fScrEligCriteria"):

Private Sub CommandNewPatient_Click()
On Error GoTo Err_CommandNewPatient_Click


DoCmd.OpenForm "fScrEligCriteria"
DoCmd.GoToRecord , , acNewRec

Exit_CommandNewPatient_Click:
Exit Sub

Err_CommandNewPatient_Click:
MsgBox Err.Description
Resume Exit_CommandNewPatient_Click

End Sub

I have the following code running, but unfortunately, it disables the
controls for New Records also. The code runs on the 'OnCurrent' property of
the Form where 'secureform' is a check box field that is checked off by the
data entry person once they are done entering data using this form. Since it
is on the 'OnCurrent' property of the form, this code is running FIRST, and
then the 'Sub CommandNewPatient_Click()' (above) code is running and it gives
the error: 'You can't use the GoToRecord Action or method on an object in
Design view'. Here is the code:

Private Sub Form_Current()
Dim blnLock As Boolean
If Me.NewRecord = False Then
blnLock = (Me.secureform.Value <> True)
Me.secureform.Enabled = blnLock
Me.FrameIncl1.Enabled = blnLock
Me.FrameIncl2.Enabled = blnLock
Me.FrameIncl3.Enabled = blnLock
Me.FrameIncl4.Enabled = blnLock
Me.FrameIncl5.Enabled = blnLock
Me.FrameIncl6.Enabled = blnLock
Me.FrameIncl7.Enabled = blnLock
Me.FrameIncl8.Enabled = blnLock
Me.FrameIncl9.Enabled = blnLock
Me.FrameIncl10.Enabled = blnLock
Me.FrameIncl11.Enabled = blnLock
Me.FrameIncl12.Enabled = blnLock
Me.FrameIncl13.Enabled = blnLock
Me.FrameIncl14.Enabled = blnLock
End If
End Sub


Since I am entering this form via a Command button on the database's startup
form, can I not evaluate NewRecord status in the 'OnCurrent' event of the
form ("fScrEligCriteria") that is being opened by the Command Button on the
database startup form?

Thanks.

 

[Arvin Meyer]

Try something like this in a standard module:

Public Sub LockControls(frm As Form)
On Error Resume Next
Dim ctl As Control

For Each ctl In frm.Controls
With ctl

Select Case .ControlType
Case acTextBox
If ctl.Tag = 2 Then
ctl.Locked= False
Else
ctl.Locked=True
End If

Case acComboBox
ctl.Locked = True

Case acListBox
ctl.Locked = True

Case acCheckBox
ctl.Locked = True

Case acToggleButton
ctl.Locked = True

Case acCommandButton
If ctl.Tag = 2 Then
ctl.Enabled = False
Else
ctl.Enalbled=True
End If

Case acSubform
ctl.Locked = True

Case acOptionGroup
ctl.Locked = True

Case acOptionButton
ctl.Locked = True

End Select
End With
Next ctl
Set ctl = Nothing
Set frm = Nothing

End Sub

Then in your form's current event:

Sub Form_Current()
If Me.New Record = False Then
LockControls Me
End If
End Sub

Leave all the controls enabled and unlocked and let the code lock them all
ecept those you want to allow. Any textbox or button that you want to still
allow access to, just put the number 2 in the Tag property. Alter the code
to include other controls you may want to variably lock and unlock.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access

 

[Pat Dools]

Hi Arvin,

First, thank you for your response-- your code makes a lot of sense. A
couple of questions, if I want a specific control (combobox in this example),
do I leave the line, 'Case acComboBox'
ctl.Locked = True'
in there? How do I name the specific combobox control that I want 'unlocked'?

Second, most of the posts I've read on this issue (yours included) calls
this type of code from the Form's 'OnCurrent' property via Event Procedure.
However, I already have this code in the Form's 'OnCurrent' property:

Private Sub Form_Current()
If Me.NewRecord Then
Call SetAutoValues(Me)
End If
End Sub

Sub SetAutoValues(frm As Form)
On Error GoTo SetAutoValues_err

' Set Automatic Values in each form in series
' Add as many fields as necessary (make sure each field has the same
name on EVERY form)
With frm
!studyday = Forms!fScrEligCriteria!studyday
!id = Forms!fScrEligCriteria!id
!ptin = Forms!fScrEligCriteria!ptin
!site = Forms!fScrEligCriteria!site
End With

SetAutoValues_err:
'MsgBox Err.Description
Resume Next

End Sub

The code simply ensures a smooth auto-fill of four header field as the ddata
entry person moves from form to form in a series. Is there any way to keep
this auto-fill code intact and still use yours to lock down data? I
definitely agree that the 'OnCurrent' property is the place to make this
work, but I'm using it for the auto-fill.

Thank you.

 

[Arvin Meyer]

Hi Arvin,

First, thank you for your response-- your code makes a lot of sense. A
couple of questions, if I want a specific control (combobox in this example),
do I leave the line, 'Case acComboBox'
ctl.Locked = True'
in there? How do I name the specific combobox control that I want

'unlocked'?

You don't need to name it. What you'd do is change th code for a combobox
like this:

Case acComboBox
If ctl.Tag = 2 Then
ctl.Locked= False
Else
ctl.Locked=True
End If

Then use the Tag property of the combo box to tell the code that this
particular one doesn't get locked, so in this Case you'd use 2 to leave it
unlocked.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access Downloads
http://www.datastrat.com
http://www.mvps.org/access

 

[Pat Dools]

Thank you Arvin, I hadn't ever used the 'Tag' property for, and I understand
it much better now.