Using Checkboxes to select Multiple Options

C

corynicole

OK - here goes my explanation of what I am trying to accomplish. I am
creating a database for a horse show to first collect entries, second
store the placings as the announcers complete the class and third
generate reports with class winners and an eventual high point award.
The basic premise is that there will be a show each week and there are
several members who may compete in several different events at each
show. Each membership may have several family members participating.
My table structure is as follows:

Members
-------------------
Membership Number (Primary Key)
FName
LName
Address
etc

HorseRiderCombo
---------------------------------
Horse&Rider (Primary Key)
Membership Number
Age Division

Class Descriptions
---------------------------------
Class Number (Primary Key)
Class Description

Show Results
----------------------------------
Class Date
Class Number
Horse & Rider
Placing
Notes

I currently have a form that utilizes a combo box to select the
desired horse and rider. It then populates the age division and
membership number just for verification purposes. Then I have a
subform based off of the show results form. This subform is auto
populating the date and Horse & Rider info. Currently I have it set
up as a combo box for the class number so that a user can select a
class number from the drop down list based off of the class
description table, enter any relevant note for that class (ie rider is
changing horses or whatever comes up) and go on to the next row in the
datasheet view and select the next class number from the list. This
works perfectly for what I need to do.

However, the peole I am designing the interface for feel that the drop
down box is too time consuming and confusing (not experienced computer
people at all). I am wondering if there is a way to create the same
type of scenario using checkboxes or something similiar that will make
it easier for them to just check the classes the member wants to
participate in, but still maintain a similiar table structure. I got
the idea of checkboxes from this site http://www.iopath.com/horseshow.htm
where they show a select multiples option as a screen shot at almost
the bottom of the page.

I am somewhat experienced with databases, SQL, VBA and other computer
applications so any assistance you all can provide is much
appreciated. And, if I am missing a detail or not explaining
something well please let me know.

Thanks
Nicole
 
G

Golfinray

You can add fields to your table of choice. Make them yes/no fields. Then add
the checkboxes to your form. Be aware, however, that checkboxes store checks
as -1's and not checked as 0's so you will have to do something to deal with
that. Such as in a query, IIf([your field]=-1,"yes","no")
 
C

corynicole

Thanks for your help. I thought of that - my problem is that the
number of classes can change from show to show. So, I hesitate to
create 40+ different columns in the table if I don't have to. I think
I may have a solution in a multi select list box and then writing the
results via code. I am hoping that will be a decent solution.

You can add fields to your table of choice. Make them yes/no fields. Thenadd
the checkboxes to your form. Be aware, however, that checkboxes store checks
as -1's and not checked as 0's so you will have to do something to deal with
that. Such as in a query, IIf([your field]=-1,"yes","no")



[email protected] said:
OK - here goes my explanation of what I am trying to accomplish.  I am
creating a database for a horse show to first collect entries, second
store the placings as the announcers complete the class and third
generate reports with class winners and an eventual high point award.
The basic premise is that there will be a show each week and there are
several members who may compete in several different events at each
show.  Each membership may have several family members participating.
My table structure is as follows:
Click to expand...
Members
-------------------
Membership Number (Primary Key)
FName
LName
Address
etc
Click to expand...
HorseRiderCombo
Click to expand...
Class Descriptions
Click to expand...
Show Results
----------------------------------
Class Date
Class Number
Horse & Rider
Placing
Notes
Click to expand...
I currently have a form that utilizes a combo box to select the
desired horse and rider.  It then populates the age division and
membership number just for verification purposes.  Then I have a
subform based off of the show results form.  This subform is auto
populating the date and Horse & Rider info.  Currently I have it set
up as a combo box for the class number so that a user can select a
class number from the drop down list based off of the class
description table, enter any relevant note for that class (ie rider is
changing horses or whatever comes up) and go on to the next row in the
datasheet view and select the next class number from the list.  This
works perfectly for what I need to do.
Click to expand...
However, the peole I am designing the interface for feel that the drop
down box is too time consuming and confusing (not experienced computer
people at all).  I am wondering if there is a way to create the same
type of scenario using checkboxes or something similiar that will make
it easier for them to just check the classes the member wants to
participate in, but still maintain a similiar table structure.   I got
the idea of checkboxes from this sitehttp://www.iopath.com/horseshow.htm
where they show a select multiples option as a screen shot at almost
the bottom of the page.
Click to expand...
I am somewhat experienced with databases, SQL, VBA and other computer
applications so any assistance you all can provide is much
appreciated.  And, if I am missing a detail or not explaining
something well please let me know.
Click to expand...
Thanks
Nicole- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...
 
J

