Get SQL from data on the multi column listbox

[paramesium]

Hi All, I wish to generate a new query consisting rows that I have selected in a multi colum list box.

May I know if it is possible to work?

I am totally lost now

Thank you!

 

[pr2-eugin]

This is possible, but needs a little bit of coding, and probably a small knowledge of QueryDefs (that is if you wish to edit already existing Queries)..

You need to (on click of the button) loop through the Listbox, adding all the SelectedItems in a long String seperated by comma. Then finally add that to the SELECT statement. Then use that to assign to a Recordset object.

 

[redalert]

Another way and one that involves little coding although it does depend on what sort of recordset you want to create and where your options come from.

Create a small sub form based upon a table, (e.g. tbl_Options), with the options in it and a Yes/No field named 'ThisRecord' for example. Include this field on the sub form as a checkbox.

You can use the tbl_Options table in the query and specify the 'ThisRecord' field to be True to select the record.

You unfortunately lose the use of the Multi Select property that a Listbox has to offer.

I often have popup form with this type of subform on it instead of the subform being on the main form. This saves space on the main form.

 

[paramesium]

thank you for your reply.
Actually what I wanted is as followed:

-----------------------
Name | Phone | Type|
-----------------------
Joe | 123 | CB |
Amy | 456 | CV | <--- Highlight this row
James| 789 | CB | <--- Highlight this row

Then, by pressing a button, I will be able to transfer it to a query as follow:
-----------------------
Name | Phone | Type|
-----------------------
Amy | 456 | CV |
James| 789 | CB |


Is this possible?

Thank you very much!

Best,
Jack

 

[paramesium]

Another way and one that involves little coding although it does depend on what sort of recordset you want to create and where your options come from.

Create a small sub form based upon a table, (e.g. tbl_Options), with the options in it and a Yes/No field named 'ThisRecord' for example. Include this field on the sub form as a checkbox.

You can use the tbl_Options table in the query and specify the 'ThisRecord' field to be True to select the record.

You unfortunately lose the use of the Multi Select property that a Listbox has to offer.

I often have popup form with this type of subform on it instead of the subform being on the main form. This saves space on the main form.

Actually I was working on a dynamic search function, I have already coded it on using a list.
Is ok to lose the multi select property, but how do I link the subform with the list? is it very complicated?

 

[redalert]

There needs to be a field in the tbl_options table that represents the identifier in the table that you are wanting to query.

Link the table that you want to query with the tbl_Options table using this field and only return options where the YesNo field is true.

If you want to use the Listbox then set the bound column in the Listbox to be the identier and make reference to the Listbox in the identifier field criteria row in the query.

 

[paramesium]

There needs to be a field in the tbl_options table that represents the identifier in the table that you are wanting to query.

Link the table that you want to query with the tbl_Options table using this field and only return options where the YesNo field is true.

Thank you very much on your ideas.
I was wondering, how is the tbl_options being updated after it is binded with my data using queries.

I mean, when I select the option, I am actually clicking button on the subform which the "table" came from the query that link tbl_Options and my table.

What should I do to update my tbl_options by clicking the button on the subform.

P/S: sorry for my poor language, I hope you understand what do I mean.

 

[redalert]

Try the attached database for an idea.

Using this technique I am sure that you can build what you want.

 

[paramesium]

yes! this is exactly what I want!
Thank you so much!

 

[paramesium]

Hi Redalert,

Thank you for your help last time, I am having great progress now.

However, I still need some little advice from you.

The program I am working on will be able to filter off the recordset on the subform using following method.

    Forms!frmGenerateChecklist!frmSubform.Form.Filter = strFilter
    Forms!frmGenerateChecklist!frmSubform.Form.FilterOn = True

Is there any way to "Select all" the filtered data?

thanks a lot!

 

[redalert]

This should do the trick.

Forms!frmGenerateChecklist!frmSubform.Form.Filter = ""
Forms!frmGenerateChecklist!frmSubform.Form.FilterOn = False