How to Put Two Columns' Values into one List-Box

joesmithf1

Registered User.
Local time
Yesterday, 16:08
Joined
Oct 5, 2006
Messages
56
Hi All,

I have a list box and I populate it with records from a "Select" query. I am using VBScript to do this. Here are my codes:

gr1_list.RowSource="Select Criteria from CriteriaList WHERE Criteria='" & cvalue & "';"

My "CriteriaList" table has two columns; Criteria and CriteriaDescriptions.

The question is, what if in my list box I want to be able to see both 'Criteria' and 'CriteriaDescription?' How would I code this?

Thank you.
 
gr1_list.RowSource="Select Criteria, CriteriaDescription from CriteriaList WHERE Criteria='" & cvalue & "';"

And change the column count and width's properties appropriately.
 
Thank you, thank you! This works! I knew how to write the select statement but I was not aware that I have to set the Column Count to 2. No wonder the list-box came out blank when I did not set it to 2.

Now I have a different question that is very similar to my previous post, but with a little twist to it.

Here are the codes:
Listbox2.RowSource = "Select [" & listbox1 & "],[" & listbox1 & "Desc] from [" & listbox1 & "] Group by [" & listbox1 & "];"

Let me give you the setting. I have 3 tables(Agency, Department, Title). Each table has two columns(Agency and AgencyDesc, Department and DepartmentDesc, Title and TitleDesc).

On my form I have two list-boxes. The values in Listbox1 are ‘Agency, Department, Names’ simple enough. Now for Listbox2, the values all depends on what the user select from Listbox1 as you can see from my select statement above.

When I execute the codes there are no errors, except Listbox2 came out empty. What am I doing wrong? Are the syntax in my select statement correct? I tried moving ‘desc’ around but still won’t work. Please help!

Thank you.
 
Well, your references to "listbox1" are going to place the exact same thing in each of those positions, which I can't believe is what you want. With a selection of "Accounting" in the first listbox, your SQL will be:

"Select [Accounting],[AccountingDesc] from [Accounting] Group by [Accounting];"

Which doesn't look too logical. Typically the only place you'd want a value from the first listbox would be for the WHERE clause. Something like:

... = "SELECT FieldName FROM TableName WHERE OtherFieldName = '" & listbox1 & "'"

Your code is actually making the fields selected and the table they're selected from dynamic based on the first listbox. Is that what you want?
 
Thank you, Paul. I understand what you are saying, but I might have left out some details since I just wanted to get to the point.

To clarify, ListBox1 is nothing but to let my select statement decide which database table my query will select from. My form is a dynamic interact form for users to search information. Think of it as a data warehouse. For example, a user want to narrow the search by Agency, so they would click on ‘Agency’ in Listbox1. Then Listbox2 will list all the Agency(codes) and Agency Descriptions so users can farther narrow their search. Does this make sense?

Btw the way, I figured out what I did wrong; I did not add Listbox1Desc to my ‘Group by.” Here is the revised statement:

Listbox2.RowSource = "Select [" & Listbox1 & "],[" & Listbox1 & "desc] from [" & Listbox1 & "] Group by [" & Listbox1 & "],[" & Listbox1 & "desc];"

Thank you very much again!

Leon
PS. I might have a lot more questions since I am trying to learn as I work on my project.
 

Users who are viewing this thread

Back
Top Bottom