list box filter dependant on another list box... 2

[JaredTaylor74]

I have 2 list boxes on my form. (lstAvailableSlides & lstAssignedSlides)
lstAvailableSlides are pulled from tblHardware and lstAssignedSlides are pulled from tblToolingSets

i have an "ADD" button and a "REMOVE" button that adds any selected slide from lstAvailableSlides to lstAssignedSlides, or removes them from lstAssignedSlides... no problems there.

The problem i have is that when i "add" a slide to lstAssignedSlides i don't want it in the available list anymore... so any slide that has been assigned should not show up in the available. but if it is removed from lstAssignedSlides it should show up as available in lstAvailableSlides.

i can't remove them from the hardware table, because that data is used in many other places. i just need to "filter" them.

using an unbound form.. here is my code...

Dim strAvailableSlides, sqlAvailableSlides, sqlAssignedSlides As String

strAvailableSlides = Me.lstAssignedSlides.Value

sqlAvailableSlides = "SELECT ToolNumber FROM tblHardware WHERE (ToolType = 'SLIDE' AND Machine = 'ARBURG' AND ToolNumber <> """ & strAvailableSlides & """) ORDER BY [ToolNumber];"
   
sqlAssignedSlides = "SELECT MoldSetTooling FROM tblToolingSets WHERE (MoldSet = """ & cboMoldBase & """ AND ToolingType = 'SLIDE') ORDER BY [Tooling];"

Me.lstAssignedSlides.RowSource = sqlAssignedSlides
Me.lstAvailableSlides.RowSource = sqlAvailableSlides

i've tried a variety of different things to have the list of available slides filter correctly... haven't been able to find anything that works yet...

Thanks in advance for any help that points me in the right direction...

Jared

 

[Trendsetter]

If you link the queries for the 2 listboxes by an inner join, any ID appearing in the righthand listbox query can be excluded from the righthand listbox query.

Frank J Hill
FHS Services Ltd.
frank@fhsservices.co.uk

 

[JaredTaylor74]

can i link my sqlAssignedSlides and sqlAvailableSlides like i would a query? if so how? i don't want to build "real" queries because there are too many variables that are selected on the form. so i have my queries in code like you see them.

can i do what i want in code or do i have to build a "real" query?

Thanks,
Jared

 

[Trendsetter]

In the sqlAssignedlides you could play with the SQL
NOT In sqlAvailableSlides

This would ensure that any item that appeared in the assigned list would not be in the available list

Frank J Hill
FHS Services Ltd.
frank@fhsservices.co.uk

 

[JaredTaylor74]

excuse my ignorance, but i'm not aware of a NOT IN statement.. is there one? where is it used?

here is the code i am using now... which works for what it needs to do, but i still want the available to not show the assigned.

sqlAvailableSlides = "SELECT MoldVisualToolNumber FROM tblListMoldingHardware WHERE (MoldToolType = 'SLIDE' AND MoldMachineCompatability = """ & strMoldType & """ AND MoldToolStatus = 'GRN' AND MoldPartStatus = 'A') ORDER BY [MoldVisualToolNumber];"

sqlAssignedSlides = "SELECT MoldSetTooling FROM tblListMoldingSets WHERE (MoldSetName= """ & strMoldSetName & """ AND MoldSetMold = """ & cboMoldBase & """ AND MoldSetToolingType = 'SLIDE' AND MoldSetQualificationStatus = 'A') ORDER BY [MoldSetTooling];"

Me.lstAvailableSlides.RowSource = sqlAvailableSlides
Me.lstAssignedSlides.RowSource = sqlAssignedSlides

again, i can add to or remove from the assigned list, because i just add a record or delete a record, but the available comes from a table that many other objects are using, therefore can't delete from it.

Thanks again for your help,
Jared

 

[Trendsetter]

The IN statement is allowed in SQL even though I don't use it a lot.
2 suggestions:
1 - you only need to apply all of your parameters in the available SQL as -
sqlAvailableSlides = "SELECT * FROM tblListMoldingSets etc.. etc.. WHERE NOT IN sqlAssignedSlides

2 - sqlAssignedSlides = SELECT * FROM tblListMoldingSets WHERE NOT IN SELECT sqlAvailableSlides

Frank J Hill
FHS Services Ltd.
frank@fhsservices.co.uk

 

[PHV]

And what about this ?
sqlAvailableSlides = "SELECT MoldVisualToolNumber FROM tblListMoldingHardware WHERE MoldToolType='SLIDE' AND MoldMachineCompatability='" & strMoldType & "' AND MoldToolStatus='GRN' AND MoldPartStatus='A'"
sqlAvailableSlides2 = ") ORDER BY MoldVisualToolNumber"

sqlAssignedSlides = "SELECT MoldSetTooling FROM tblListMoldingSets WHERE MoldSetName= '" & strMoldSetName & "' AND MoldSetMold = '" & cboMoldBase & "' AND MoldSetToolingType='SLIDE' AND MoldSetQualificationStatus ='A'"
sqlAssignedSlides2 = ") ORDER BY MoldSetTooling"

Me.lstAvailableSlides.RowSource = sqlAvailableSlides & " AND MoldVisualToolNumber NOT IN (" & sqlAssignedSlides & sqlAvailableSlides2
Me.lstAssignedSlides.RowSource = sqlAssignedSlides & " AND MoldSetTooling NOT IN (" & sqlAvailableSlides & sqlAssignedSlides2

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[JaredTaylor74]

Thank you very much PHV, sorry it's taken so long to get back to this... too many other projects.

I did use the code you provided me and it works perfectly.

trendsetter,
thank you for the suggestions on how to use the NOT IN statement in sql. will come in handy very soon i think.

Thanks again to both of you for taking time out of your day to help the un-educated.


Jared