Hide a field in Subform (Datasheet) form based on Form Value

[gstreichan]

Dears,

I have a database with 2 roles: Requestor & Approver.
The role is always stored in form textbox after user logs in. This form has a subform (datasheet) with field A, B, C, D and F.
What I want to do (if possible) is to make fields D and F not visible if Form.Role = Requestor (Current Event?).
How can I get it done? I have been trying different ways in Current Event of main form, through the subform but I am still not able to.
Hope you can help me.

 

[arnelgp]

you can't hide the Column of the datasheet that way, since it will affect all records.
what you can do is Remove the Alternating hightlight and add Conditional Format to D and F
with the Expression, [Role]="Requestor", then change the Forecolor to White.

then you can add code to the Current event to Disable/Enable D and F.

 

[CJ_London]

you can use code to change the visible or columnwidth property of the relevant control.

Not much info on your setup but assuming you select the requestor in a combo then in the combo after update event the code would be something like

mysubform.form.D.visible=Me.Role <> "Requestor"
mysubform.form.F.visible=Me.Role <> "Requestor"

or

mysubform.form.D.columnwidth=Me.Role <> "Requestor"
mysubform.form.F.columnwidth=Me.Role <> "Requestor"

if you use the column width, there is nothing to stop the user right clicking on the subform header and choosing unhide

 

[MsAccessNL]

Alternatively, you could make two datasheet forms. You can load it in the subform control with SubformcontrolName.SourceObject = ".."

 

[MajP]

Datasheets have a hidden property
.columnHidden

me.Controls("F").columnHidden = (me.role = "Requestor")
me.controls("D").columnHidden = (me.role = "Requestor")

if me.role = requestor then columnHidden = true for controls d,f

 

[Pat Hartman]

If the value is in the subform table, you can't hide the column because the value could be different on different rows and hiding the column obviously hides it for all rows. If the value is always the same for all rows, then the field is in the wrong table and you need to get some help fixing your schema design.