Using VB...how do I control one record at a time...how to set up a Public Sub or Function. 1

[puforee]

I have a control on a form with a DropDown object. When a certain value is selected I want to disable several other objects within the RECORD.
Here is my code so far. Of course this changes the objects for all records. I want to limit it to only one record...each record may have a different selection.

Also, this is going to be used on several Forms with the same objects...trust me...I have to do it this way...how can I make this global so it can be called from any of my forms.

Private Sub TngProdDD_AfterUpdate()
If Me.TngProdDD = "Analysis/Research" Then
Me.TypeRevDD.Enabled = False
Me.ATA_Chapter.Enabled = False
Me.Sub_Chapter.Enabled = False
Me.Pageset___Media_Identifier.Enabled = False
Me.Team_Review_of_Work_Complete.Enabled = False
Me.LeadAppDD.Enabled = False
Me.Date_Workflow_Approved.Enabled = False
Me.Additional_Comments.Enabled = False
Else
Me.TypeRevDD.Enabled = True
Me.ATA_Chapter.Enabled = True
Me.Sub_Chapter.Enabled = True
Me.Pageset___Media_Identifier.Enabled = True
Me.Team_Review_of_Work_Complete.Enabled = True
Me.LeadAppDD.Enabled = True
Me.Date_Workflow_Approved.Enabled = True
Me.Additional_Comments.Enabled = True
End If
End Sub

Thanks......

 

[MajP]

In the forms current event. This assumes all forms will have the same controls

disableEnableControls(me)

in a standard module

Public sub DisableEnableControls(frm as access.form)
  frm.TypeRevDD.Enabled = frm.TngProdDD = "Analysis/Research"
  frm.ATA_Chapter.Enabled = frm.TngProdDD = "Analysis/Research"
  frm.Sub_Chapter.Enabled = frm.TngProdDD = "Analysis/Research"
  frm.Pageset___Media_Identifier.Enabled = frm.TngProdDD = "Analysis/Research"
  frm.Team_Review_of_Work_Complete.Enabled = frm.TngProdDD = "Analysis/Research"
  frm.LeadAppDD.Enabled = frm.TngProdDD = "Analysis/Research"
  frm.Date_Workflow_Approved.Enabled = frm.TngProdDD = "Analysis/Research"
  frm.Additional_Comments.Enabled = frm.TngProdDD = "Analysis/Research"
end sub

 

[Andrzejek]

Or you can use a Tag property of the controls you want to control (pun intended )
So MayP's code would be something like:

disableEnableControls(me, True) 
or 
disableEnableControls(me, False)

and something like:

Public sub DisableEnableControls(frm as access.form, bln As Boolean)
Dim cntr As Access.Control *

For Each cntr in frm.Controls
    If cntr.Tag = "Whatever" Then
        cntr.Enabled = bln
    End If
Next cntr

End Sub

* I am not sure if that's correct Dim statement


Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[puforee]

So...MajP Actually there are a couple of different Filter controls depending on how the form is shown...Add mode or Edit mode.

So, the code in the standard module will set the values I want for that specific record. The selection for the next record may be different. The If Me.TngProdDD = "Analysis/Research" Then is a drop down list that has six selections. Only if the the selection is Analysis/Research do I want the selected controls...for that record only...disabled. For the other 5 selections I want all of them enabled. These controls are just a subset of more controls I have on the form.

thanks,

 

[MajP]

The selection for the next record may be different. The If Me.TngProdDD = "Analysis/Research" Then is a drop down list that has six selections. Only if the the selection is Analysis/Research do I want the selected controls...for that record only...disabled. For the other 5 selections I want all of them enabled.

That is why the calling code goes in the current event. It enables and disables for the current record.

 

[puforee]

Please bear with me here. I am getting a little confused.

When I make a selection from the TngProdDD I need to analyze the selection. So, on the After Update event for TngProdDD control I placed the above code. You are talking about Current event. The combobox does not have a "Current" event.

This form is actually a sub form. It has 14 controls on it. I need to be able to set 8 of these controls to Enabled or Disabled based on the selection from TngProdDD. I want this to occur on only the record I am currently working on. Right now, the code at the beginning of this message stream sets the 8 controls to enabled or disabled for All Records...I only want it to work on one record...

Hopefully this will help clear this up. I thought maybe the current record cmd could be used...somehow.

Thanks,

Sorry to be so dense.

 

[MajP]

The better solution would then be to use conditional formatting. Make all the controls disabled. Then select all the controls. Then click conditional formatting with all controls selected. Then in conditional formatting choose

Expression is: [TngProdDD] = "Analysis/Research"

Click in the right corner the Enable/disable format, and choose enable.

 

[MajP]

By "all the controls" I am referring to all the controls you want to enable/disable.

 

[puforee]

Oh come on now....that easy?..sometimes I can't see the forest for the trees. I can't believe how easy this was. You definitely get a Star For this one. I guess I will have to broaden my horizons and not just think about code.

Fantastic thanks,

 

[Andrzejek]

MayP,
puforee is happy with your solution, and that’s nice. But wouldn’t my approach (above) work OK as well? Just courious...

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[MajP]

But wouldn’t my approach (above) work OK as well?

Yes, if it was a single form view. The OP appears to have continuous form. I think you would have to play with some formatting to get the effect they want. In access when you disable a control it has a tendency to automatically grey out (I believe you can play with the formatting to avoid this). So if you called your code in both the current event (for existing records) and in the afterupdate of the combobox (for new records) then it would disable and enable the correct controls in the current record. However, since it is a continuous form you would see the enabled/disabled fields associated to the current record applied to all records. If instead you locked the fields (no formatting change), you would not be able to tell the difference and it would seem as only the fields for the current record are locked/unlocked.
cntr.locked = bln
But locking may not be as obvious to the user, since they would be able to put the cursor in the control but not edit it.