Display Multiselect on list box

[Lysander]

Hi,
I am using Access 2003
I have seen some threads on how to select multiple values from a list box, but how do I display the selected options back again (Last time I used Access in anger, you could not multi-select at all)

This is what I have. A demographic survey form, with over 100 questions, most of which can have multiple answers. At the moment, each answer is stored in the form as a seperate y/n field. Apart from the in-elgance of this solution, Access tells me I have two many fields.

What I would like to do is have a lookup table for each question, with two columns, first column is A,B,C,D etc, second column is the potential answer.
eg A Glass, B Knife, C Scissors etc

Displaying this on the form as a list box, I can pick up the options selected and store in the table the combination, say ADEG

What I cant work out how to do is to display the list box on the form, highlighting the previous selected values from my table field values of "ADEG"

Can this be done in Access 2003 with a bound field, or easily in code?

[ADezii]

Hi,
I am using Access 2003
I have seen some threads on how to select multiple values from a list box, but how do I display the selected options back again (Last time I used Access in anger, you could not multi-select at all)

This is what I have. A demographic survey form, with over 100 questions, most of which can have multiple answers. At the moment, each answer is stored in the form as a seperate y/n field. Apart from the in-elgance of this solution, Access tells me I have two many fields.

What I would like to do is have a lookup table for each question, with two columns, first column is A,B,C,D etc, second column is the potential answer.
eg A Glass, B Knife, C Scissors etc

Displaying this on the form as a list box, I can pick up the options selected and store in the table the combination, say ADEG

What I cant work out how to do is to display the list box on the form, highlighting the previous selected values from my table field values of "ADEG"

Can this be done in Access 2003 with a bound field, or easily in code?

Is your request to store multiple selections of the 1st Column Values in a 2 Column List Box as a String, then save this to the underlying Table? e.g.

Col1    Col2
A       Glass
B       Knife
C       Scissors
D       Bottle
E       Howitzer
F       Lead Pencil
 

If the User selects A, B, D, and F, store ABDF in the underlying Table?

[Lysander]

Is your request to store multiple selections of the 1st Column Values in a 2 Column List Box as a String, then save this to the underlying Table? e.g.

Col1    Col2
A       Glass
B       Knife
C       Scissors
D       Bottle
E       Howitzer
F       Lead Pencil
 

If the User selects A, B, D, and F, store ABDF in the underlying Table?

Storing ABDF in the underlying table is ok, but what I want to do is when the user looks at the record in the future, the form will pick up the ABDF from the table and highlight the respective entries in the list.

[ADezii]

Storing ABDF in the underlying table is ok, but what I want to do is when the user looks at the record in the future, the form will pick up the ABDF from the table and highlight the respective entries in the list.

This can be done, and as soon as I get a chance, I'll show yoou how.

[FishVal]

Hi,
I am using Access 2003
I have seen some threads on how to select multiple values from a list box, but how do I display the selected options back again (Last time I used Access in anger, you could not multi-select at all)

This is what I have. A demographic survey form, with over 100 questions, most of which can have multiple answers. At the moment, each answer is stored in the form as a seperate y/n field. Apart from the in-elgance of this solution, Access tells me I have two many fields.

What I would like to do is have a lookup table for each question, with two columns, first column is A,B,C,D etc, second column is the potential answer.
eg A Glass, B Knife, C Scissors etc

Displaying this on the form as a list box, I can pick up the options selected and store in the table the combination, say ADEG

What I cant work out how to do is to display the list box on the form, highlighting the previous selected values from my table field values of "ADEG"

Can this be done in Access 2003 with a bound field, or easily in code?

It seems to me that normalized db structure in your case should be something like this.

tblQuestions
.keyQuestionID (PK, Long(Autonumber))
.txtQuestion (Text)

tblAnswerVariants
.keyAnswerVariantID (PK, Long(Autonumber))
.keyQuestionID (FK,Lookup(tblQuestions))
.txtAnswerVariant (Text)

tblUsers
.keyUserID (PK,Long(Autonumber)
.txtName (Text)

tblUserAnswers
.keyUserAnswerID (PK, Long(Variant))
.keyUserID (FK, Lookup(tblUsers))
.keyAnswerVariantID (FK, Lookup(tblAnswerVariants))

[ADezii]

Hi,
I am using Access 2003
I have seen some threads on how to select multiple values from a list box, but how do I display the selected options back again (Last time I used Access in anger, you could not multi-select at all)

This is what I have. A demographic survey form, with over 100 questions, most of which can have multiple answers. At the moment, each answer is stored in the form as a seperate y/n field. Apart from the in-elgance of this solution, Access tells me I have two many fields.

What I would like to do is have a lookup table for each question, with two columns, first column is A,B,C,D etc, second column is the potential answer.
eg A Glass, B Knife, C Scissors etc

Displaying this on the form as a list box, I can pick up the options selected and store in the table the combination, say ADEG

What I cant work out how to do is to display the list box on the form, highlighting the previous selected values from my table field values of "ADEG"

Can this be done in Access 2003 with a bound field, or easily in code?

I experimented with some coding techniques, and arrived at a solution but first a couple of Assumptions:

1. The Main Form name is frmMain.
2. Tha Main Table Name is tblMain.
3. The List Box name is lstAnswers. It has 2 Columns, Bound Column = 1, and Multi Select = Extended. the 1st Column will be a single character Answer as A, B, C, ... and the 2nd Column will contain a brief Description.
4. txtAnswer - A Text Box on the Form that will receive the dynamically built Answer String for each Selection made, e.g. If the user selects A, B, D, and G this Field on the Form will appear as A ==> AB ==> ABD ==> and finally ABDG. txtAnswer is Bound to an [Answer] Field in tblMain.
5. This is all meant to be a Generic Template to help you accomplish your specific task at hand, make all necessary substitutions wherever appropriate.

1. In the AfterUpdate() Event of your List Box, place the following code. This code will concatenate (Answer only) all values selected by a User and place them in [txtAnswer] as they are selected.
   

   Private Sub lstAnswers_AfterUpdate()
   Dim varItem As Variant, frm As Form, ctl As ListBox
   Dim strBuiltAnswer As String
    
   Set frm = Forms!frmMain
   Set ctl = frm![lstAnswers]
    
   For Each varItem In ctl.ItemsSelected
     strBuiltAnswer = strBuiltAnswer & ctl.ItemData(varItem)
   Next varItem
    
   Me![txtAnswer] = strBuiltAnswer
   End Sub

2. The following code segment is to be placed in the Current() Event of the Form as indicated. It will parse the value in txtAnswer character by character, and select the appropriate entries in the List Box. This will be done for each individual Record, which is why the code is placed in this Event.
   

   Private Sub Form_Current()
   Dim S As Integer
    
   'Before anything else, you must clear any Selections in the List Box
   'Items may also be selected from the previous Record
   For S = 0 To Me![lstAnswers].ListCount - 1
     Me![lstAnswers].Selected(S) = False
   Next S
    
   If IsNull(Me![txtAnswer]) Then Exit Sub
    
   Dim strLongAnswer As String, intNoOfListItems As Integer
   Dim T As Integer
    
   intNoOfListItems = Me![lstAnswers].ListCount
   strLongAnswer = Me![txtAnswer]
    
   For S = 0 To intNoOfListItems - 1
     Me![lstAnswers].Selected(S) = False
   Next S
    
   For T = 1 To Len(strLongAnswer)
     For S = 0 To intNoOfListItems - 1
       If Me![lstAnswers].Column(0, S) = Mid$(strLongAnswer, T, 1) Then
         Me![lstAnswers].Selected(S) = True
       End If
     Next S
   Next T
   End Sub

3. This code provides the logic as to how to solve the 1 specifc problem that you mentioned. It makes no other assumptions about how your data is structured and as to whether or not it is Normalized.
4. Good luck and let me know how you make out!

[Lysander]

I experimented with some coding techniques, and arrived at a solution but first a couple of Assumptions:

1. The Main Form name is frmMain.
2. Tha Main Table Name is tblMain.
3. The List Box name is lstAnswers. It has 2 Columns, Bound Column = 1, and Multi Select = Extended. the 1st Column will be a single character Answer as A, B, C, ... and the 2nd Column will contain a brief Description.
4. txtAnswer - A Text Box on the Form that will receive the dynamically built Answer String for each Selection made, e.g. If the user selects A, B, D, and G this Field on the Form will appear as A ==> AB ==> ABD ==> and finally ABDG. txtAnswer is Bound to an [Answer] Field in tblMain.
5. This is all meant to be a Generic Template to help you accomplish your specific task at hand, make all necessary substitutions wherever appropriate.

1. In the AfterUpdate() Event of your List Box, place the following code. This code will concatenate (Answer only) all values selected by a User and place them in [txtAnswer] as they are selected.
   

   Private Sub lstAnswers_AfterUpdate()
   Dim varItem As Variant, frm As Form, ctl As ListBox
   Dim strBuiltAnswer As String
    
   Set frm = Forms!frmMain
   Set ctl = frm![lstAnswers]
    
   For Each varItem In ctl.ItemsSelected
     strBuiltAnswer = strBuiltAnswer & ctl.ItemData(varItem)
   Next varItem
    
   Me![txtAnswer] = strBuiltAnswer
   End Sub

2. The following code segment is to be placed in the Current() Event of the Form as indicated. It will parse the value in txtAnswer character by character, and select the appropriate entries in the List Box. This will be done for each individual Record, which is why the code is placed in this Event.
   

   Private Sub Form_Current()
   Dim S As Integer
    
   'Before anything else, you must clear any Selections in the List Box
   'Items may also be selected from the previous Record
   For S = 0 To Me![lstAnswers].ListCount - 1
     Me![lstAnswers].Selected(S) = False
   Next S
    
   If IsNull(Me![txtAnswer]) Then Exit Sub
    
   Dim strLongAnswer As String, intNoOfListItems As Integer
   Dim T As Integer
    
   intNoOfListItems = Me![lstAnswers].ListCount
   strLongAnswer = Me![txtAnswer]
    
   For S = 0 To intNoOfListItems - 1
     Me![lstAnswers].Selected(S) = False
   Next S
    
   For T = 1 To Len(strLongAnswer)
     For S = 0 To intNoOfListItems - 1
       If Me![lstAnswers].Column(0, S) = Mid$(strLongAnswer, T, 1) Then
         Me![lstAnswers].Selected(S) = True
       End If
     Next S
   Next T
   End Sub

3. This code provides the logic as to how to solve the 1 specifc problem that you mentioned. It makes no other assumptions about how your data is structured and as to whether or not it is Normalized.
4. Good luck and let me know how you make out!

That looks just what I want, will try it out when I get to the office and let you know.

As for having the tables normalised, yes, I can that view point, but this particular survey form, with its multiple answers, is one of many and all the look up tables have to have exacatly the same format due to another constraint (Having to have all the answers in multiple languages)

[Lysander]

That looks just what I want, will try it out when I get to the office and let you know.

As for having the tables normalised, yes, I can that view point, but this particular survey form, with its multiple answers, is one of many and all the look up tables have to have exacatly the same format due to another constraint (Having to have all the answers in multiple languages)

Works a dream, thanks

[ADezii]

Works a dream, thanks

You're quite welcome. It was, however, a little tricky!