Listbox filtering Help

[ichi]

I have a form within my database which is the data input for an ordering system for an electrical companies housing section. The user is able to choose a site in a combo box which then allows the user to see the relevant houses for that site in a second combobox. The details of the houses are stored in "tblPlotref" within this table there are currently over a thousand unique houses. Each house is a particular type and there are around 200 different house types, these are kept in "tblHouseref". In "tblPlotref" each house has a number in the Houseref column which corresponds with the housetype number in tblHouseref.

Once the House has been chosen in the form, it triggers a number of other actions on the form but the one I'm interested in is a listbox on the form. The data for the listbox is a query which shows the relevant 'kits' for that housetype (each housetype has a '1st Fix Kit' and a '2nd Fix Kit') the user selects which 'kit' is being ordered and that is added to a table called "tblDespatches" in a column called "FixRef", it adds a number which refers to details in a different table.

What I want the form to do is once the user has chosen the house it checks "tblDespatches" to see if that house has already had a 'kit' ordered and prevent it from being ordered again.

I hope that made sense, unfortunatly the db is quite complicated its tricky to give a full explanation of it without writing an essay!

 

[adam_fleck]

subQuery?

This might be a silly suggestion, but you could try restricting the kit list so that only kits that haven't been ordered for the current house will show up.

One way of doing this would be set the RowSource for LstKits to something like this -

lstKits.RowSource = "SELECT kit_ID, kit_Name from tblKits" & _
                    " WHERE house_ref_ID = " & Me.house_ref_ID & _
                    " AND kit_ID NOT IN" & _
                    " (SELECT kit_ID FROM tblDispatches" & _
                    " WHERE house_ID =" & Me.house_ID & ");"

You could put this code on the event that triggers the changes that happen when a house is selected.

The query the code constructs involves a subQuery. These can run really slowly on Access, but seem to be fantastically fast on SQL server.

 

[ichi]

Excellent that worked perfectly, just what I needed Thanks