Link to home
Start Free TrialLog in
Avatar of Fred Webb
Fred WebbFlag for United States of America

asked on

Remove Item from combobox after being selected.

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

Avatar of Mark Edwards
Mark Edwards
Flag of United States of America image

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.
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.
Sorry, not sure what fields to use, but hopefully you get the idea.
Avatar of Fred Webb

ASKER

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

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.
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.
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.
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.
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.

User generated image
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.
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.
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.
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.
I can not seem to get this to work
ASKER CERTIFIED SOLUTION
Avatar of Mark Edwards
Mark Edwards
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Sorry i have not responded i have been out of town. Thanks Mark I will try that.
Thanks Mark for your assistance.