Criteria to query more than 1 partcode?

[KevinSlater]

Hi,

I have the following criteria on a field named "STK_PART_CODE" in a Query
: [Enter Part Code] to promt the user to enter a part code in the input box to lookup, however I would like to be able to give the user the option of entering more than one part code to lookup and display in the query results.

Ideally would like to give the user the ability to enter upto 10 part codes at a time, but sometimes they may only need to enter just one.

Is this possible?

Kevin

 

[rainman89]

Are you going to have 10 diff spots for them to enter in??

Would using a multiselect listbox do the trick? Of course I dont believe that you can type into a listbox and have it go to what you want.... but I may be wrong

 

[KevinSlater]

hm multiselect listbox sounds like a good option, although theres about 1264 records so maybe that could be to much for the user to scroll and find the right records to select but i can try that if you know how?

10 different places for them to enter could be to much.

 

[Atomic Shrimp]

If you get them to enter the part codes as a comma-separated list, you could knock up a bit of VBA to build a query that took the entered list, replaced the commas with " OR Like ", then inserted that as the criteria in the query.

Question is, what next? - the user types in three part codes and gets back a list of the three relevant parts... what are they then going to be able to usefully do with that list - surely any action they take will most likely apply to only one, won't it?

Another way around it might be some sort of search one at a time, with an option to 'add to list', creating a small temporary table containing a list keys that can be used to select the desired records with a Join in the query.

 

[rainman89]

With 1264 records, we might want to think of another way to do it.

Mikes solution of the list and commas would work

You could create a combo box and in the after update, or other event that you wanted you would put

If me.yourtextfieldlist ="" then
me.yourtextfieldlist=me.comboboxname
else
me.yourtextfieldlist= me.yourtextfieldlist +","+me.comboboxname
end if

 

[KevinSlater]

A report looks at this query for the user to view, a macro loads the report and is set to print the report.

A button on the menu (switchboard) runs the macro.

The query displays other data other than just the part code, no it may sound a little odd but they need to be able to enter more than one part code from the information they will be viewing.

The comma option sounds good to me, so do you mean create a new form with a combo box for the user to enter the codes they want to look up, and this in turn loads the macro (which in turn runs and prints the report)?

 

[KevinSlater]

Im not sure how to go about doing this?, rainman89 please can you explain this code sample? i guess id still need some vba code to build a query that took the entered list, replaced the commas with " OR Like ", as suggested by mike?

 

[rainman89]

All the code sample does it take what the user selects in the combo box and puts it into a textbox for storage.

And yes you will then need to write something that looks at the values in the textbox and replaces the ","s with the "OR Like" that mike mentioned

 

[Atomic Shrimp]

How about this as a bit simpler (and, OK, dirtier) a solution...

Add a 'mark' field (yes/no type) to the main parts table

Give the user a button labelled 'clear all marks' - get it to run an update query that sets all the marks to 'no'

Give the user a way of searching for a record, then just marking it, then searching for another one and marking it, and so on.

Produce a report (or whatever) based on a query with criteria to filter only marked records.

Disadvantages of this approach:
-Ugh, it's a bit crude
-It could go wrong in a multi-user environment, if two people are individually trying to mark two different collections of records.
-People might not clear the marks before running the report and end up with their own data, plus the previous set.

On the plus side, it's not too different from the 'check to compare' option you see on a lot of e-commerce websites, so it might be easy for people to understand.

 

[raskew]

Hi -

Take a look at this.

Bob

 

[KevinSlater]

Mike, your suggestion sounds like an idea but the problem is the some of the fileds the query looks at are comming from an Oracel table that i cant easily add an extra filed too.

Bob: that code looks good and seems to be more like what im looking for, although theres a few issues:

I dont want the user to have to enter the table name or field name
(ive modified the code so that pTable = ("myqueryname")
im not sure what pField needs to = to not promt the user, i guess pField = STK_PART_CODE?


Also the original query is looking at a few different tables, and bobs code seems to create a tempquery table, which is fine if the user just needs to see the search results in that query but i need to be able to get the other required fields in the query for the report, otherwise the report wont be displaying all the correct data.

See screenshot of the query - sorry i guess i should of explanined this better to start of with.

Also as the STK_PART_CODE field is based in an oracel table and is set to 20 charaters it seems to need 20 characters input exactly otherwise the search wont work (so i previusly had this criteria on the field in the query: Part code]+" " to add in extra spaces, part codes are mostly 7 characters so i added 13 spaces, not ideal I know but not sure of a better way? and to get this working with the code bob suggested.

I will try to modify some of the code but not sure if i can do what im trying to achieve without help so any suggestions on if the above changes is possible to do would be great.

 

[KevinSlater]

query screenshot attached.