multi select 3

[member 37483]

Can someone please help me out here. I am trying to make a list box the pulls from a query. This users can select multiple things from this list box and the results go into a table. I can't get this to work. Please help me out.
John

 

[WaltLukeIII]

In the properties for the Combo Box set the Row Source equal the SQL expression that equals your query.

Walt III
SAElukewl@netscape.net

http://www.muc.edu/~lukewl

 

[member 37483]

thanks,
but I can't get it to have the multiple selections go into the table. please help.

 

[raskew]

You could probably benefit from studying the Access Help Files, looking at 'MultiSelect property' and 'ItemsSelected collection' (particularly the examples).

Look at the properties of your list box. Set the Multi Select property (Others tab) to Extended. This will allow you to hold down the Control key and randomly select items. If you want an uninterrupted block of items, select the first item, then hold down the Shift key and click on the last item of the group.

You'll need a command button which you'll click when you're satisfied with your selections. Code for the OnClick event could begin something like this:

Private Sub Command4_Click()
Dim Q As QueryDef, db As Database
Dim Criteria As String, strSQL as String
Dim ctl As Control
Dim Itm As Variant

' Build a list of the selections.
Set ctl = Me![List0]

'the following routine builds a comma-delimited string of items selected, e.g., if SSN is the bound 'column, then Criteria might end up looking like this: "122321312, '333333333, 333333332"

For Each Itm In ctl.ItemsSelected
If Len(Criteria) = 0 Then
Criteria = ctl.ItemData(Itm)
Else
Criteria = Criteria & ", " & ctl.ItemData(Itm)
End If
Next Itm

'**********
'Now, you've got to do something with the Criteria string. In your example, you want to append 'it to a table, so you need to build an append query. Say for example that tblClients is your 'record source, and you want to append the selected clients to tblSelected. The OnClick code 'might continue:
'**********
'build SQL for an append query

strSQL = "INSERT INTO tblSelected SELECT * FROM tblClients Where [SSN] In(" & Criteria & ""

'Finally, you need to run the query:

DoCmd.SetWarnings False

DoCmd.RunSQL strSQL

DoCmd.SetWarnings True

End Sub

 

[WaltLukeIII]

Do you mean you can't get the value selected to be the value put into a certain field in the table.

Set the control property to be equal to the field that you want to set. Make sure you change the "limit to list" setting to NO if your query changes from time to time that way data other than what is in the list may be entered.

Walt III
SAElukewl@netscape.net

http://www.muc.edu/~lukewl

 

[raskew]

The original post said "... users can select multiple things from this list box". I read this to mean multiselect list box (in which case I stand by my advice). If in fact the poster wanted to select items one at a time, have them processed, then select more items (including duplicates) then I can understand WaltLuke's concern.

Maybe we can get some clarification on what's really intended.

 

[raskew]

The original post said "...users can select multiple things from this list box". I read this to mean multiselect list box, and that the poster had already populated the list box, so Row Source wasn't the problem.

If in fact this is a question about how to use a multiselect list box, then I stand by my advice. If, however, the intent is to use a plain-vanilla list box, select an item and, in the AfterUpdate event do something with the selection, then repeat the process (including selecting duplicate entries), then Luke's concern is understandable.

John- Would you please clarify the problem.

 

[member 37483]

It's like you said raskew. I have a list box with information already in it. I have users that will be selecting more than one choice. So I figured to set the multiselect to extended. I want the multiple selections to go into a table (all in the same field). What's happening is that the table is showing no entries in the field that the information is supposed to go in. Like when I click 5 items from the list box, I should see 5 items entered. I don't. I am trying to use your suggestions raskew, but I am not good at programming like you appear to be. I guess if you or Luke or anybody could give me directions that I could use by just changing the information in the properties. Or maybe if you could tell me what inside the programming to change so that I could use what you've written raskew. Well guys, I hope I've been descriptive. I appreciate all of your help.

 

[Zorro1265]

I have the same dillema, I want each itme selected in a multiselect list box to be entered into a table. 5 items selected 5 seperate entries in the table. I have posted several times but no one has been able to help. If you get something good please let me know!

 

[WaltLukeIII]

Zorro

For your answer I would do actual code
Perhaps after a button is pushed or after_Update for a textfield,
Do five separate insert statements.

if the data is similar then maybe you can use a loop. or just do each one individually.


Walt III
SAElukewl@netscape.net

http://www.muc.edu/~lukewl

 

[Zorro1265]

WaltLukeIII

I wish I knew how to code! Ideally my problem would allow someone to add or change their mind within the list box elements. I posted to jdgsr about the multiselect list box I found that works for what he wants and I would like the same functionality just to store the data more efficiently.

 

[member 37483]

Zorro

Are you getting any entries in the fields. I am not. If so tell me how you did that.

John

 

[Zorro1265]

Did you see my other post? Look at this web page it will talk you through what you want to do.

http://support.microsoft.com/suppor...nk=1&src=DHCS_MSPSS_gn_SRCH&SPR=MSALL

 

[member 37483]

thanks zorro.