By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,827 Members | 2,256 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,827 IT Pros & Developers. It's quick & easy.

Display Multiselect on list box

Expert 100+
P: 344
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?
Jun 8 '07 #1
Share this Question
Share on Google+
8 Replies


ADezii
Expert 5K+
P: 8,615
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.
Expand|Select|Wrap|Line Numbers
  1. Col1    Col2
  2. A       Glass
  3. B       Knife
  4. C       Scissors
  5. D       Bottle
  6. E       Howitzer
  7. F       Lead Pencil
  8.  
If the User selects A, B, D, and F, store ABDF in the underlying Table?
Jun 9 '07 #2

Expert 100+
P: 344
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.
Expand|Select|Wrap|Line Numbers
  1. Col1    Col2
  2. A       Glass
  3. B       Knife
  4. C       Scissors
  5. D       Bottle
  6. E       Howitzer
  7. F       Lead Pencil
  8.  
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.
Jun 10 '07 #3

ADezii
Expert 5K+
P: 8,615
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.
Jun 10 '07 #4

FishVal
Expert 2.5K+
P: 2,653
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))
Jun 10 '07 #5

ADezii
Expert 5K+
P: 8,615
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.
    Expand|Select|Wrap|Line Numbers
    1. Private Sub lstAnswers_AfterUpdate()
    2. Dim varItem As Variant, frm As Form, ctl As ListBox
    3. Dim strBuiltAnswer As String
    4.  
    5. Set frm = Forms!frmMain
    6. Set ctl = frm![lstAnswers]
    7.  
    8. For Each varItem In ctl.ItemsSelected
    9.   strBuiltAnswer = strBuiltAnswer & ctl.ItemData(varItem)
    10. Next varItem
    11.  
    12. Me![txtAnswer] = strBuiltAnswer
    13. 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.
    Expand|Select|Wrap|Line Numbers
    1. Private Sub Form_Current()
    2. Dim S As Integer
    3.  
    4. 'Before anything else, you must clear any Selections in the List Box
    5. 'Items may also be selected from the previous Record
    6. For S = 0 To Me![lstAnswers].ListCount - 1
    7.   Me![lstAnswers].Selected(S) = False
    8. Next S
    9.  
    10. If IsNull(Me![txtAnswer]) Then Exit Sub
    11.  
    12. Dim strLongAnswer As String, intNoOfListItems As Integer
    13. Dim T As Integer
    14.  
    15. intNoOfListItems = Me![lstAnswers].ListCount
    16. strLongAnswer = Me![txtAnswer]
    17.  
    18. For S = 0 To intNoOfListItems - 1
    19.   Me![lstAnswers].Selected(S) = False
    20. Next S
    21.  
    22. For T = 1 To Len(strLongAnswer)
    23.   For S = 0 To intNoOfListItems - 1
    24.     If Me![lstAnswers].Column(0, S) = Mid$(strLongAnswer, T, 1) Then
    25.       Me![lstAnswers].Selected(S) = True
    26.     End If
    27.   Next S
    28. Next T
    29. 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!
Jun 10 '07 #6

Expert 100+
P: 344
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.
    Expand|Select|Wrap|Line Numbers
    1. Private Sub lstAnswers_AfterUpdate()
    2. Dim varItem As Variant, frm As Form, ctl As ListBox
    3. Dim strBuiltAnswer As String
    4.  
    5. Set frm = Forms!frmMain
    6. Set ctl = frm![lstAnswers]
    7.  
    8. For Each varItem In ctl.ItemsSelected
    9.   strBuiltAnswer = strBuiltAnswer & ctl.ItemData(varItem)
    10. Next varItem
    11.  
    12. Me![txtAnswer] = strBuiltAnswer
    13. 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.
    Expand|Select|Wrap|Line Numbers
    1. Private Sub Form_Current()
    2. Dim S As Integer
    3.  
    4. 'Before anything else, you must clear any Selections in the List Box
    5. 'Items may also be selected from the previous Record
    6. For S = 0 To Me![lstAnswers].ListCount - 1
    7.   Me![lstAnswers].Selected(S) = False
    8. Next S
    9.  
    10. If IsNull(Me![txtAnswer]) Then Exit Sub
    11.  
    12. Dim strLongAnswer As String, intNoOfListItems As Integer
    13. Dim T As Integer
    14.  
    15. intNoOfListItems = Me![lstAnswers].ListCount
    16. strLongAnswer = Me![txtAnswer]
    17.  
    18. For S = 0 To intNoOfListItems - 1
    19.   Me![lstAnswers].Selected(S) = False
    20. Next S
    21.  
    22. For T = 1 To Len(strLongAnswer)
    23.   For S = 0 To intNoOfListItems - 1
    24.     If Me![lstAnswers].Column(0, S) = Mid$(strLongAnswer, T, 1) Then
    25.       Me![lstAnswers].Selected(S) = True
    26.     End If
    27.   Next S
    28. Next T
    29. 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)
Jun 11 '07 #7

Expert 100+
P: 344
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
Jun 11 '07 #8

ADezii
Expert 5K+
P: 8,615
Works a dream, thanks
You're quite welcome. It was, however, a little tricky!
Jun 11 '07 #9

Post your reply

Sign in to post your reply or Sign up for a free account.