none
Error when synchronise several combo Boxes RRS feed

  • Question

  • I have a form with subform that we use for Inventory Transaction. The subform has three combo box:

    -cboInventoryCategoryID

    -cboInventoryGroupID

    -cboInventoryID

    because we have a wide range of inventory items, for convenience the operator first select the Category, then Group and the last item is Inventory. The first combo and second work good and when you select the first combo box the second filter based the first one, but when I select the third combo an error message appears and say:

    "The current field must match the join key '?' in the table that....

    I tried to solve the problem by adding this code to second combo box after update event:

    DoCmd.RunCommand acCmdSaveRecord

    it works but it is not a good solution, because it save the record before entering all items in the row and triggers the Afterupdate event of the subform that make trouble for me.I have some code that don't want to be executed before entering all data in the row.

    How can I solve this problem?

    Option Compare Database
    
    Private Sub cboInventoryCategoryID_AfterUpdate()
        'Bind the Group name to category Id combobox and filter the Group based on Category
        If IsNull(Me.InventoryCategoryID) Then
            Me.cboInventoryGroupID.RowSource = ""
        Else
            Me.cboInventoryGroupID.RowSource = _
                "SELECT InventoryGroupID,InventoryGroupName " & _
                "FROM tblInventoryGroup " & _
                "WHERE InventoryCategoryID = " & Me.cboInventoryCategoryID & " " & _
                "ORDER BY InventoryGroupName"
           
            Me.cboInventoryGroupID.Requery
                
        End If
                
        
            
    End Sub
    
    Private Sub cboInventoryGroupID_AfterUpdate()
        'bind the Inventory name to Inventory group for filtering the list of items
        If IsNull(Me.cboInventoryGroupID) Then
            Me.cboInventoryID.RowSource = ""
        Else
            Me.cboInventoryID.RowSource = "Select InventoryID,InventoryName " _
            & "FROM tblInventory " _
            & "WHERE InventoryGroupID= " & Me.cboInventoryGroupID & " " & _
            "ORDER BY InventoryName"
            
             Me.cboInventoryGroupID.Requery
             Me.cboInventoryID.Requery
        
            'DoCmd.RunCommand acCmdSaveRecord
        End If
    End Sub



    Regards,




    • Edited by kvaziri Wednesday, February 24, 2016 9:49 PM
    Wednesday, February 24, 2016 9:29 PM

Answers

  • Here is a suggestion to consider.  Definitely trial this in a copy.

    Delete your table relationship join lines.  I know this seems radical to some database development - but it won't break any of your existing design.  It does mean that going forward when you make a query or form/sub form these join relationships will need to be manually entered.

    Once free of the relationship join; then your combo boxes can be based upon queries that use criteria of the other combo boxes' bound value at the form level -  without needing the write to the table.

    There may be an alternative to this solution that others will post.  But based on what I see I think this idea might work.

    Wednesday, February 24, 2016 10:57 PM