We help IT Professionals succeed at work.

Remove Item from combobox after being selected.

84 Views
Last Modified: 2019-04-29
I have a combo box (cboAttrb) (Attribute) that is filtered by the contents of another combo box (cboSubcat) (Subcategory) with a query. for example if cboSubcat contains "Accessory" then cboAttrib would only display the attributes assigned to that Subcategory  "Color",Style", "Etc". what I would like to have happen is when the Color attribute is selected it disappears from the list, and only Style remains. what I am currently doing on the AfterUpdate Event is if the Attribute has been selected  I do a DCount in the on the item_attrib_attribvl table with the ITEMNMBR and the ATTRIB_ID if it is > 0  I popup a message that the Attribute has already been selected.

Private Sub cboAttrb_AfterUpdate()
    If DCount("*", "item_attrib_attribvl", "(ITEMNMBR = '" & Me.txtItem & "') AND (ATTRIB_ID=  " & Me.cboAttrb & " )") > 0 Then
        MsgBox "You Have Already selected the " & Me.cboAttrb.Column(1) & " Attribute", vbOKOnly + vbCritical, "Attribute Duplication Error"
        bSaveClicked = False
        Me.cboAttrb.SetFocus
        Me.cboAttrb = Null
        Exit Sub
        ' End If
    Else
        bSaveClicked = False
        Me.cboAttribvl.Requery
    End If
End Sub

Open in new window

Comment
Watch Question

Mark EdwardsChief Technology Officer
CERTIFIED EXPERT

Commented:
You don't say how you are populating the rowsource of your cboAttrb combobox.  Any solution will depend on the method you are using to supply the list.
Mark EdwardsChief Technology Officer
CERTIFIED EXPERT

Commented:
You seem to have a table where that attribute is "tagged" if it has been chosen.
If the rowsource is a query, you can add this table on a left join with ItemNmbr and Attrib_ID, and just select those attributes where:
   WHERE [item_attrib_attribvl].[Attrib_ID] Is Null
Have the combobox requery when it is clicked or gets the focus.  Only those items which haven't been tagged in the item_attrib_attribvl table will remain in the droplist.
Mark EdwardsChief Technology Officer
CERTIFIED EXPERT

Commented:
Sorry, not sure what fields to use, but hopefully you get the idea.
skull52IT director

Author

Commented:
Opps, Sorry about that forgot to include that it is based on a query

SELECT Attribute.ATTRIB_ID, Attribute.ATTRIBUTE, Subcategory.SUBCAT_ID
FROM Subcategory INNER JOIN (SUBCAT_ATTRIB INNER JOIN Attribute ON SUBCAT_ATTRIB.ATTRIB_ID = Attribute.ATTRIB_ID) ON Subcategory.SUBCAT_ID = SUBCAT_ATTRIB.SUBCAT_ID
WHERE (((Subcategory.SUBCAT_ID)=[Forms]![frmEnterItems]![cboSubcat]))
ORDER BY Attribute.ATTRIBUTE;

Open in new window

Mark EdwardsChief Technology Officer
CERTIFIED EXPERT

Commented:
Also, not sure what your table fields are or what specific fields you are using to signal a selected attribute, etc.  so I can only give general hints as to what to do.
Mark EdwardsChief Technology Officer
CERTIFIED EXPERT

Commented:
o.k.  saw your droplist rowsource query.  Looks like adding a left join on the table item_attrib_attribvl (which seems to have the selected attributes) and filtering:
WHERE [item_attrib_attribvl].[Attrib_ID] Is Null
to filter out any attribute that has a record in the "item_attrib_attribvl" table.
Mark EdwardsChief Technology Officer
CERTIFIED EXPERT

Commented:
You'll probably get the best droplist update results if you add a
cboAttrb.Requery
line of code at the end of your cboAttrb_Afterupdate() event.
Mark EdwardsChief Technology Officer
CERTIFIED EXPERT

Commented:
I see that ItemNmbr is not a field in your droplist query, so you'll need to filter the [item_attrib_attribvl] table in your query for the selected ItemNmbr.
skull52IT director

Author

Commented:
Mark,
the query joins on 3 tables SUBCAT_ATTRIB which has the Attribute and Subcategory ID's the Attribute table which has the Attribute value and ID and the Subcategory table which has the Subcategory value and ID they are joined to the the SUBCAT_ATTRIB table on their ID's and and the Attribute is filter by the Subcategory that is assigned to it by looking at the cboSubcat combobox Value.

Attribute to Subcategory
Mark EdwardsChief Technology Officer
CERTIFIED EXPERT

Commented:
I see in your original post that you are filtering the "item_attrib_attribvl" table in your DCOUNT() function on ItemNmbr and Attrib_ID.
"Attrib_ID" is in your rowsource query, but not "ItemNmbr".  I don't know where ItemNmbr is coming from, but the first thing you have to do is filter your ""item_attrib_attribvl" table for "ItemNmbr", otherwise you won't know what attributes in that table belong to the active ItemNmbr.

It looks like the SUBCAT_ATTRIB table is your "base" table on the query, so create a LEFT JOIN on a subquery
(SELECT item_attrib_attribvl.* FROM [item_attrib_attribvl] WHERE [ItemNmbr]= <put the source for this data here>) AS qry
Link the SUBCAT_ATTRIB to the subquery on Attrib_ID and add the filter
[qry].[Attrib_ID] Is Null
to your other criteria.
Mark EdwardsChief Technology Officer
CERTIFIED EXPERT

Commented:
Basically, what you are doing is creating an "unmatched" query, where you are linking the attributes in the droplist to the attributes in the selected attribute list and looking for those attributes in the droplist that AREN'T in the selected attributes list.
skull52IT director

Author

Commented:
Mark,
I got the subquery but what is the  WHERE [ItemNmbr]= <put the source for this data here>)  sorry if I am Missing something.
This looks like a derived table which I don't believe you can do in Access or I don't know how to do it. I could do a Stacked Query and Join it to the rowsource query, but still not sure what to put in the where clause, I work more in MSSQL than Access.
Mark EdwardsChief Technology Officer
CERTIFIED EXPERT

Commented:
What "<put the source for this data here> " means is put the source for the value in your query.  For example, in one of your comments you have:

WHERE (((Subcategory.SUBCAT_ID)=[Forms]![frmEnterItems]![cboSubcat]))

the "[Forms]![frmEnterItems]![cboSubcat]" is an example of what I'm talking about.  You didn't include the source for ItemNmbr in any of your posts.
skull52IT director

Author

Commented:
I can not seem to get this to work
Chief Technology Officer
CERTIFIED EXPERT
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION
skull52IT director

Author

Commented:
Sorry i have not responded i have been out of town. Thanks Mark I will try that.
skull52IT director

Author

Commented:
Thanks Mark for your assistance.

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions