Add new records with Cascading combo boxes

[dgmdvm]

One portion of my db deals with furniture items that are donated from individuals and then are given away to the needy. I have created a series of cascading combo boxes to assist the user in recording the donations and delivery of furniture. The relationships for this part of the db look like this:





I have created a simple form to allow users to add a new piece of furniture to the lists. Each new item has to have a TYPE ( such as bedding, appliance, etc.), CATEGORY ( such as bed frame, mattress, washer, dryer etc) and STYLE ( such as queen, king, electric, gas). The form looks like this:


Each combo box gets its records from the corresponding tables (tblfurnituretype, tblfurniturecat, tblfurnitureStyle). The user can't add a new type. There are only 3 types. The category combo is requeried after the user selects a type. The user can create a new category, a new style, OR BOTH. I currently do not have the style combo box requeried after a category is chosen. All three combo boxes are set to "limit to list".

I got the idea for this setup from the files of Ken Sheridan so I want to give him credit here.

My problem is as follows:

If I add a new category but use a style that is already in the tblfurniturestyle table, both the tblfurniturecat and tblfurniturestyle tables update correctly.
If I add a new category AND a new style, the tblfurnitureCat table updates correctly but the tblfurniturestyle table gets the same entry twice. I believe this is occurring because when a new style is entered in the style combobox the Not in list event fires and then the After Update event fires. I only want the Not in List event to fire in this instance.

How to fix this? I don't know. I have tried a variety of things but nothing has worked. I'm probably missing the obvious.

Here is all the code for the form:

Option Compare DatabaseOption Explicit




Private Sub CmboAddCat_AfterUpdate()
Me.CmboAddStyle.Requery
End Sub


Private Sub CmboAddCat_NotInList(NewData As String, response As Integer)
' if a new category is added, the category table will be updated


On Error GoTo Err_CmboAddCat_NotInList_Click
Dim ctrl As Control
Dim strsql As String
Dim strmessage As String
Dim x As Double


x = Val(Me.CmboType)
Set ctrl = Me.ActiveControl
strmessage = "Add " & NewData & " to list?"
strsql = "Insert Into tblFurnitureCat(FrnType,FrnCategory) VALUES(""" & x & """,""" & NewData & """)  "


If Msgbox(strmessage, vbYesNo + vbQuestion) = vbYes Then
    CurrentDb.Execute strsql, dbFailOnError
    response = acDataErrAdded
Else
    response = acDataErrContinue
    ctrl.Undo
End If


Exit_Err_CmboAddCat_NotInList_Click:
  Exit Sub


Err_CmboAddCat_NotInList_Click:
Msgbox Err.Description
Resume Exit_Err_CmboAddCat_NotInList_Click
End Sub


Private Sub CmboAddStyle_AfterUpdate()
' if the value of the style combobox is "in the list", then find the values of the 3 boxes and add them to the style table
On Error GoTo Err_CmboAddStyle_AfterUpdate


Dim strsql As String
Dim x As Double
Dim y As Double
Dim z As String


x = Val(Me!CmboType)
y = Val(Me!CmboAddCat)
z = Me!CmboAddStyle
strsql = "Insert Into tblFurnitureStyle(FrnType,FrnCat,FrnStyle) VALUES( """ & x & """,""" & y & """, """ & z & """)  "


CurrentDb.Execute strsql, dbFailOnError


Exit_Err_CmboAddStyle_AfterUpdate:
  Exit Sub


Err_CmboAddStyle_AfterUpdate:
Msgbox Err.Description
Resume Exit_Err_CmboAddStyle_AfterUpdate
End Sub


Private Sub CmboAddStyle_NotInList(NewData As String, response As Integer)
' if a new style is added, it will update  style table with all three entries
On Error GoTo Err_CmboAddStyle_NotInList


Dim ctrl As Control
Dim strsql As String
Dim strmessage As String
Dim x As Double
Dim y As Double


Set ctrl = Me.ActiveControl


x = Val(Me!CmboType)
y = Val(Me!CmboAddCat)




strmessage = "Add " & NewData & " to list?"
strsql = "Insert Into tblFurnitureStyle(FrnType,FrnCat,FrnStyle) VALUES( """ & x & """,""" & y & """, """ & NewData & """)  "


If Msgbox(strmessage, vbYesNo + vbQuestion) = vbYes Then
    
    CurrentDb.Execute strsql, dbFailOnError
    response = acDataErrAdded
  
Else
    response = acDataErrContinue
    ctrl.Undo
End If
Exit_Err_CmboAddStyle_NotInList:
  Exit Sub


Err_CmboAddStyle_NotInList:
Msgbox Err.Description
Resume Exit_Err_CmboAddStyle_NotInList
End Sub


Private Sub CmboType_AfterUpdate()
' clears the 2nd and 3rd combo boxes after changing the first combo box


On Error GoTo Err_CmboType_AfterUpdate
Me.CmboAddCat = Null
Me.CmboAddStyle = Null
Me.CmboAddCat.Requery
Me.CmboAddStyle.Requery


Exit_CmboType_AfterUpdate:
    Exit Sub


Err_CmboType_AfterUpdate:
    Msgbox Err.Description
    Resume Exit_CmboType_AfterUpdate
End Sub


Private Sub CmdDone_Click()


On Error GoTo Err_cmdDone_click
Me.CmboType.Requery
Me.CmboAddCat.Requery
Me.CmboAddStyle.Requery
' the form will only close if all 3 boxes are null or all three boxes have entries
If IsNull(Me.CmboType) And IsNull(Me.CmboAddStyle) And IsNull(Me.CmboAddCat) Then
    DoCmd.Close
ElseIf IsNull(Me.CmboType) Or IsNull(Me.CmboAddCat) Or IsNull(Me.CmboAddStyle) Then
    Msgbox " You must fill in all three boxes before closing", vbOKOnly, "Missing Entry"
    Exit Sub
Else
    DoCmd.Close
End If
Exit_CmdDone_Click:
    Exit Sub


Err_cmdDone_click:
    Msgbox Err.Description
    Resume Exit_CmdDone_Click
End Sub

[Bulzie]

In your code, put a break point at the top say at the x = Val(Me.CmboType) line and step through "F8" each line of code and it should show you where it is writing the style twice.

[dgmdvm]

I have done that. The code runs the CmboAddstyle_NotInList event and adds the fields to the tblfurniturestyle table. Then the code moves to the CmboAddStyle_AfterUpdate code and adds the fields again. It seems what I need is for the CmboAddStyle AfterUpdate event to only fire when the NotInList event doesn't run.

[isladogs]

Something is confused here I think.
If all 3 combos are set as Limit to list = Yes, how can a user add a new category or style?
Similarly why would you need a NotInList event when the list can't be altered?

[dgmdvm]

I do want the user ( with admin. permissions) to be able to add to the category and style lists. I don't want any user to be able to do so however. Perhaps if I use Afterupdate events only and get rid of the notinlist events? I will give that a try tonight when I get home.

[Bulzie]

Yes think you only need the insert to be in the Not In list code. Also probably not needed but you could add a DLookup to test if that value is already in your table before you write it.

x = DLookup("CustomerID", "tblFurnitureStyle", "FrnStyle = '" & NewData & "'")
If isnull(x) then
"do the insert"

[dgmdvm]

Yes think you only need the insert to be in the Not In list code. Also probably not needed but you could add a DLookup to test if that value is already in your table before you write it.

x = DLookup("CustomerID", "tblFurnitureStyle", "FrnStyle = '" & NewData & "'")
If isnull(x) then
"do the insert"

So I kept the NotInList event for the category, but got rid of it for the style. I did add your lookup code to the style After_Update event so that the user would be told why a duplicate entry wasn't accepted and now it all works.

Thanks for helping me work through that.

[Bulzie]

Glad we could help.