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