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:
Code:
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