Populate list box using check boxes 2

[forceflow]

I would like to have users click on a command button to open a pop-up form (no problem). The pop-up form consists of 20 or so check boxes. On pop-up form close, I would like to display in a list box on the main form ONLY the names of the items that were checked "YES" (-1)on the pop up form. The items need to be saved for each record so that when the record is recalled, the list box represents the items originally checked. Any ideas how to go about this "cleanly" (brute force will be last resort!).

 

[TheAceMan1]

How are ya forceflow . . . . .

Yes . . . can be done. But I'm compelled to make a suggestion.

On the Popup, a [blue]Multi-Select[/blue] listbox would work great here. Not only would it relieve the user from focusing on those checkboxes (all over the place), but I'm confident you'll find it [blue]looks better & more professional.[/blue]

You could make the listbox in a matter of minutes. With the wizard, just select [blue]I will type in the values[/blue] (alphabetically of course) option. Set a few properties and your done.

I'll stop here and see which way you want tot go . . .

See Ya! . . . . . .

 

[forceflow]

AceMan--Yes I could use list box in the pop-up instead of check boxes. That route will work. Look forward to your help.

 

[TheAceMan1]

forceflow . . . . .

Have to completed the listbox or do you need help with it!

In any case post back:
[ol][li]MainForm Name[/li]
[li]ListBox name on mainform[/li][/ol]

See Ya! . . . . . .

 

[forceflow]

Aceman----
No problem building list boxes, combos etc. Just need some help on the code for the above issue. Thanks

 

[forceflow]

Aceman---
Mainform "frmJobFiles"
Subform "subProducts"

Thanks

 

[TheAceMan1]

forceflow . . . . .

Sorry to get back so late . . . . I'm working this up now.

Things you need to do in the meantime:
[ol][li]Add a [blue]Memo type[/blue] field to the table the MainForm is bound too (to hold the recordsource for the listbox).
Post the name of that field.
Make sure the field is in the [blue]RecordSource[/blue] of the mainform.[/li]
[li]Post the [blue]column(s) number(s) of the popup listbox[/blue] you want to use ([purple]remember column index starts at zero[/purple]) for transfer to the mainform listbox.[/li]
[li]Post the names of both list boxes and ID the form their on like : [blue]FormName!ListboxName[/blue].[/li][/ol]

See Ya! . . . . . .

 

[forceflow]

Aceman----
Memo type field on main to be called "mmoProducts"

Popup list columns: 5 (six product names)
I will actually be having three separate list boxes where I need to do this procedure but for learning purposes, one will work great.

Main: frmJobFiles!lstProducts
Sub: subJobProducts!lstProducts

Feel free to modify names if too many "products" causes confusion. I do not have a problem keeping it straight but that is because it is my "child"

 

[TheAceMan1]

[blue]Popup list columns: 5 (six product names)[/blue]

This may change method used. Is it your intent to pass all five colums or just one (the less the better)?

Popup form name is (unless you saying the subform is the pop-up)?

See Ya! . . . . . .

 

[forceflow]

Oops--Sorry about that on the columns. Single column is all that is needed. I will have probably 5 to 10 rows in the column. Popup form name "popSelectProducts"

 

[TheAceMan1]

OK forceflow . . . here we go . . .
[ol][li]Open [blue]popSelectProducts[/blue] in design view.[/li]
[li]Set the listbox [blue]Multi Select[/blue] property to [purple]Extended[/purple].[/li]
[li]Add a [blue]Command Button[/blue] and copy/paste the following code to the [blue]OnClick[/blue] event.

[blue]   Dim frm As Form, ctl As Control, idx, Build As String
   
   Set frm = Forms!frmJobFiles
   Set ctl = Me!lstProducts [green]'Popup Listbox Name[/green]
   
   If ctl.ItemsSelected.Count > 0 Then
      
      For Each idx In ctl.ItemsSelected
         If Build <> "" Then
            Build = Build & ";" & ctl.ItemData(idx)
         Else
            Build = ctl.ItemData(idx)
         End If
      Next
      
      frm!lstProducts.RowSource = Build
      frm!mmoProducts = Build
   End If
   
   Set ctl = Nothing
   Set frm = Nothing[/blue]

[/li]
[li]Save & Close the form.[/li]
[li]Open [blue]frmJobFiles[/blue] in design view.[/li]
[li]Set the following [blue]properties[/blue] for [blue]lstProducts:[/blue]
[ol a][blue][li]Column Count [purple]1[/purple][/li]
[li]Bound Column [purple]1[/purple][/li]
[li]Row Source Type [purple]Value List[/purple][/li]
[li]Row Source is [purple]Empty/Blank[/purple][/li][/blue][/ol][/li]
[li]In the [blue]OnCurrent[/blue] event of the form, copy/paste the following"

[blue]   If Trim(Me!mmoProducts & "") = "" Then
      Me!lstProducts.RowSource = ""
   Else
      Me!lstProducts.RowSource = Me!mmoProducts
   End If[/blue]

[/li][/ol]
The OnCurrent event [blue]updates the listbox when changing records.[/blue]

Thats it for the code.

When your satisified with the code, add two [blue]Labels[/blue] along side the listbox in the popup to explain multiselect. Example messages ([purple]hit Ctrl + Enter for new line[/purple]):
[ol][li]To make multiple indivisual selections:
hold Ctrl as you click.[/li]
[li]To select a range:
Select the first
Hold Shift & select the second.[/li][/ol]

See Ya! . . . . . .

 

[forceflow]

Aceman.....
You rock! Perfect code. works great. I have wanted to find a way to do this for the last year, looked on Tek-Tips from time to time and finally asked. Big thanks!!!

forceflow

 

[forceflow]

Aceman---
Hate to complicate things but what if I wanted to have three separate list boxes on the popup form, then combine the items chosen in each of the three to display in one list box on the main form? Possible? or ugly.

Example:

On Popup form:
lstA = Item1 and Item4 selected
lstB = Category2 and Category5 selected
lstC = Accessory5 and Accessory 10 selected


Then Main form would display list box as follows
lstABC = Item1, Item4, Category2, Category5, Accessory5, Accesory10


forceflow

 

[TheAceMan1]

forceflow . . . . .

Glad we could help ;-)

[blue] . . . what if I wanted to have three separate list boxes on the popup form, then [purple]combine the items[/purple] chosen in each of the three [purple]to display in one list box[/purple] on the main form? Possible? or ugly.[/blue]

Yes . . . can be done, but the method used depends on the following criteria:

[blue]Considering the maximum row count expected of each listbox, will the total character count of all three listboxes (including semicolon as name sperator) exceed 2048 characters?[/blue]

The above is the limit of the [blue]RowSource[/blue] when using [blue]RowSourceType[/blue] of [purple]Value List[/purple].

See Ya! . . . . . .

 

[forceflow]

Will not exceed 2048. Thanks

forceflow

 

[TheAceMan1]

forceflow . . . . .

I intend to sort (alphabetically) the final compiled list.

Is this ok, or do you want lst a,b,c specifically in that order?

See Ya! . . . . . .

 

[forceflow]

Aceman:

Would rather have lstA B C order than alphabetical

forceflow

 

[TheAceMan1]

Roger That forceflow!

[ol][li]If [blue]you enter the data[/blue] for the listboxes [blue]in proper order[/blue], I wont have too . . .[/li]
[li]Change the [blue]Name Property[/blue] of the listboxes to [purple]ListA[/purple], [purple]ListB[/purple], [purple]ListC[/purple] appropriately.[/li][/ol]
I have to run an errand, but I should be back within the hour . . .

See Ya! . . . . . .

 

[forceflow]

Aceman----
I had planned on entering each of the values for each popup list box in my preferred order of display.

I hope that frmJobFiles!lstAllProductsSelected
can display like this:

First or "top" row(s)of "lstAllProductsSelected" would be the selected data from lstA on popup (of course in row source order)

The next lower row(s) would be the data from lstB on the popup.

And finally, the lowest rows of data in the list box would be item(s) selected form lstC on popup.

 

[TheAceMan1]

[blue]I had planned on entering each of the values for each popup list box in my preferred order of display.[/blue]

Thats impossible! . . . . . [purple]I bet you thought![/purple] ;-)

Replace the code in the [blue]CommandButton on the popup[/blue] with the following:

[blue]   Dim frm As Form, ctl As Control
   Dim idx, Build As String, x As Integer
   
   Set frm = Forms!frmJobFiles
   
   For x = 65 To 67 [green]'A to C[/green]
      Set ctl = Me("List" & Chr(x)) [green]'Popup Listbox Name[/green]
   
      If ctl.ItemsSelected.Count > 0 Then
         
         For Each idx In ctl.ItemsSelected
            If Build <> "" Then
               Build = Build & ";" & ctl.ItemData(idx)
            Else
               Build = ctl.ItemData(idx)
            End If
         Next
      End If
      
      Set ctl = Nothing
   Next
   
   If Build <> "" Then
      frm!lstproducts.RowSource = Build
      frm!mmoProducts = Build
   End If
   
   Set frm = Nothing[/blue]

You might want to add [blue]Clear[/blue] buttons under each listbox with the following:

[blue]   Me!ListboxName = Null[/blue]

and/or and [blue]All Clear[/blue] button with:

[blue]   Dim x As Integer
   
   For x = 65 To 67 [green]'A to C[/green]
      Me("List" & Chr(x)) = Null
   Next[/blue]

[purple]Cheers![/purple]

See Ya! . . . . . .