How do I hide columns in a sub form on a tab in my main form?

I have a subform that is simply displaying a table that is linked to the current record on the main form.  The subform is showing certain fields which I would like to hide on the subform view but still be visible on the table.  I tried the following code:
Private Sub Form_Load()
Me.[AuditTrailID].ColumnHidden = True
End Sub

Open in new window


but I get the following message when Access gets to the sub form load:
"Run-time error 2465:
DatabaseName can't find the field '|1' referred to in your expression"

The table is a linked table in a backend access database, the form/subform are in the front end.
Muhammed UmairAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rey Obrero (Capricorn1)Commented:
try this syntax

Me.Controls("NameOfControl").ColumnHidden = true

if the name of the control (textbox) is txtAuditTrailID

Me.Controls("txtAuditTrailID").ColumnHidden = true
0
Rey Obrero (Capricorn1)Commented:
or if you want to call the codes in the Load Event of the Main form

Me.MySubformControlName.Form.Controls("NameOfControl").ColumnHidden = True
0
Muhammed UmairAuthor Commented:
if the name of the control (textbox) is txtAuditTrailID

Me.Controls("txtAuditTrailID").ColumnHidden = true

Hmm, I don't think there's a textbox that I can refer to, its a subform labeled tblAuditTrail and there is a column displaying within that subform that I'd like hidden to the user
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Rey Obrero (Capricorn1)Commented:
close your main form,
open your subform in design view
select the control that shows the data you want to hide
hit F4
in the property window, select the Other tab and look at the information in the Name property

use that name in the code

Me.Controls("theNAMEgoeshere").ColumnHidden = true
0
Muhammed UmairAuthor Commented:
I opened my subform in design view and hit f4 and see tblAuditTrail as the Name under the Other tab. See attached screenshot for reference.

I then put in the following code:
Private Sub Form_Load()
Me.Controls("tblAuditTrail").ColumnHidden = True
End Sub

Open in new window


however, I'm getting the following error:
"Object doesn't support this property or method"

Sorry, I'm a little new to access vba programming

property sheet
0
Rey Obrero (Capricorn1)Commented:
can you post a screen shot of your subform "tblAuditTrail" in design view.

better if you can upload a copy of your db.
0
Muhammed UmairAuthor Commented:
db upload is difficult to do.  Here is a screenshot:
subform screenshot
0
Rey Obrero (Capricorn1)Commented:
ok, that is not the design view of the SUBFORM, that is the design view of the MAIN form  "Device Input Form"

close that view

look for the form named "tblAuditRail" and open it in design view
0
Gustav BrockCIOCommented:
Don't touch the subform. Run the command in the OnLoad event of the main form.
Here is how:
Private Sub Form_Load()

    Me!tblAuditRail.Form!AuditTrailID.ColumnHidden = True

End Sub

Open in new window

/gustav
0
Muhammed UmairAuthor Commented:
Gustav,

That code is almost bringing me there! The issue is that code also hides the column in the table as well as the subform.  I would like all columns to be viewable in the table for DB admins on the back end but certain columns hidden in the subform for users on the front end.

Do you know of any code that would hide only the columns in the subform but not the actual table itself?
0
Muhammed UmairAuthor Commented:
Here is the design view of tblAuditTrail, just to confirm it is a table not a form.  see screenshot:
design view of tblAuditTrail
0
Rey Obrero (Capricorn1)Commented:
@Muhammed Umair

instead of inserting your table to the form,
create a Data Sheet form with tblAuditTrail as the Record Source
- name the form  frmAuditTrail
then place the form " frmAuditTrail" as a subform

use this code to hide the "AuditTrailID" column in the Load event of the subform "frmAuditTrail"

private sub form_load()

Me.Controls("AuditTrailID").ColumnHidden = true

end sub
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Gustav BrockCIOCommented:
> that code also hides the column in the table as well as the subform

That's because you use the table directly as a subform. Don't ever do that; create a subform in data sheet mode and use that as the subform.

/gustav
0
Muhammed UmairAuthor Commented:
I created the datasheet frmAudit Trail, see screenshot:

frmAuditTrail
I then added it as a subform, see screenshots:
subform frmAuditTrailauditrailID
I then inserted the code:
private sub form_load()

Me.Controls("AuditTrailID").ColumnHidden = true

end sub

however i get the following error:
"Database cant find the field 'AuditTrailID' referred to in your expression"

not sure what I'm doing wrong
0
Muhammed UmairAuthor Commented:
@gustav

That did it, thank you!
0
Rey Obrero (Capricorn1)Commented:
where did you place the codes?
0
Gustav BrockCIOCommented:
Use the code I provided. Now it would be:
Private Sub Form_Load()

    Me!frmAuditTrail.Form!AuditTrailID.ColumnHidden = True

End Sub

Open in new window

/gustav
0
Gustav BrockCIOCommented:
OK. Great!

/gustav
0
Rey Obrero (Capricorn1)Commented:
@Muhammed Umair,

if you will notice, i was the one who gave you the instruction to create and use a form and not the table..

see my post at http:#a40972662 

it was just repeated by the other expert..
0
Muhammed UmairAuthor Commented:
Thank you for all your help.
0
Gustav BrockCIOCommented:
You are welcome!

/gustav
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.