John W. Vinson

However, the peole I am designing the interface for feel that the drop
down box is too time consuming and confusing (not experienced computer
people at all). I am wondering if there is a way to create the same
type of scenario using checkboxes or something similiar that will make
it easier for them to just check the classes the member wants to
participate in, but still maintain a similiar table structure.
Click to expand...

Unbound checkboxes is one possibliity; I'd have to disagree with Golfinray,
putting yes/no fields in a table would become a maintenance nightmare as the
list of classes changes, though.

One other tool that might be easier is a multiselect Listbox. It would show
all the available classes onscreen, and the user could simply select whichever
ones they want. You will need some code to move those selections to the table;
try adapting this to your needs:

Private Sub cmdProcess_Click()
' Comments : Update the AnimalCondition table based on the selections in
' the unbound multiselect listbox lstHealthIssues.
' Newly selected rows will be added to the table, newly
' cleared rows will be deleted.
' Parameters: None
' Modified : 01/29/02 by JWV
'
' --------------------------------------------------
' Populate the AnimalCondition table with the selected issues
On Error GoTo PROC_ERR

Dim iItem As Integer
Dim lngCondition As Long
Dim db As DAO.Database
Dim rs As DAO.Recordset

' save the current record if it's not saved
If Me.Dirty = True Then
Me.Dirty = False
End If
Set db = CurrentDb
' Open a Recordset based on the table
Set rs = db.OpenRecordset("AnimalCondition", dbOpenDynaset)
With Me!lstHealthIssues
' Loop through all rows in the Listbox
For iItem = 0 To .ListCount - 1
lngCondition = .Column(0, iItem)
' Determine whether this AnimalID-HealthID combination is
' in the table
rs.FindFirst "[AnimalID] = " & Me.AnimalID & " AND " _
& "[HealthIssueID] = " & lngCondition
If rs.NoMatch Then ' this item has not been added
If .Selected(iItem) Then
' add it
rs.AddNew
rs!AnimalID = Me.AnimalID
rs!HealthIssueID = lngCondition
rs.Update
End If ' if it wasn't selected, ignore it
Else
If Not .Selected(iItem) Then
' delete this record if it's been deselected
rs.Delete
End If ' if it was selected, leave it alone
End If
Next iItem
End With
rs.Close
Set rs = Nothing
Set db = Nothing
Me.subAnimalCondition.Requery

PROC_EXIT:
Exit Sub

PROC_ERR:
MsgBox "Error " & Err.Number & " in cmdProcess_Click:" _
& vbCrLf & Err.Description
Resume PROC_EXIT

End Sub
 
C

corynicole

That should work beautifully! I will try and adapt it to my tables
and post back if I have any problems.

Thanks for your help!!



However, the peole I am designing the interface for feel that the drop
down box is too time consuming and confusing (not experienced computer
people at all).  I am wondering if there is a way to create the same
type of scenario using checkboxes or something similiar that will make
it easier for them to just check the classes the member wants to
participate in, but still maintain a similiar table structure.
Click to expand...

Unbound checkboxes is one possibliity; I'd have to disagree with Golfinray,
putting yes/no fields in a table would become a maintenance nightmare as the
list of classes changes, though.

One other tool that might be easier is a multiselect Listbox. It would show
all the available classes onscreen, and the user could simply select whichever
ones they want. You will need some code to move those selections to the table;
try adapting this to your needs:

Private Sub cmdProcess_Click()
    ' Comments  : Update the AnimalCondition table based on the selections in
    '             the unbound multiselect listbox lstHealthIssues.
    '             Newly selected rows will be added to the table, newly    
    '            cleared  rows will be deleted.
    ' Parameters: None
    ' Modified  : 01/29/02 by JWV
    '
    ' --------------------------------------------------
    ' Populate the AnimalCondition table with the selected issues
    On Error GoTo PROC_ERR

    Dim iItem As Integer
    Dim lngCondition As Long
    Dim db As DAO.Database
    Dim rs As DAO.Recordset

    ' save the current record if it's not saved
    If Me.Dirty = True Then
        Me.Dirty = False
    End If
    Set db = CurrentDb
    ' Open a Recordset based on the table
    Set rs = db.OpenRecordset("AnimalCondition", dbOpenDynaset)
    With Me!lstHealthIssues
        ' Loop through all rows in the Listbox
        For iItem = 0 To .ListCount - 1
            lngCondition = .Column(0, iItem)
            ' Determine whether this AnimalID-HealthID combination is
            ' in the table
            rs.FindFirst "[AnimalID] = " & Me.AnimalID & " AND " _
                         & "[HealthIssueID] =" & lngCondition
            If rs.NoMatch Then ' this item has not been added
                If .Selected(iItem) Then
                    ' add it
                    rs.AddNew
                    rs!AnimalID = Me.AnimalID
                    rs!HealthIssueID = lngCondition
                    rs.Update
                End If ' if it wasn't selected, ignore it
            Else
                If Not .Selected(iItem) Then
                    ' delete this record if it's beendeselected
                    rs.Delete
                End If ' if it was selected, leave it alone
            End If
        Next iItem
    End With
    rs.Close
    Set rs = Nothing
    Set db = Nothing
    Me.subAnimalCondition.Requery

