enable/disable controls in datasheet

  • Thread starter Thread starter GDW
  • Start date Start date
G

GDW

I'm trying to enable/disable controls on a datasheet based on the value
of another field/control on each record. The problem that I'm having
is not being able to access the controls specific to each record. What
happens is that the controls end up being either all enabled or all
disabled. There are 2 parts to what I'm trying to do: 1) setting the
initial state of the controls (DRFID in the code below) when the form
first opens or is switched to datasheet view and 2) changing the state
of DRFID when the DRFCompleted checkbox is clicked. BTW, this form is
a subform (I don't know if that matters).

Any help would be appreciated - Gary

Here's the code:

Private Sub DRFCompleted_Click()
Me.DRFID.Enabled = Me.DRFCompleted
End Sub

Private Sub Form_Current()
On Error GoTo Exit_Form_Current

If Me.ActiveControl.ControlName = "DRFID" And Not Me.DRFCompleted
Then
Me.DRFCompleted.SetFocus
End If
Me.DRFID.Enabled = Me.DRFCompleted

Exit_Form_Current:
Exit Sub
End Sub
 
Bad news. It ain't gonna work. Regardless of whether it is a form or a
subform, if you are using Datasheet, or Continuous form, there is no way for
Access to know what row to affect when addressing controls.
 
Like Klatuu says, you can't enable a certain control for some records and
not for others, but you can sort of simulate that effect. When a user clicks
on a row in a datasheet or on a field in a continuous form, the form's
Current event fires. So you can change the enable/disable status of your
controls in the current event of the form based on the value of the
checkbox. The drawback is if the control is disabled, the user won't be able
to click on that control in a record where it should be enabled. If you just
lock the control rather than disable it, then it will work a bit better.
hope this helps
-John
 
Actually, I think he can do this if he uses Conditional Formatting
(available in XP/2002, not available in 97, not sure about 2000). In the few
times I've used it, I've found Conditional Formatting to be "record
specific smart" whether viewing as Single Form, Continuous Forms or
Datasheet.

Select the control. Format>ConditionalFormatting. Set up your rules and
format (disabled).

Remember to delete (or comment out) your enable-related VBA code before
testing, or it won't be a fair test. :-)

HTH,
 
Clever idea, John

John Welch said:
Like Klatuu says, you can't enable a certain control for some records and
not for others, but you can sort of simulate that effect. When a user clicks
on a row in a datasheet or on a field in a continuous form, the form's
Current event fires. So you can change the enable/disable status of your
controls in the current event of the form based on the value of the
checkbox. The drawback is if the control is disabled, the user won't be able
to click on that control in a record where it should be enabled. If you just
lock the control rather than disable it, then it will work a bit better.
hope this helps
-John
Click to expand...
 
Works like a charm. I set up 2 formatting conditions:

Not DRFCompleted - disabled
DRFCompleted - enabled

Thanks everyone.

Gary
 
I certainly have a lot of continues forms where I set the controls 'enabled'
property on/off. While this actually makes all instances of the control go
disabled, I actually find this works quite well. I mean, when you move
to the next row, those columns that you enable, or disable can then
be set. So, you could set the controls visible property as you move
the cursor up/down through the

In fact, I actually PREFER the above behavior, as then during
data entry it is VERY easy to see that the column in question
is enabled.

In place of a VERY HARD TO READ checkerboard pattern of enabled, and
disabled boxes,
, you get a very nice enable/display view as I move the cursor up /down.

I have uploaded a gif animation of me navigating in a form, both of the two
screen shots will give you an idea of how this looks.

http://www.members.shaw.ca/AlbertKallal/HideColumn/index.htm
 
I have the exact same situation as GDW trying to enable/disable controls on a datasheet based on the value of another field/control on each record. I have tried George proposal to use Conditional Formatting and it do work. But not if the control is a checkbox. It looks as if Conditional Formatting is not available for checkbox controls. :confused:

Anyone know of a workaround? (I'm using Access 2010)


George Nicholson said:
Actually, I think he can do this if he uses Conditional Formatting
(available in XP/2002, not available in 97, not sure about 2000). In the few
times I've used it, I've found Conditional Formatting to be "record
specific smart" whether viewing as Single Form, Continuous Forms or
Datasheet.

Select the control. Format>ConditionalFormatting. Set up your rules and
format (disabled).

Remember to delete (or comment out) your enable-related VBA code before
testing, or it won't be a fair test. :-)

HTH,
--
George Nicholson

Remove 'Junk' from return address.


> "GDW" wrote:
>
>> I'm trying to enable/disable controls on a datasheet based on the value
>> of another field/control on each record. The problem that I'm having
>> is not being able to access the controls specific to each record. What
>> happens is that the controls end up being either all enabled or all
>> disabled. There are 2 parts to what I'm trying to do: 1) setting the
>> initial state of the controls (DRFID in the code below) when the form
>> first opens or is switched to datasheet view and 2) changing the state
>> of DRFID when the DRFCompleted checkbox is clicked. BTW, this form is
>> a subform (I don't know if that matters).
>>
>> Any help would be appreciated - Gary
Click to expand...
 
Back
Top