Combobox

[hinchi1]

I am working on a database which is used to log buildings and rooms our faculty occupies across the various campuses. One of the forms contains a listbox which is populated by a query. there is also a textbox which is used to enter text and the list is filtered as you type. This works fine for letters and to some degree with numbers, but it is not accurate enough. One of the main reasons of the database is to also log what square footage we occupy, so i was thinking of adding a combobox to my form and populate the list with several options like. 1-50,51-100,101-150, then when the user selects an option from the dropdown list the listbox is filtered to show the rooms which are 1 to 50 square metres or 51 to 100 square metres and so on. My problem is i am unsure how to code this and would i need to create several queries for all of the options in the combobox? i don't know how to dynamically change the rowsource of the listbox. Or is there a way to use the keyword textbox to be more accurate when searching by square footage? Thanks in advance for your feedback.

[alansidman]

Have you considered cascading combo boxes, where the first one would list 1 to 50 and 51 to 100. Based upon that selection, then the rooms would be available in your second combo box.

Look here for an explanation on how to do this: http://www.fontstuff.com/access/acctut10pfv.htm

[hinchi1]

Hi

Thanks for your response. I have implemented cascading comboboxes in other areas of my database but i don't think this option will work here. The idea is to select a range from the combobox which then the listbox is filtered, i can then either click on one of the items in the list to see more detail or i can select all of the items and output to PDF for viewing.

[davegri]

i don't know how to dynamically change the rowsource of the listbox

Just set the listbox rowsource to the updated query string and requery it.

[hinchi1]

Thanks for this, do you have any examples of code showing the process? I was hoping to use a case statement but I don't think that will work. Do you think I need separate queries for each option in the combo box?

[davegri]

Listboxes.zip
Here's an example of a couple of listboxes being dynamically updated.
In your case, SELECT CASE should work fine. You would have to assign a new query string to the listbox rowsource for each SELECT.
The select case would be in the after_update event of the combobox.

[hinchi1]

Thanks, will give it a try later. Just unsure how to put data range in comboboxes to filter to the select query. Will try later and thanks for your feedback. Much appreciated.

[June7]

The combobox range list can be based on a table or typed into the RowSource property.

The combobox could be multi-column. Users see the range column but the beginning and ending values are in separate columns (can be hidden). Reference those columns to build SQL statement. Column index begins with 0 so column 1 is index 0.

strSQL = SELECT * FROM sometable WHERE [Area] BETWEEN " & Me.cbxArea.Column(1) & " AND " & Me.cbxArea.Column(2)

[davegri]

SquareMeter.zip
Here's a better example, with a combobox that allows you to select the area range (Low-High), and populates the listbox with the records that match.

[hinchi1]

Thanks for all your suggestions. I will work through these today and let you know later if i have managed to solve my problem. Once again thanks for all your support.

[hinchi1]

Hi Davegri

I am using your suggestion and it does filter the squarefootage value as selected, but the listbox only displays the squarefootage value and not the room name, etc as per my form screenshot in this thread. What do i need to change in the afterupdate code to display the fields as in the screenshot. Also, you can see i have a reset search button which no longer works in conjunction with the combobox. What code needs to go in there to make it reset the list again? As always thanks for your support and feedback.

[davegri]

it does filter the squarefootage value as selected, but the listbox only displays the squarefootage value and not the room name

The example was meant to be an example, not production code. After all, I have no information about your table structures or field names.
There's a lot of confusion on my part about the form. Why do you have 2 searches, one with a combobox and another with a textbox? Why 2 listboxes? What do you mean by "reset the list"?
Perhaps you could post the db here and we could communicate with common knowledge. If not the db, at least the SQL of the combobox and its original after_update code.

[hinchi1]

Thanks for your reply. The textbox is used to dynamically search the listbox. There are two listbox, the top one is to search for information on individual rooms. The bottom listbox is multiselect and is used for printing out full or filtered room lists. If I use the textbox to filter the records the reset button clears the textbox filter. However, if I use the comboboxes to filter the records the reset button does not work, is, clear the filter from the comboboxes selection. Ideally I would love to post the dB but there is a fair bit of sensitive data in the tables. This is my first dB in 15 years so still work in progress. When the form loads the listbox get their data from a query. So when I select an option from the comboboxes the resource of the table changes. I just need to press the reset button to change the listbox rowsource back to the original query when the form first loads. Or ideally have code behind the textbox to do exactly what the comboboxes does. Thanks again anyway.

[hinchi1]

I will post SQL of the query where the listbox gets its data on form load later. Thanks again

[davegri]

As for sensitive data, you could post a database with just the working form and the tables feeding that form. The form doesn't seem to show anything sensitive.