Multiple Input Parameters Question

[cjh7111]

Hi all,

Alright so I'm new to Access and I'm a little lost. My end goal is to create a database where users can open up the front end and input "Material Numbers" and then will be provided with the related data for said material numbers.

Currently, I have the query set up with the following SQL code:

SELECT [Material Key], [Material Name], [info]
FROM Table1
WHERE (((";" & [Enter Material Key] & "; ") Like ("*" & [Material Key] & "*")));

So far this works pretty well. However, in parameter input box can only fit about 30 semi colon delimited concatenated material numbers. I need it to be able to do a couple thousand at a time ideally.

So, is there anyway to get it to work the way I have it? If not, does anyone have any ideas how I could go about accomplishing this?

Thanks

 

[plog]

I need it to be able to do a couple thousand at a time ideally.

Load them into a temporary table. Let's call the table 'search_MaterialKeys' and the field [MaterialKeySearch]. Then you add that table via an INNER JOIN in your query:

SELECT [Material Key], [Material Name], [info]
FROM Table1
INNER JOIN search_MaterialKeys ON Table1.[Material Key] = search_MaterialKeys.[MaterialKeySearch];

 

[Ranman256]

Have them select items from a table. These items in the tPicked table are joined to the main data table and ONLY those items picked will get pulled.

 

[cjh7111]

Thanks plog, that's helpful.

I'm a little confused as to how to make a temp table. Ideally, a form would pop up that users could paste their list of mat numbers and it would populate the temp table and then run the query based on the numbers in the temp table. Is this possible?/ How would I go about it?

Thanks again

 

[plog]

To make the temp table, you simply create a new table with one field with they same data type as Table1.[Material Key]. To have it work like you want, is possible, but will require some coding. Don't know your experience with Access, but this will require some medium level skills.

Here's the broad strokes:


1. Create your temp table as described above.

2. Create a truncate query to delete all the data in the temp table. This will remove all previous search data and give the user a clean slate for their search.

3. A form with a text area to paste their data into and a button.

User opens form, pastes data into area and clicks the button. Using VBA you run the truncate query to clear out the temporary table, then more code for parsing their pasted data into that table (probably involve some string functions, maybe a recorodset, a loop and executing dynamic SQL) and then finally after the data is loaded into the temporary table you run the query I explained in the prior post.

So yes, possible. If new to Access, possible but will be a struggle.