Populating Listbox Records from Code

[GummowN]

I am attempting a bit of reverse engineering with a query builder I have designed. The user can select fields from a list box and then build a query from them. They can then save the SQL statement as a record in a table (or multiple records if the SQL statement is over 255 in length).

The problem arises when I load the query. I can concatenate the fields to produce the loaded SQL statement. I can then split in the SQL statement into its component parts (SELECT, FROM, WHERE, ORDER BY) and then into the fields.

The question is can I take the list of values and highlight those values where they appear in the list box?

 

[scking]

I'm confused why you are limited to 255 characters and splitting up queries. Can you not use a memo field to store the SQL? You could store the Primary Key in the listbox and use it to DLookup or find using a recordset to execute it. ----------------------
scking@arinc.com
Life is filled with lessons.
We are responsible for the
results of the quizzes.
-----------------------

 

[FancyPrairie]

Yes you can highlight the items in the list box that match the where clause of your select statement.

I've written a general purpose routine that does this. It assumes that you can have more than 1 list box for your critiera. Therefore, I use the tag property of the list box to tell me 3 things I need to know to build the Where clause. Likewise, these 3 things then help me to highlight the items included in the Where Clause. The 3 things I need to know are:
1) Name of Table
2) Name of Field
3) Type of Field (string, number, etc)

i.e. tblYours.strField1.string

Now I can search my where clause for "tblYours.strField1". If it exists then I know something in the list box needs to be checked. So I loop thru the list box looking for the value referred to by "tblYours.strField1="thisvalue".

Works great. The user's can select the items they want from the list box. I can then create the Where clause and then save the SQL statement to a query. The user can then, later, instead of reselecting the criteria, they can select a saved query. I will then open that query to get the SQL statement and highlight the items selected in the list box.

Also, if the Criteria screen contains a date range, you can populate it with what's in the Where clause.