Hiding Columns on Subform Based on Multi-Value Lookup Field (1 Viewer)

cavemanderson

New member
Local time
Today, 11:08
Joined
Aug 7, 2013
Messages
2
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
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 14:08
Joined
Feb 19, 2002
Messages
42,971
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?
 

cavemanderson

New member
Local time
Today, 11:08
Joined
Aug 7, 2013
Messages
2
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? :banghead:
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 14:08
Joined
Feb 19, 2002
Messages
42,971
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.
 

Users who are viewing this thread

Top Bottom