Go Back   Access World Forums > Microsoft Access Discussion > Forms

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 08-07-2013, 11:25 AM   #1
cavemanderson
Newly Registered User
 
Join Date: Aug 2013
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
cavemanderson is on a distinguished road
Cool Hiding Columns on Subform Based on Multi-Value Lookup Field

Hello,

This is my first post

I have been searching the internet for the past week and can't find the answer to this problem. I want to use VBA to hide columns in a subform based on what is checked in a multi value look up field.

I am creating this DB for use with sharepoint as a web database, which is why I am using the lookup field to begin with. There will be a client DB to use with some VBA code

So what I have is a lookup field with tests "Test 1, Test 2, etc" on form sample.

There is a subform called results, and I want to hide certain columns based on what tests are performed.

I tried using an If Then statement (code is being run on subform load)

If Me.Parent.fieldTest = "Test 1" Then
Me.Test1Col.ColumnHidden = False
Else Me.Test1Col.ColumnHidden = True

That is basically the code I was trying to use. I am getting an error 13, which I assume is because fieldTest can not = something since it is a multi value look up field.

Any suggestions?

Thanks in advance

cavemanderson is offline   Reply With Quote
Old 08-07-2013, 06:59 PM   #2
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 28,238
Thanks: 15
Thanked 1,591 Times in 1,511 Posts
Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all
Re: Hiding Columns on Subform Based on Multi-Value Lookup Field

I can't tell you what the syntax would be since I would never use a multi-value field but how do you expect this to work if the fieldTest could have multiple values? Which would rule?
__________________
Bridge Players Still Know All the Tricks
Pat Hartman is offline   Reply With Quote
Old 08-08-2013, 10:21 AM   #3
cavemanderson
Newly Registered User
 
Join Date: Aug 2013
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
cavemanderson is on a distinguished road
Thumbs up Re: Hiding Columns on Subform Based on Multi-Value Lookup Field

Quote:
Originally Posted by Pat Hartman View Post
I can't tell you what the syntax would be since I would never use a multi-value field but how do you expect this to work if the fieldTest could have multiple values? Which would rule?
Well, since there multiple tests which would have multiple results, I don't know that one would need to rule.

If you have Test 1, Test 2, and Test 3 selected, I would like it to show the columns for the results of Test 1, Test 2, and Test 3. Know what I'm saying?

cavemanderson is offline   Reply With Quote
Old 08-08-2013, 05:59 PM   #4
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 28,238
Thanks: 15
Thanked 1,591 Times in 1,511 Posts
Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all
Re: Hiding Columns on Subform Based on Multi-Value Lookup Field

Your table is set up the way a spreadsheet would be set up. That's why you are having this issue. If you normalize the table, you won't have any need to hide/show columns AND you'll be able to add tests without changing the structure of your table which is not the case now. You need a table that defines what tests are required. And another table to store the results. The structure is basically -
ResultID (autonumber primary key)
ItemID (foreign key to what you are testing)
TestID (foreign key to the test you are doing)
TestResult
TestDate
etc.

__________________
Bridge Players Still Know All the Tricks
Pat Hartman is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Multi Column Lookup Wizard Display 3 columns change which one is shown on table DBnub11 Tables 6 02-11-2011 01:05 PM
Hiding Columns realtime in Subform Spartacus Forms 2 09-25-2005 11:28 AM
Hiding Columns in Subform Spartacus Forms 1 08-02-2005 08:52 AM
Hiding columns in a subform datasheet Beacher Forms 5 06-04-2005 01:33 PM
***//Hiding columns in a subform jhsurti Forms 2 05-25-2003 08:45 PM




All times are GMT -8. The time now is 06:50 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World