I have two cascading combo boxes in a form in which I want new entries to be stored in the table the other selections came from. My first box is categories which has: food, manipulative, sensory, cage furniture change out, and training, from the cateogory field in my table. My second combo box is based upon the first selection, which is description. So say I have a new food I want to store in my table. If I select Food, then type a new description, the new description is stored in the table, by the category is blank because food is not a new category. I have placed the following code in both the after updates.
Private Sub Category_AfterUpdate()
On Error Resume Next
Description.RowSource = "Select qry_enrichment_all1.Comments " & _
"FROM qry_enrichment_all1 " & _
"WHERE qry_enrichment_all1.Category = '" & Category.Value & "' " & _
"ORDER BY qry_enrichment_all1.Comments;"
Dim i As Integer
i = DCount("*", "Table1", "[Category]='" & Category & "'")
If i = 0 Then
CurrentDb.Execute "insert into Table1 ([Category]) select '" & Category & "'"
End If
End Sub
and for the second combo box:
Private Sub Description_AfterUpdate()
Dim i As Integer
i = DCount("*", "Table1", "[Comments]='" & Description & "'")
If i = 0 Then
CurrentDb.Execute "insert into Table1 ([Comments]) select '" & Description & "'"
End If
End Sub
Is there a way to intertwine the two so that even if my category selection isnt new, but my desription is, that it will store both the category and description in the new record so it will appear in the correct combo box the next time? Thanks.