Question related to controlling the Fields in a SubForm displayed in DataSheet Mode.

[MSAccessRookie]

I have a Form to Display Projects that has a SubForm displaying Sub-Projects related to them. The main Project Form has a Combo Box which allows a user to select a ProjectID to display. The SubForm has a ProjectID Field as well. When the Project ID is selected in the Main Form, only the appropriate Sub-Projects are displayed in the Sub-Form.

On the SubForm, there is a Field named ActionID, which uses the following VB Code to limit the availability of the Fields in the SubForm that apply to Dates. If the selected Action is Install, only Install related Dates should be available. Likewise, if the selected Action is Remove only Remove related Dates should be available.

    If Me.ActionID.Column(1) = "Install" Then
        Me.AssetInstallMonth.Enabled = True
        Me.AssetInstallYear.Enabled = True
        Me.AssetRemovalYear.Enabled = False
        Me.AssetInstallMonth.Locked = False
        Me.AssetInstallYear.Locked = False
        Me.AssetRemovalYear.Locked = True
    Else
        Me.AssetInstallMonth.Enabled = False
        Me.AssetInstallYear.Enabled = False
        Me.AssetRemovalYear.Enabled = True
        Me.AssetInstallMonth.Locked = True
        Me.AssetInstallYear.Locked = True
        Me.AssetRemovalYear.Locked = False
    End If

Everything seems to work as expected until there is more than one Sub-Project is the SubForm and they do not contain the same Action. In these cases, it appears as if the Sub-Projects in the SubForm are not being treated as individual records. If Sub-Project is Added or Modified to have an action of "Install", all records are treated as Install Records (only Install related Dates are available), regardless of their content.

Is there a way to update the properties for only the row that has been selected?

-- Rookie

 

[pr2-eugin]

Is the SubForm set to Continuous form? If yes, I guess the problem depends on where the CODE is placed to be executed Form Current would solve the immediate problem, althought I am not 100% sure. On top of that, I think continuous forms always have this problem, the only solution to this would be the use of Conditional formatting.

 

[MSAccessRookie]

Is the SubForm set to Continuous form? If yes, I guess the problem depends on where the CODE is placed to be executed Form Current would solve the immediate problem, althought I am not 100% sure. On top of that, I think continuous forms always have this problem, the only solution to this would be the use of Conditional formatting.

Thanks for replying.

• The SubForm is set to DataSheet View, not any type of Form View.
• The Code is executed in the OnChange Event of the ActionID Field.

I am beginning to think that Datasheet View has issues that are similar to Continuous Forms View, and will look into whether Conditional Formatting is an option.

-- Rookie

 

[MSAccessRookie]

Update:

Changing the activation of the VB Code from the OnChange Event of the ActionID Field to the OnCurrent Event of the Form looks like it will resolve the issue. Thanks for your response.

-- Rookie

 

[pr2-eugin]

Rookie, Current will work only when the 'Current' Record has the focus right? Or does having the code in Current work for all the records as desired?

 

[MSAccessRookie]

Rookie, Current will work only when the 'Current' Record has the focus right? Or does having the code in Current work for all the records as desired?

For the purpose of the original issue, it worked on each record independently. There has been a new issue today where I am not able to relate combo boxes properly, and all similar boxes use the rules for the one currently in focus.

I can only go back to the fact that that Datasheet View has issues that are similar to Continuous Forms View.