PROC_EXIT:
    Exit Sub

PROC_ERR:
    MsgBox "Error " & Err.Number & " in cmdProcess_Click:" _
           & vbCrLf & Err.Description
    Resume PROC_EXIT

End Sub
Click to expand...
 
H

hor vannara

OK - here goes my explanation of what I am trying to accomplish. I am
creating a database for a horse show to first collect entries, second
store the placings as the announcers complete the class and third
generate reports with class winners and an eventual high point award.
The basic premise is that there will be a show each week and there are
several members who may compete in several different events at each
show. Each membership may have several family members participating.
My table structure is as follows:

Members
-------------------
Membership Number (Primary Key)
FName
LName
Address
etc

HorseRiderCombo
---------------------------------
Horse&Rider (Primary Key)
Membership Number
Age Division

Class Descriptions
---------------------------------
Class Number (Primary Key)
Class Description

Show Results
----------------------------------
Class Date
Class Number
Horse & Rider
Placing
Notes

I currently have a form that utilizes a combo box to select the
desired horse and rider. It then populates the age division and
membership number just for verification purposes. Then I have a
subform based off of the show results form. This subform is auto
populating the date and Horse & Rider info. Currently I have it set
up as a combo box for the class number so that a user can select a
class number from the drop down list based off of the class
description table, enter any relevant note for that class (ie rider is
changing horses or whatever comes up) and go on to the next row in the
datasheet view and select the next class number from the list. This
works perfectly for what I need to do.

However, the peole I am designing the interface for feel that the drop
down box is too time consuming and confusing (not experienced computer
people at all). I am wondering if there is a way to create the same
type of scenario using checkboxes or something similiar that will make
it easier for them to just check the classes the member wants to
participate in, but still maintain a similiar table structure. I got
the idea of checkboxes from this site http://www.iopath.com/horseshow.htm
where they show a select multiples option as a screen shot at almost
the bottom of the page.

I am somewhat experienced with databases, SQL, VBA and other computer
applications so any assistance you all can provide is much
appreciated. And, if I am missing a detail or not explaining
something well please let me know.

Thanks
Nicole
Click to expand...
 
H

hor vannara

OK - here goes my explanation of what I am trying to accomplish. I am
creating a database for a horse show to first collect entries, second
store the placings as the announcers complete the class and third
generate reports with class winners and an eventual high point award.
The basic premise is that there will be a show each week and there are
several members who may compete in several different events at each
show. Each membership may have several family members participating.
My table structure is as follows:

Members
-------------------
Membership Number (Primary Key)
FName
LName
Address
etc

HorseRiderCombo
---------------------------------
Horse&Rider (Primary Key)
Membership Number
Age Division

Class Descriptions
---------------------------------
Class Number (Primary Key)
Class Description

Show Results
----------------------------------
Class Date
Class Number
Horse & Rider
Placing
Notes

I currently have a form that utilizes a combo box to select the
desired horse and rider. It then populates the age division and
membership number just for verification purposes. Then I have a
subform based off of the show results form. This subform is auto
populating the date and Horse & Rider info. Currently I have it set
up as a combo box for the class number so that a user can select a
class number from the drop down list based off of the class
description table, enter any relevant note for that class (ie rider is
changing horses or whatever comes up) and go on to the next row in the
datasheet view and select the next class number from the list. This
works perfectly for what I need to do.

However, the peole I am designing the interface for feel that the drop
down box is too time consuming and confusing (not experienced computer
people at all). I am wondering if there is a way to create the same
type of scenario using checkboxes or something similiar that will make
it easier for them to just check the classes the member wants to
participate in, but still maintain a similiar table structure. I got
the idea of checkboxes from this site http://www.iopath.com/horseshow.htm
where they show a select multiples option as a screen shot at almost
the bottom of the page.

I am somewhat experienced with databases, SQL, VBA and other computer
applications so any assistance you all can provide is much
appreciated. And, if I am missing a detail or not explaining
something well please let me know.

Thanks
Nicole
Click to expand...
 
Top