Multi-select listbox. (1 Viewer)

[Henley12]

I have several enhanced listboxes in a form, but they are not saving the data selected to my table. Any ideas why?

 

[wazz]

need more info.
what do you mean, 'enhanced'?
how are you trying to save data now?

 

[Henley12]

I meant to type 'extended'. I have the control source set to a field in my table, but it doesn't want to save the selections to the table. Is there something I am leaving out? This is an Access 2007 database.

 

[wazz]

still don't know what you mean by 'save the selections to the table'. you are already seeing values from a table. where are you trying to save the selections? and how. can you show us what you are trying now? can't explain what's wrong if we don't see. tnx.

 

[boblarson]

Multi-select listboxes can't be bound to a field as they do not have a VALUE returned (it is always NULL). You need to use code to store it. And, if you are trying to store multiple items from a listbox to a single field, then I think you should redesign that. It isn't good to store multiple items in just a single field.

 

[Henley12]

I load the selections from another table, but the control source of the listbox is set to the field in the main table linked to the form. For instance, let's say you have a form to keep track of people and the states they've been to. You have a multi-select listbox to list the states, which is populated from a separate table of states. You want the states you select to be saved to the main table for each record in the table.

 

[boblarson]

Sorry, I'm a bit confused by your explanation. Can you post a screenshot of what you're talking about?

 

[Henley12]

Here is a screenshot of what I am working on.

 

[Henley12]

Any ideas on this one? The selections I make need to be highlighted in some way if I open the record later for editing. I'm sure there is a way to do this, but I cannot for the life of me remember how to accomplish it. I must be getting old.

 

[rainman89]

Here is some code that I used to repopulate a multi select listbox

Dim rs As Recordset
Dim reccount As Integer

Set db = CurrentDb()

SQL = "select * from qry_benchskill where studentYearID=" & Forms!frm_aisreportcard!Text47 & "AND termID=" & Me.cbotermID & "and categoryId= 1" & "And skillid=1"
Set rs = db.OpenRecordset(SQL)

rs.MoveFirst
    Do Until rs.EOF
            benchid = rs!BenchmarkcountID - 1
               Me.frm_benchmark!cbo_Phonemic1.Selected(benchid) = True
            rs.MoveNext
    Loop

 

[Henley12]

I need to reopen this discussion. Ray, I'm not sure how to make your idea work in my case. I have a form with multiple listboxes. When I open the form, it displays the records and of course the first time, no choices have been selected in the listboxes. When those choices are made, I need to save that info for each listbox for each record and when the form is opened again, the choices made previously for that record will be selected in the listboxes. Does that make any sense at all?

 

[rainman89]

Yeah that is what my code does.

Ill try to break it down for you

Dim rs As Recordset
Dim reccount As Integer

Set db = CurrentDb()

SQL = "select * from qry_benchskill where studentYearID=" & Forms!frm_aisreportcard!Text47 & "AND termID=" & Me.cbotermID & "and categoryId= 1" & "And skillid=1"
Set rs = db.OpenRecordset(SQL)

So all that code does is query my tables and selects the options that I want to repopulate in my list box

In this case I want to return the results based on who is logged in and what term they are using. I set category and Skill to 1 since that is my first multi-select box on my form and have it set up that way in the tables.

Our next step is to go through the records

rs.MoveFirst
    Do Until rs.EOF
            benchid = rs!BenchmarkcountID - 1
               Me.frm_benchmark!cbo_Phonemic1.Selected(benchid) = True
            rs.MoveNext
    Loop

This steps through the records that were returned by the query and, if they ID matches the ID in the listbox, it selects it again.

That help at all?

 

[vbaInet]

If these list boxes are bound then you won't be able to do it. If they are not here's you would need to save values of the last selections in a table and call the values from that table.

 

[rainman89]

If these list boxes are bound then you won't be able to do it. If they are not here's you would need to save values of the last selections in a table and call the values from that table.

Correct, I forgot to mention that they are unbound listboxes

If i have time tomorrow, I may be able to make a small example

 

[vbaInet]

See how good rainman89 is to you, making a small example (if time permits of course). Hehe!

It would be nice to have a "Call Last Saved" button as well which will perform the selections.

 

[rainman89]

Here is a quick example on how to select options in your listbox based on what records are in a table

Click the select records button to see it select the appropriate records.

If you wanna see it change, then edit the records in the table benchskill

Any ????s let me know