Results 1 to 8 of 8
  1. #1
    dgmdvm is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Mar 2014
    Posts
    76

    Add new records with Cascading combo boxes

    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:



    Click image for larger version. 

Name:	Capture.PNG 
Views:	22 
Size:	26.9 KB 
ID:	40857

    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:
    Click image for larger version. 

Name:	Capture2.PNG 
Views:	21 
Size:	12.0 KB 
ID:	40858

    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

  2. #2
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,315
    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.

  3. #3
    dgmdvm is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Mar 2014
    Posts
    76
    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.

  4. #4
    isladogs's Avatar
    isladogs is offline Very idle programmer
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    3,987
    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?
    Colin (Mendip Data Systems) : Website, email
    If this has helped, please click the star button and leave a comment

  5. #5
    dgmdvm is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Mar 2014
    Posts
    76
    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.

  6. #6
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,315
    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"

  7. #7
    dgmdvm is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Mar 2014
    Posts
    76
    Quote Originally Posted by Bulzie View Post
    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.

  8. #8
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,315
    Glad we could help.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 5
    Last Post: 02-01-2018, 04:53 PM
  2. Cascading Combo Boxes
    By raychow22 in forum Forms
    Replies: 15
    Last Post: 06-09-2017, 10:39 AM
  3. Cascading Combo Boxes
    By Heathey94 in forum Queries
    Replies: 26
    Last Post: 09-05-2016, 03:02 PM
  4. Replies: 5
    Last Post: 10-07-2014, 11:53 AM
  5. Replies: 12
    Last Post: 02-01-2013, 02:25 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums