Multiple Select List Box in Sheetview? 2

[1DMF]

Hi,

Is it possible to have a multiple select lisbox in data sheet view?

A bit like the way a web form would let you select multiple options with CTRL ?

I've set the mutiple-select to simple and extended, but only seem to be able to choose one option?

I was hoping the bound field would end up with some form of separated data with each option such as comma or semi-colon?

Is this possible?

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Google Rank Extractor -> Perl beta with FusionCharts

 

[MajP]

This functionality as described is available in A2007 using multi-value fields. What version do you have?

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

 

[1DMF]

2003! :-(

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Google Rank Extractor -> Perl beta with FusionCharts

 

[1DMF]

yup looks like it can't be done in 2003, you can't even select multiple when in data sheetview only form view.

So no lookup table to validate entries then, they will just have to type the codes manually and comma separate them themselves.

At least if we finally upgrade to a newer version of office / access , using the new multi-value field option will be easy to implement.

Cheers MajP

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Google Rank Extractor -> Perl beta with FusionCharts

 

[MajP]

Can you explain a little more what you are trying to do and why you would want to put in comma seperated values. If you do this is 2003 you will violate normalacy, but that may be OK for what you are doing. In 2007 although those values appear seperated by commas there is more going on behind the scenes. They are actually stored in a normal structure. In other words you may see
cat, dog, mouse
cat, bird, rabbit
rabbit, dog, mouse

however in 2007 you can actually query the multivalued field and return all record with cat
select * from tbl where animals = 'cat'


I think you could possibly "fake" this functionality in a continous form view, by pulling down a combo an adding a value to the string if it does not exist in the string already.
If you normalize your data you could have a contious form or form in datasheet view that displays the data as described. If you want to add or edit then double click the field and pop up a small multiselect listbox. make/update your choices close out and the data would appear in the text box comma seperated. That is the more correct approach, but requires some amount of coding.

 

[1DMF]

If you do this is 2003 you will violate normalacy,

? what does that mean.

If you want to add or edit then double click the field and pop up a small multiselect listbox. make/update your choices close out and the data would appear in the text box comma seperated. That is the more correct approach, but requires some amount of coding.

That's exactly the road I'm taking.

Basically they wanted an extra field in the table that would hold clasification codes, via some multiple select method, and displayed via the web application as comma separated.

So I've devised a double click method which pops up a modal form with two listboxes, one with current selection and one they can double click to add classifications and also double click to remove.

When the form closes I'll process the listbox content and simply generate a comma separated string in a standard nvarchar field.

Job done! , but as you say a lot of coding for such a simple feature!

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Google Rank Extractor -> Perl beta with FusionCharts

 

[PHV]

you will violate normalacy
? what does that mean.
Have a look here:

http://www.r937.com/Relational.html

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[1DMF]

cheers PHV, but the first line is certainly correct

Database design theory is a topic that many people avoid learning for lack of time.

plus lack of funding for the training in the first place!

I'm still unsure why it would be wrong to put a comma separated string into a nvarchar field?

should I be using an ntext field instead? or is it simply relative to the size of data storage required?

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Google Rank Extractor -> Perl beta with FusionCharts

 

[PHV]

why it would be wrong to put a comma separated string into a nvarchar field
It violates 1NF (atomicity).

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[1DMF]

But it's just a string?

So to have a string that contains commas you should use a memo/ntext field?

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Google Rank Extractor -> Perl beta with FusionCharts

 

[PHV]

But it's just a string
Just a string or a list of values ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[1DMF]

Define String and Define List?

Is a sentence a string or a space separated list of words?

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Google Rank Extractor -> Perl beta with FusionCharts

 

[MajP]

Here are some examples. Lets say your list is names of people on a team. Well eventually you probably want to be able to do things like

find how many times Mary is on a team
Ensure that Mary is not on two team in group b
Show the each person and which teams they are on
etc.

All of these basic data manipulations are very easy to do with a normal structure. This is not theory it is just a simple fact. With the non normal string data, all of those basic queries require complex vba code to attempt to answer.

If you want to expand functionality such as relate people to other information that becomes nearly impossible. It may serve your 1 single exact purpose of showing a list of names. If you want to do anything with that information, your limited.

 

[MajP]

To normalize this would be very simple and could still be shown in a single text box using a calculated field that concatenates the normal values into a comma seperated string.

 

[1DMF]

I understand the issue if this was a complex DB with peoples names and the desire to run complex SQL joins and searches etc.

But this is just a simple text field they want to put classifications in.

i.e.

A2,G3,R5

They told me there will never be any desire to report against it nor run complex analysis, they just need to store these codes against each case record, to satisfy new FSA regulatory requirements.

But we all know that managements famous last words are "we only want to do this" and "we won't want to do that"

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Google Rank Extractor -> Perl beta with FusionCharts