Multi Select List Boxes as Slection Criteria for a Select Query 2

[DropsIT]

I have a customer database and I am building a select query with criteria based upon the selection of multiple records from at least 4 list boxes on a form called "Search_Select". The idea is that one or more of the list boxes is used for the extraction of the data using multiple selection criteria.

My 4 list boxes are:

List_Status (2 cols, Code / Desc), widths 0,2 bound column 1
List City (1 Col, City)
List County (1 Col, County)
List_Country (1 Col, Country)

I am hoping to add more list boxes to give more selection criteria in future.

What I would like to do is to put the multiple selections from the list boxes into a number of tables S_TYPE, S_CITY, S_COUNTY & S_COUNTRY and then link these to the main database tables as part of the query. If no selection is made from a list box then all records from the list box should be put into the table so that all matching records are selected as a default.

Can someone tell me if this is the best way to do this or if there is an easier way ?

What would be the code to copy the multiple selected records from each of the list boxes to get them into the tables and if nothing is selected from the list box all records from the list boxes are copied to the respective tables
?

 

[dhookom]

There are some FAQs in this forum that might help. I would not modify records or add them to implement multi-select list boxes. There is a generic function with sample usage at

http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[randy700]

How about....

Using a Multi-Select Listbox As Criteria for a Form or Report


Randy

 

[DropsIT]

Hi Randy

Thanks for this - I have created the unbound text box so that as I now select multiple items I get a result of 'A' or 'B' Or 'C' etc. on update of the source list box

However when I pick this up as the criteria of a select statement it is not recognising the result as a criteria correctly.

Is this because the format of the criteria is not formatted correctly when it is read from the unbound text box ?

Many thanks

 

[dhookom]

I'm wondering why you want to "put the multiple selections from the list boxes into a number of tables". Are you opening a form as noted in the link from Randy? Do you have any code or information about what you created?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[DropsIT]

Hi Duane

I'm not actually trying to put the criteria into a table, but I do need to use the criteria as part of a select query to extract items out of a table.

I can get 'A' Or 'B' or alternatively [TYPE] = 'A' Or [TYPE] = 'B' into the unbound text box called [List_Status_C] but when I reference this as [Forms]![Search_Select]![List_Status_C] as the select criteria in the select query it doesn't seem to work. I am assuming that the problem is that it may be treating the whole text as a complete text statement i.e. "'A' Or 'B'" and not individual criteria seperated by the 'Or' expression.

Do you think this may be the problem ? - If I could send you a screen shot it may be easier to explain.

Thanks

 

[dhookom]

I'm sure that is the problem. You understand the issue. That is why you have been directed to two other solutions.

Apparently you are not using this criteria to open a form or report. If you were, then Randy's suggestion would be my choice. If this is for just a query then use my suggestion. There are other solution but your current attempt is not one of them.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]