Listbox Help

[MattBaldry]

Hi all,

I am after some help. I am trying to find a way of filtering 2 list boxes, but scratching my head on it.

Listbox 1 will have a list or processes available for the build of a BOM.
Listbox 2 will have a list of the processes that have been selected.

What I am after is a code that will hide the process in listbox 1 once it has been moved to listbox 2.

Both listboxes are based on queries, but I am not sure how to hide the processes in listbox 1 once they are in listbox 2.

Listbox 1	Listbox 2
Process 1	Process 2
Process 2	Process 4
Process 3
Process 4

In the simple example above, I would like Process 2 and Process 4 to not appear in Listbox 1 unless they are remove from the BOM process list displayed in Listbox 2.

The unique field I need to filter in/out will be LabourProcessID

~Matt

 

[Ranman256]

You want an OUTER join query for List1 that says:
show items in tList1 that are NOT in tList2

like:
SELECT tList1.Field, tList2.Field
FROM tList2 RIGHT JOIN tList1 ON tList2.Field = tList1.Field
WHERE ((tList1.Field) Is Null));

 

[MattBaldry]

You want an OUTER join query for List1 that says:
show items in tList1 that are NOT in tList2

like:
SELECT tList1.Field, tList2.Field
FROM tList2 RIGHT JOIN tList1 ON tList2.Field = tList1.Field
WHERE ((tList1.Field) Is Null));

I shall give that a go, thank you for your help.

I'll let you know how it goes

~Matt

 

[MattBaldry]

You want an OUTER join query for List1 that says:
show items in tList1 that are NOT in tList2

like:
SELECT tList1.Field, tList2.Field
FROM tList2 RIGHT JOIN tList1 ON tList2.Field = tList1.Field
WHERE ((tList1.Field) Is Null));

Yes, that worked fine. Thank you for your help.

I just need to populate all the tables now and put it into test.

~Matt

 

[arnelgp]

see this demo.
the listbox is based on those 2 queries.

 

[moke123]

Heres a custom class PickList.

 

[MattBaldry]

Okay,

So I have this working, and it is working well. However, I cannot help but feel it could be more elegant.

I have 10 listboxes which are based on 10 queries with an additional 2 list boxes showing summaries.

As a self taught person who has grown this database over the years, I know there are tricks to learn.

Would any of you VIP's fancy taking a look to see if there is anything I could do more elegant, or if what I have done is fine for the job needed?

~Matt

 

[Minty]

I'll be honest it looks fine to me. It looks very good in fact, and very functional.
You could (and I say could...) make the Available and Selected Listbox pairs a sub form and simply repeat it with different rowsources...

But I suspect that the coding to do that might be more onerous than it's worth.

 

[moke123]

I'm not sure which solution you used, Arnel's or mine.
Its nothing I ever thought about when writing the class but I think it's possible you could use one listbox as the source listbox. You would just need to change the rowsource of the listbox and instantiate the class. See the combobox in my example which is labeled "Change Table" When you initiate the class just point to the destination listbox as the 2nd listbox argument.

 

[MarkK]

If I made that form, each one of these ...

...would be a subform.

 

[MattBaldry]

I'm not sure which solution you used, Arnel's or mine.
Its nothing I ever thought about when writing the class but I think it's possible you could use one listbox as the source listbox. You would just need to change the rowsource of the listbox and instantiate the class. See the combobox in my example which is labeled "Change Table" When you initiate the class just point to the destination listbox as the 2nd listbox argument.

I used your one as I had it working before Arnel posted his solution. As it worked, I left it as is.

~Matt

 

[MattBaldry]

If I made that form, each one of these ...

View attachment 92883

...would be a subform.

Hi Mark,

What would be the benefit in having those as subforms if they are just listboxes based on queries and not based on the form datasource?

I really only use subforms where I want the main form to link to data in the subform, IE tblQuote -- tblQuoteDetail

In the scenario above there are no linked fields, so hadn't even thought of using them as subforms.

If there is a benefit, I am open to changing. None of my coding is very elegant, but it is functional, so any improvements I can make, I am always happy to learn.

~Matt

 

[moke123]

I used your one as I had it working before Arnel posted his solution. As it worked, I left it as is.

~Matt

I meant to include RanMan's solution in the question as his is the most accepted method of excluding selected items.

Bottom line is if it aint broke don't fix it. Your form looks good on appearance and if it does what you want with accurate results, leave it.
Chances are, that down the road when you learn something new, you'll revisit past procedures and improve them anyway.

 

[MattBaldry]

I meant to include RanMan's solution in the question as his is the most accepted method of excluding selected items.

Bottom line is if it aint broke don't fix it. Your form looks good on appearance and if it does what you want with accurate results, leave it.
Chances are, that down the road when you learn something new, you'll revisit past procedures and improve them anyway.

Thanks Moke,

I do like the layout template I use on my forms.

Above is one I am currently working on for a QMS system. This is the returns section.

~Matt

 

[MarkK]

What would be the benefit in having those as subforms if they are just listboxes based on queries and not based on the form datasource?

One subform, the same subform. Five separate instances of the same subform, loaded and configured with data by the main form after it opens. Benefits are encapsulation, less repetition, simplicty, speed.

 

[MajP]

@tfaisal,
STOP HIJACKING THREADS.