Solved Using a parameter in a query that will have a changing number of search values (1 Viewer)

[LouisPrefersExcel]

Hey guys,

I currently have a simple query with a parameter to allow the user to type in an item code and pull all of the information required, I then have a macro to export this data to Excel. What I would like to do is be able to enter multiple item codes at once so that I can export the information for all of them in 1 go. I may want to pull the data for just 1 item then the next time I run the query/macro I may wish to pull the data for 4 lines of data.

The best solution I have to this currently is to type all of the item codes in a separate table within the database and to link this to the query instead of typing the codes in, this is not ideal though as this database may have several users that will need to clear the old data in the table and type in new data, and that's only if they know/remember to clear the old item codes and type in the new ones.

Appreciate any help or tips.

Thank you.

 

[Minty]

Assuming your database is split make the table that holds the data a local one, then each user only get their own selections saved?
It can then be displayed and adjusted just for that user?

Alternatively, you could use VBA to create a where clause using something like
IN('Item1','Item2','Item3',etc etc)

This assumes your item codes are text not numbers.

 

[arnelgp]

you can make the table Locally.
but if the db is not Split, then we have a problem.
on both cases this form will do even when your
db is not split.

 

[Pat Hartman]

If the user selects three items, is the data all exported to the same Excel Workbook or do you create three Workbooks?

Typically, you would use a multi-select listbox (for either of the above actions. How you do the export changes though). If you are going to create one export for each selection, then you would write a VBA loop that iterates through the selections in the listbox and exports one file for each. The export query can use criteria that references an unbound form field which is filled with the value for each list item in sequence.

If you want to export only one file with 1 or many selections, then you would use VBA to build an In() clause.
Select ... From ... Where SomeField In(1,3,887)
That sounds like what you want so I'll post a sample. It is not possible to modify the structure of a saved querydef so you would need to crate and embedded SQL String. Look at samples 2-4. One of them should help you.