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!).

 

[forceflow]

Excellent Code Aceman! Not one hang-up or glitch!

A couple other things now that I have all that working:

What about a method to completely clear the frmJobFile!lstProducts field?

How can I convert existing table fields that are not null into the mmoProducts field? I originally used check boxes for about 20 items (now in my three list boxes)so their fields either equal -1 or 0. How would I take all the items in a row that are marked -1 and combine them by item name into the field mmoProducts? For simplicity sake, the record source order of items will not be critical.

Thanks for all your help!

 

[PHV]

completely clear the frmJobFile!lstProducts
frmJobFile!lstProducts.RowSource = ""

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[TheAceMan1]

[blue]What about a method to completely clear the frmJobFile!lstProducts field?[/blue]

In my prior post I provided code for [purple]clearing listboxes.[/purple]

[blue]How can I convert existing table fields that are not null into the mmoProducts field?[/blue]

Am I understanding you want to populate mmoProducts with equivalent selections made by the origional checkboxes and consequently get rid of those 20 fields?

See Ya! . . . . . .

 

[forceflow]

Exactly! Other 20 fields can be deleted after data is merged over to mmoProducts so the data can then be displayed in the frmJobFiles!lstProducts instead of using 20 check boxes on the form.

forceflow

 

[TheAceMan1]

Roger That forceflow . . . . .

I have to run out so I'll have to get back to ya later this afternoon with the code. In the meantime do this:

Backup the table with the checkboxes (so you can come back to square one if you have too):
[ol][li]Right-click the table & copy.[/li]
[li]Right-click a blank area in the tables window & paste.[/li]
[li]Give the pasted table the same name only post append [blue]_bu[/blue].[/li][/ol]
Next:
[ol][li]Open the origional table with the checkboxes in [blue]design view[/blue] (I assume mmoProducts is in this table as well. If not notify me asap which table).[/li]
[li]Rename the fields to their [blue]respective entry[/blue] in the three list boxes.[/li]
[li]From top to bottom, [purple]sequentially lineup the checkboxes in your custom order.[/purple][/li]
[li] Starting at zero and Counting from the top, postback the [blue]starting position of the 1st checkbox[/blue].[/li]
[li]If checkbox count is [blue]other than 20[/blue] post that as well.[/li]
[li]Post back the [blue]table name[/blue] so I can stick it in the code for ya.[/li][/ol]

See Ya! . . . . . .

 

[forceflow]

When you say respective entry do you mean for example to change the name of the table fields to ListA0, ListA1, ListA2, etc?

[blue]forceflow[/blue]

 

[TheAceMan1]

forceflow . . . . .

No! . . . . the actual text that would be transferred to mmoProducts when you make a selection!

I'm going to parse thru the checkbox fields for each record (looking for true value) and the [blue]fieldnames have to be the actual text to transfer to mmoProducts![/blue]

Example:
A checkbox fieldname of [blue]Chk1[/blue] has its equivalent selection in [blue]ListboxC - 4th row[/blue], which is say [purple]Birds[/purple]. [blue]Chk1[/blue] has to be changed to [purple]Birds[/purple].

See Ya! . . . . . .

 

[forceflow]

Roger that Aceman. Leave data type as "YES/NO" for now?

 

[forceflow]

Aceman----
tblJobFiles

Check box qty: 23

Starting Position in table: 8 (counting "0" as 1)

forceflow

 

[TheAceMan1]

forceflow . . . . .

Yes!

Don't forget, after you rename them all, order them from top to bottom just like they are in the listboxes with listA checkboxes 1st then b & c (AKA your custom order).

See Ya! . . . . . .

 

[forceflow]

Did it.

Thanks Aceman

 

[TheAceMan1]

OK forceflow . . . . here we go . . .
[ol][li]In a [blue]module[/blue] in the [blue]modules[/blue] window, copy/paste the following routine:

[blue]Public Sub UpDateMMO()
   Dim db As DAO.Database, rst As DAO.Recordset
   Dim Build As String, x As Integer, Name As String
   
   Set db = CurrentDb()
   Set rst = db.OpenRecordset("tblJobFiles", dbOpenDynaset)
   
   Do
      Build = "" [green]'reset for next MMO Build[/green]
      
      For x = 8 To 30 [green]'23 checkboxes[/green]
         Name = rst.Fields(x).Name [green]'Actual MMO text is Name![/green]
         
         If rst(Name) Then
            If Build <> "" Then
               Build = Build & ";" & Name
            Else
               Build = Name
            End If
         End If
      Next
      
      If Build <> "" Then
         rst.Edit
         rst!mmoProducts = Build [green]'Update[/green]
         rst.Update
      End If
      
      rst.MoveNext
   
   Loop Until rst.EOF
   
   Set rst = Nothing
   Set db = Nothing

End Sub[/blue]

Leave the module open.[/li]
[li]Click the [purple]Immediate Window[/purple] toolbar button

.[/li]
[li]In the [purple]Immediate Window[/purple] type [purple]Call UpDateMMO[/purple] and hit enter.[/li]
[li]Check mmoProducts in the table and/or form![/li][/ol]
[purple]Cheers![/purple]

See Ya! . . . . . .

 

[forceflow]

Getting a compile error for [blue]db as DAO.database [/blue]

[red]"User defined type not defined"[/red]

Ideas?

forceflow

 

[TheAceMan1]

forceflow . . . . .

The code requires [purple]Microsoft DAO 3.6 Object Library[/purple] to run. To [blue]check/install[/blue] the library, in the same code window click [blue]Tools[/blue] - [blue]References...[/blue] In the listing find the library and [blue]make sure its checked.[/blue] Then using the up arrow, [purple]push it up as high in priority as it will go[/purple]. Click OK.

Try running the code again . . . .

See Ya! . . . . . .

 

[forceflow]

Aceman--
That worked perfectly. You just saved me a ton of time!

forceflow