Select from Table using Yes/No Field

[iamnu]

I'm working on a database to enter recipes. Each recipe will have a
number of categories that can be assigned to it. For example, a Sweet
and Sour Chicken recipe might be assigned the Categories "Chinese",
"Dinner", "Chicken", etc.

The Categories will be in a Category table. When I want to "select"
all the Categories that will be assigned to a recipe, I'd like to do
so by clicking on all the Yes/No fields which represent the Categories
assigned to this recipe from the Category table.

I assume I link the Recipe and Category tables in some fashion, but I
don't know how to "convert" the Category table to a yes/no field.

Can someone suggest a method for accomplishing my task?

 

[tina]

so one recipe may fall into many categories, and one category may contain
many recipes. that's a many-to-many relationship. in Access you model that
relationship with a "join" or "linking" table, as

tblRecipes
RecipeID (primary key)
RecipeName
<other fields that describe a recipe, but nothing about categories.>

tblCategories
CategoryID (pk)
CategoryName
<perhaps other fields that describe a category, but nothing about recipes.>

tblRecipeCategories
ReCatID (pk)
RecipeID (foreign key from tblRecipes)
CategoryID (foreign key from tblCategories)
<note: if you wish, you can omit field ReCatID, and instead use the two
foreign key fields as a combination primary key.>

relationships are:
tblRecipes.RecipeID 1:n tblRecipeCategories.RecipeID
tblCategories.CategoryID 1:n tblRecipeCategories.CategoryID

if you're not familiar with relationships, primary and foreign keys, etc,
recommend you STOP working on your database, and read up on relational
design principles before you proceed any further. for more information, see
http://home.att.net/~california.db/tips.html#aTip1.

hth

 

[iamnu]

so one recipe may fall into many categories, and one category may contain
many recipes. that's a many-to-many relationship. in Access you model that
relationship with a "join" or "linking" table, as

tblRecipes
RecipeID (primary key)
RecipeName
<other fields that describe a recipe, but nothing about categories.>

tblCategories
CategoryID (pk)
CategoryName
<perhaps other fields that describe a category, but nothing about recipes..>

tblRecipeCategories
ReCatID (pk)
RecipeID (foreign key from tblRecipes)
CategoryID (foreign key from tblCategories)
<note:  if you wish, you can omit field ReCatID, and instead use the two
foreign key fields as a combination primary key.>

relationships are:
tblRecipes.RecipeID  1:n  tblRecipeCategories.RecipeID
tblCategories.CategoryID  1:n  tblRecipeCategories.CategoryID

if you're not familiar with relationships, primary and foreign keys, etc,
recommend you STOP working on your database, and read up on relational
design principles before you proceed any further. for more information, seehttp://home.att.net/~california.db/tips.html#aTip1.

hth

I have the linkage working now as tina suggested. However, I would
like this Recipe/Category thing to work in a different way.

I want to click a Category button which will open a form to display
ALL the values in the Category table as yes/no fields, so that I can
select all the Categories that pertain to that particular recipe. I
don't know how to do this.

In this same Category form, I would be able to add additional
Categories as necessary. I know how to do this.

How do I display the Category table as a form of yes/no fields?

 

[Douglas J. Steele]

You can't, at least not easily.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



I have the linkage working now as tina suggested. However, I would
like this Recipe/Category thing to work in a different way.

I want to click a Category button which will open a form to display
ALL the values in the Category table as yes/no fields, so that I can
select all the Categories that pertain to that particular recipe. I
don't know how to do this.

In this same Category form, I would be able to add additional
Categories as necessary. I know how to do this.

How do I display the Category table as a form of yes/no fields?

 

[John W. Vinson]

I'm working on a database to enter recipes. Each recipe will have a
number of categories that can be assigned to it. For example, a Sweet
and Sour Chicken recipe might be assigned the Categories "Chinese",
"Dinner", "Chicken", etc.

The Categories will be in a Category table. When I want to "select"
all the Categories that will be assigned to a recipe, I'd like to do
so by clicking on all the Yes/No fields which represent the Categories
assigned to this recipe from the Category table.

I assume I link the Recipe and Category tables in some fashion, but I
don't know how to "convert" the Category table to a yes/no field.

Can someone suggest a method for accomplishing my task?

As noted in the thread, it's not easy to do this with Yes/No fields; but you
can do so with a Multiselect Listbox with the help of some VBA code. This
would let the user see a list of categories, click each one they want to
include, and click a button to load those selections into the many-to-many
resolver table.

Here's some sample code you can adapt:

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
currently
' 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

 

[iamnu]

I'm working on a database to enter recipes.  Each recipe will have a
number of categories that can be assigned to it.  For example, a Sweet
and Sour Chicken recipe might be assigned the Categories "Chinese",
"Dinner", "Chicken", etc.

The Categories will be in a Category table.  When I want to "select"
all the Categories that will be assigned to a recipe, I'd like to do
so by clicking on all the Yes/No fields which represent the Categories
assigned to this recipe from the Category table.

I assume I link the Recipe and Category tables in some fashion, but I
don't know how to "convert" the Category table to a yes/no field.

Can someone suggest a method for accomplishing my task?

As noted in the thread, it's not easy to do this with Yes/No fields; but you
can do so with a Multiselect Listbox with the help of some VBA code. This
would let the user see a list of categories, click each one they want to
include, and click a button to load those selections into the many-to-many
resolver table.

Here's some sample code you can adapt:

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
currently
            ' 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

Thank you, Mr. Vinson.

It's not what I want, but I'll give it a try.