Access 2010: Column with multiple values

[jonathanchye]

In my table I have a column called "Departments" which is set in Access 2010 to be a multiple value field.

This works brilliantly on user input level as a smart drop down box which contains check boxes which allows users to select one or more values.

However, the problem comes when I need to check the values selected. Normally I would use Dlookup and assign this value to a variable. However, how should I do it in this case as the column might contain more than 1 values?

In one of my forms, I need to do this action in the On Load event :

DLookup("Departments", "tblManagers")

I need to use the result(s) from this DLookup to set the visibility of some controls in my forms. Can I use something like this in my form's On Load event?

Me.tab1.Visible = False
Me.tab2.Visible = False
Me.tab3.Visible = False

Dim temp As Variable

temp = DLookup("Departments", "tblManagers")

If temp = "Finance" Then
Me.tab1.Visible = True
End If

If temp = "Maintanence" Then
Me.tab2.Visible = True
End If

If temp = "Management" Then
Me.tab3.Visible = True
End If

Basically I want to only make relevant tabs visible. So if a person has Departments assigned as Finance and Maintanent both will be visible but not Manangement etc.

 

[jonathanchye]

Hmm, I've managed to run a simple query and filer the results so it looks something like this now :

Name---------------------------Deparment
John Doe-----------------------Finance
John Doe-----------------------Manangement

The tabs in my tab controls are all names the same, ie "Finance", "Management"...etc.

Is there a way to scan through this query and enable the relevant tabs? Using DLookup just scans the first value.

 

[jonathanchye]

Right, found a very helpful ELookup module which looks like what I want here : http://allenbrowne.com/ser-42.html

However, I don't really understand how to use it. How would I store the values returned by this ELookup?

 

[jonathanchye]

Managed to find a workaround but took plenty of code Oh well.

 

[Galaxiom]

As you have discovered, multivalue fields appear superficially attractive but are actually lame ducks and best avoided entirely.

Later when you want to upsize the backend to a database server you will have no choice but to completely replace them with related tables.

The time you spent on the kludge to work with them would have been better spent on reworking to a more conventional structure. Oh well. Next time.

 

[jonathanchye]

Yeah, unfortunately that's what the client wants and they want it in a hurry. To be honest I've only discovered about this when I upgraded to Office2010.

If I am not mistaken though, if I upgrade to MS SQL Server in the future the feature should still be supported?

 

[Galaxiom]

If I am not mistaken though, if I upgrade to MS SQL Server in the future the feature should still be supported?

Afraid not.

http://office.microsoft.com/en-us/access-help/guide-to-multivalued-fields-HA001233722.aspx

"A Office Access 2007 multivalued field is upsized to SQL Server as a memo (ntext) field that contains the delimited set of values. Because SQL Server does not support a multivalued data type that models a many-to-many relationship, additional design and conversion work might be needed."