listbox

[kingsgambit]

I have build a search form, the user types is a file next and the results are shown in a listbox, the listbox is attached to a query.
I want to put a button on the form so they can search again, the button would clear the listbox of the previous results.
What would be the vb code for this?

 

[boblarson]

to clear the listbox of everything, you can use:

lstMyListBoxName.Rowsource = ""
lstMyListBoxName.Requery


And that should do it for you.

 

[kingsgambit]

I tried that but after I cleared the listbox and tried searching again the form returns nothing whatever you try and search for

 

[boblarson]

That is because you have to set the rowsource on your search. Just having it assigned to the control will not work if you want to clear it like you had requested.

What you should do is to code behind your search button to set the rowsource to your query and then requery. Then you can use that and the code that I gave you to be able to make it all work.

I use that all the time.

 

[kingsgambit]

I am not sure how you put the code behind the search button to run the query and point the result to the list box. My search button basically refreshes the listbox each time the user enters some text intoa textbox.
Can you help with this

 

[boblarson]

Sure, I have found the best way to do it is to do something like this:

1. Take your query that you would normally assign to the rowsource of the listbox and open it in SQL mode. Then, copy the SQL code and then go into the code window behind your button.

2. In the code window behind your search button, you would want to paste the SQL code (if you copy and paste from the SQL window it will be several lines long, so make sure to remove the carriage returns and have the whole thing enclosed in quotation marks like my example below), something like:

lstMyListBoxName.Rowsource = "Select * From tblNames;"
lstMyListBoxName.Requery

replacing lstMyListBoxName with your actual listbox name and the short SQL statement example I have with your real SQL. Also, if you have any quotation marks in your SQL code, change them to single quotes so that it will work properly.

Give that a try and see if that works for you.

 

[kingsgambit]

Thanks alot, I did not think of that it works great.
Just one thing is there a way to keep the column headings visible
I put Me.search.ColumnHeads = Visible but they disappeared

 

[boblarson]

Add this before your requery line:

lstMyListBox.ColumnHeads = True

 

[kingsgambit]

I tried that but they disappeared, what I meant was when the form opens the column heads appear in the listbox before a search is done. I tried lstMyListBox.ColumnHeads = True in the Onload part of the form but nothing

 

[boblarson]

Make sure that the initial column heads setting for the listbox in the design view of the form is set to YES and not NO. That could be your problem. I have an application with it set to yes and I don't even need to set it using code.

 

[kingsgambit]

When a search is run the Column heads appear, I assume that because there is no rowsource attached the listbox, it can not produce the query headings, I could put a rowsource in the Onload of the form so the query would run but make sure the result is nothing.
What do you think