Populating a List box

[Mike-palmer]

I’ve been coding up Access databases for my own personal needs for some time but I get confused easily around queries and I wondered if someone could suggest the best way to accomplish the following:

I have a large list box that contains a list of DVD movies I own Title & year). I would like the list to show the movies that meet certain parameters such as I have not yet watched them or genre.

Currently the list box is populated using a query that just lists the fields I need to be shown and sorts them into appropriate order. When I try to add more than one reference to check boxes on a form to attempt to select a sub-set of the complete list I run into problems.

I have a feeling that I should be creating the query using VB based on the selection check boxes on the form but I’m not sure how to do that.

Thanks for your help.

 

[Fizzio]

You are going in the right direction, Query parameters are easier (and faster) than dynamically producing SQL. How have you tried to set your query parameters thet supplies the listbox so far? ie, what criteria have you entered into the criteria boxes on the QBE grid (or the SQL statement if you are comfortable obtaining this)

 

[Mike-palmer]

Hi Fizzio, thanks for the prompt reply ...

An example of the SQL I'm using is:

SELECT DVDs.DVDID, DVDs.Title, DVDs.Year FROM DVDs WHERE (([DVDs].[GenreID]) < 90) Or (([Forms]![Browse DVDs]![Adult]) = -1) ORDER BY DVDs.Title, DVDs.Year;


I have noticed that building the SQL using the design view often results in very wierd SQL - I edited the one above to show what I really want. I have other "tests" that I will add once I get the basics.

 

[Fizzio]

What problems are you running into?

 

[Mike-palmer]

The above query works OK... it's when I add a second criteria and reference a second that the query builder seems to get all messed up. When I look at teh SQL it creates it is all messed up and the form does not work as I would expect.

So I would like to build the query with VB so I can make it correct. From what I've read it looks like i use the Rowsource property to do this but I'm just wondering if that's teh best way.

 

[Fizzio]

I'm surprised that you are having problems with multiple criteria. It may be how you are specifying the criterai that is throwing up unexpected results.

If you want to go down dynamically producing an SQL statement for the rowsource, search the forums as there will be a lot of examples.

 

[Mike-palmer]

Thanks for the help Fizzio. I'm not sure why the Query builder messes up with several criteria and references to forms, but anyway I got it nailed using VB and the rowsource.

Just incase anyone else finds this thread while searching what to do:

View2 is the name of the list box ...

Criteria1 = "DVDs.LastWatched Is Null"
Criteria2 = "DVDs.GenreID < 90"

Me.View2.RowSource = "SELECT DVDs.DVDID, DVDs.LastWatched, DVDs.Title FROM DVDs" & _
" WHERE " & Criteria1 & " AND " & Criteria2 & _
" ORDER BY DVDs.Lastwatched DESC, DVDs.Title;"

 

[Fizzio]

No problem, glad you got it sorted.