Command button to filter list box

[Londa Sue]

Hello,

I have a list box that I would like to have "filtered" using various command
buttons. The list box by default shows all records. When the command button
for "Checklists" is clicked, I'd like the list box to display only checklist
records. Below is some information on what I have done thus far.

Listbox name = DocList

Checklist button code:
DocList.RowSource = "SELECT tbl_main.ID, tbl_main.DocumentID,
tbl_main.DocumentTitle, tbl_main.ReleaseDate, tbl_main.Version,
tbl_main.Valid FROM tbl_main Where (((tbl_main.DocumentID) LIKE 'CHK'))ORDER
BY tbl_main.DocumentID"

I decided to reuse the statement from another database, changing the
parameters/fields as needed. I do not, however, get the results I'm looking
for. When the button is clicked, the list box does not return any records at
all. It is blank/empty of records.

Any help is appreciated.

Londa Sue

 

[Steve Sanford]

Hi Londa Sue,

It looks like you are missing a space before the 'Order By' clause.


One way to test your SQL statements is to create a new query (no need to
save it), cancel the add table dialog, switch to SQL view and paste the
statement (without quotes) you want to check.

When you execute the query, do you get the expected results?
If not, remove the Where and Order By clauses; execute the query. If it
displays records, then add the criteria . Execute the query. What are the
results? If you get the expected results, add the sorting.

Switch to SQL view. Copy the SQL and paste it in the button event (don't
forget to add the quotes). Close the query without saving it.

HTH

 

[Londa Sue]

Steve,

I carried out your instructions, and ran into a snag. I got everything to
work, but when I paste the SQL statement into the code, I get an error
(outside expression). Here is what the statement looks like:

DocList.RowSource = "SELECT tbl_main.ID, tbl_main.DocumentID,
tbl_main.DocumentTitle, tbl_main.Version, tbl_main.ReleaseDate,
tbl_main.Valid FROM tbl_main WHERE (((tbl_main.DocumentID) Like 'FR')) ORDER
BY tbl_main.DocumentID"

Thanks for the info on SQL, too. I built the original database from which I
am reusing the code, and am unsure about how I got there (I had some help
then, too).

YIDH,

Londa Sue

 

[Londa Sue]

The problem is solved. I redid everything, and I think it was the sequence
in which I was doing the steps. Now, it works, but I've another question.
Over time, the records for the list box will be entered based on what is
needed according to document type. I'd like the list box, however, to always
order documents by the Document ID and not the ID. (Doc ID will show things
in grouped order, whereas, ID will not). How can I do this without having to
do the list box again?

Thanks,

Londa Sue