Remove Item from combobox after being selected.

skull52
skull52 used Ask the Experts™
on
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

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Mark EdwardsChief Technology Officer

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

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

Commented:
Sorry, not sure what fields to use, but hopefully you get the idea.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

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

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

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

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

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

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

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

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
Commented:
o.k let's try this Access SQL:

SELECT * FROM (
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]))
) AS [qryList] LEFT JOIN (
SELECT DISTINCT [ATTRIB_ID] FROM [item_attrib_attribvl]
WHERE (ITEMNMBR = '" & Me.txtItem & "')) AS [qrySelected] ON
qryList.[ATTRIB_ID]=qrySelected.[ATTRIB_ID]
WHERE qrySelected.[ATTRIB_ID] Is Null
ORDER BY qryList.ATTRIBUTE

You are going to need to replace in the "WHERE (ITEMNMBR = '" & Me.txtItem & "')) " with the control that provides the ITEMNMBR value in the format of [Forms]![NameOfFormHere]![NameofControlHere])) because you obviously have to filter the records in the "item_attrib_attribvl" table for ITEMNMBR as evidenced by your initial post, but nobody knows what your using for that control.
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.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial