How to filter combo box list by itself?

[binbin]

Hi There,

I have these table:

tblInvoice

tblInvoiceDetail (Related to tblInvoice)

tblProduct (chosen through a combo box on a subform based on a query.)

I have a combo box where you can select products by its ProductID.

However, I would like that item, once selected and added to tblInvoiceDetail, to be removed from the cmbProductID list but would re-appear on the list once a new Invoice has been made. The purpose being that to prevent duplicate items being added to the same tblInvoiceDetail.

I tried having to query the two tables, tblProduct using jointype #2 to tblInvoiceDetail and using the "Is Null" criteria on the field InvoiceID. This works at first, but the list does not re-include those items in a new invoice since their InvoiceID have already been filled (not null anymore) and therefore would not re-appear on the new Invoice.

I tried to use the value of the cmbProductID itself for the criteria but the one I did in the cmbProductID query does not seem to work.

I have included a sample of the database I have made.

 

[MStef]

Her it is (attachment, zip)
Open form "frmInvoice" and see.

 

[Severin]

Have a look at this solution!~)

In short you use outerjoin is null functionality to filter data then just remember to requery on current on the main form, save/refresh/requery on afterupdate on the subform, requery after delete confirmed and check for mainform id when creating a new record in the subform...

I updated the changes in the attachment...

 

[binbin]

Her it is (attachment, zip)
Open form "frmInvoice" and see.

Thank you Mstef, it did what my final intention was.

But in addition to that, would it be possible to filter out the combo box "cmbProduct" list to not include the items that were already chosen in the drop down list?

Example....

User will make a new invoice naming it "Invoice 001." In the Invoice detail, the user will use the combo box "cmbProductID" to select the items to include in the Invoice 001 detail. User selects "Product A" from the list. The user may continue to add products from the list in the combo box "cmbProductID," however, "Product A" will not appear in the list anymore, since it was already added in "Invoice 001" detail. But when the user makes another invoice like "Invoice 002," all the products will appear in the list, also re-listing "Product A."

Thank you again for the help.

 

[binbin]

Have a look at this solution!~)

In short you use outerjoin is null functionality to filter data then just remember to requery on current on the main form, save/refresh/requery on afterupdate on the subform, requery after delete confirmed and check for mainform id when creating a new record in the subform...

I updated the changes in the attachment...

You caught me in mid reply I'm having a look right now. Thanks!

 

[binbin]

Thanks Severin for the solution! I would have never figured that out. The beauty of this method is the user would not keep adding an item that has already been added because he would not be able to see that item in the list anymore. Nice! Thanks Everyone!

 

[Severin]

Great!~)

..just remember to change the 'limit to list' property on cmbProductID to true..

 

[Severin]

...and you probably want 'Allow Value List Edits' to be false...

 

[binbin]

Noted. Thanks

 

[MStef]

Hello binbin!
As you can see, there are more ways for do this.
you can chose what you want.

 

[binbin]

Hello binbin!
As you can see, there are more ways for do this.
you can chose what you want.

How right you are... I was actually trying out if I could use the count function to hide rows of duplicates, but became too complicated for me. Very grateful for the inputs.