Listboxes, ItemSelected, Control Source, Multiple Fields... 2

[jpkent]

I have a table in which each row has several checkboxes (yes/no) fields. I thought that instead of modifying this data through using checkboxes on a form, it would be easier to modify this data using a listbox with it's multiselect property set to simple, so that if the user selects an item in the list, then the corresponding checkbox is set to true, or false otherwise. Is there an easy way to implement this? I also wanted the listbox to be able to look up the number of fields that use yes/no. Thanks in advance.

 

[fneily]

Create your check boxes (probably fields off a table). Call them check1, check2, check3, etc. (property sheet, name option). Create a list box using the wizard. Type in your own values which will be the check box names - check1, check2, check3, ets.
On the AfterUpdate event of the list box putin the line For i = 1 to 3, change the 3 to how many check boxes you have):

Private Sub List6_AfterUpdate()
Dim Checkno
For i = 1 To 3
Me("check" & i) = False
Next
For Each Checkno In Me![List6].ItemsSelected
Me("check" & (Checkno + 1)) = True
Next
End Sub

You can now select or deselect the check box names in the list and it will be reflected in the boxes.

Neil

 

[fneily]

By the way, if you want your listbox to show something more definitive then check1, etc. , create a little table that has in the first field check1, check2, etc. and in the second column the name correponding to that checkbox, such as male, female, other, etc. Then create your listbox with the wizard selecting the table and all fields. Change the listbox selection to simple and keep the same code on the AfterUpdate event. I used check1, check2, etc. so I could just loop through the boxes.

Neil

 

[TonyJollans]

Hi blackfire,

You need two routines - one to populate the listbox and one to interpret the results., so ...

Create a form based on your table and create a listbox on it and call it YesNoFields. Set the Row Source Type to "FillListBox" (without the quotes). Put the following function in its code module; this checks your table and builds a list of the column names which are Yes/No types and passes them to the listbox as Access requests them.

Private Static Function FillListBox(ctlField As Control, _
                                    UniqueId, _
                                    ListRow, _
                                    ListColumn, _
                                    CallType) As Variant

    Dim tjDatabase As DAO.Database
    Dim tjTable As DAO.TableDef
    Dim tjField As DAO.Field
    Dim tjYesNoFields() As String

    Select Case CallType
    
        Case acLBInitialize:       FillListBox = True
        Case acLBOpen:             FillListBox = True
        Case acLBGetColumnCount:   FillListBox = 1
        Case acLBGetColumnWidth:   FillListBox = -1
        Case acLBGetValue:         FillListBox = tjYesNoFields(ListRow + 1)
    
        Case acLBGetRowCount
            
            Set tjDatabase = CurrentDb
            Set tjTable = tjDatabase.TableDefs(Me.RecordSource)
        
            ReDim tjYesNoFields(0)
        
            For Each tjField In tjTable.Fields
                If tjField.Type = dbBoolean Then
                    ReDim Preserve tjYesNoFields(UBound(tjYesNoFields) + 1)
                    tjYesNoFields(UBound(tjYesNoFields)) = tjField.Name
                End If
            Next
        
            Set tjField = Nothing
            Set tjTable = Nothing
            Set tjDatabase = Nothing
            
            FillListBox = UBound(tjYesNoFields)
        
    End Select

End Function

Next add the following routine to the Form BeforeUpdate event. This runs through the list and sets Selected columns to True and unselected ones to False.

Private Sub Form_BeforeUpdate(Cancel As Integer)

    Dim tjInt As Integer
    
    For tjInt = 0 To YesNoFields.ListCount - 1
        Me.Controls(YesNoFields.ItemData(tjInt)) = YesNoFields.Selected(tjInt)
    Next
    
End Sub

Enjoy,
Tony

 

[jpkent]

This is exactly what was looking for, and for those who are interested, there's one little suggestion I would make so that the listbox can deal with a changing amount of checkboxes, as well as speed up this procedure a bit (should only matter if you have a massive amount of checkbox fields). Given that your listbox will look up values in another table or file (RowSource property) that could vary in how many records it holds, change the code to the following:

Private Sub List_AfterUpdate()

For i = 0 To List.ListCount - 1
Me("check" & i) = IIf(List.IsSelected(i), True, False)
Next

End Sub

Note: you must also modify your field names to deal with base 0 (check0, check1, check2 ...), or add 1 to each i within the for loop for base 1.

 

[fneily]

Mr. BlackFire
See, people like you are fun to suggest answers to. You take what I gave you and Mr. TonyJollans, studied it, and then not only modified it for your purposes but even had a good suggestion to improve on mine. I was assuming a fixed amount of boxes. I didn't know if you knew about list counts beginning with 0, so in my code I added a 1. Some people have trouble with that concept. Nice going.

Neil

 

[jpkent]

Thank Neil, but actually I just realized I don't have a complete solution yet. What we've figured out so far is how to find and edit new fields in a table, but I realized that I need to be able to add a new field to my table, preferably through the use of a form and not through manually editing the table. I think TonyJollans' code could be modified to do this, but I have had no such luck with the Append method. Can this be done in code or does it have to be done through a macro?

 

[TonyJollans]

Hi blackfire,

'fraid you can't do that. It is not possible to alter a table while it is open for update and you necessarily have it open for update in your form.

Enjoy,
Tony

 

[Poteen]

mmmhhhhh!
Can the two events be separated, I wonder? Hypothetically, of course!!!
(1) The table can be altered while closed.
(2) The table can then be opened and data added to the new field.

Doesn't seem impossible!!

Regards,
John

 

[TonyJollans]

Hi John,

OK, it's not totally impossible, but it's not easy and not without impact.

You could not have the Form bound to the Table, otherwise you'ld have to close the Form to alter the Table. So you'ld have to populate all the unbound controls and handle all the record movement via code, as well as updating all the table columns in code. I suppose if all you've got on the form is the single listbox (and a mechanism for inputing the new column name) it could be done.

Enjoy,
Tony

 

[jpkent]

Ah ha, but the table will be closed. I was planning on adding a new field with another form (not bound to the table). How's it done? I haven't found any helpful information on this other than a cumbersome SQL statement that won't let me supply format parameters to my new field.

 

[TonyJollans]

Hi blackfire,

Adding the column needs a simple bit of SQL and if you only ever use your forms to view and update the table you don't really need to add a format. Anyway here's the code to do the whole thing.

Dim tjDb As DAO.Database
Dim tjTab As DAO.TableDef
Dim tjFld As DAO.Field
Dim tjProp As DAO.Property

Set tjDb = CurrentDb
Set tjTab = tjDb.TableDefs!

TableName

tjDb.Execute "ALTER TABLE

TableName

 ADD COLUMN

ColumnName

 YesNo;"

Set tjFld = tjTab.Fields!

ColumnName

Set tjProp = tjFld.CreateProperty("Format", dbText, "Yes/No")
tjFld.Properties.Append tjProp

tjDb.Close

Set tjProp = Nothing
Set tjFld = Nothing
Set tjTab = Nothing
Set tjDb = Nothing

Enjoy,
Tony