# Solved: Form to search & select records from a table



## walker3901 (Nov 5, 2010)

Hello, I am trying to accomplish the following with Access 2010: 1. Filter records within a table by typing characters that are contained in a text field. The text filter on the shortcut menu searches and returns records to suit me but I need it to make several selections. Combo box does similarly, but returns only those records that begin with the characters you type. 2. Select the desired record from those displayed and add that record to a current list. 3. Repeat filtering and adding until all desired records from the table have been added to the current list. 4. Displ the current list in a report or form for printing or saving as a text file. I will be grateful for any tips on the best way to do this.


----------



## OBP (Mar 8, 2005)

It is not normal for "Users" to interract directly with the table, it is normally done via forms and queries. What you are trying to do is what Queries are for, although users normally only search for 1 criteria per field at a time.
So you need a Search Form to set up the Criteria that the user needs.


----------



## walker3901 (Nov 5, 2010)

I'm understanding one criteria at a time but some of these records have relationships that are not easily defined. It would be ideal for the user to be able to enter keywords from the item description field and make selections from those items which contain the keywords in that field. My most valued assistant has long been constrained to pouring through several 500+ page catalogs to select items for ordering from a few suppliers. My belief is that Access can save her a huge amount of time and surely someone else already has a solution. All the selection criteria she needs would be in the Item column. I think you are telling me that I need to further devide that column in order for Access to search efficiently.

*ID Cwi**p **Item **CatPage *
252 32725 FIXT:RUM:13"RND:WHITE:3-PL13 227
253 25252 FIXT:RUM:13"RND:WH:1FC8&1FC12 227
254 22067 LIGHT PANELRISMATIC:CLR:2X4 251
255 26261 LENS:14":WHT ACRYL:F/DRUM FIXT 227


----------



## OBP (Mar 8, 2005)

OK, I am not saying divide the column up, just the input of what the user is looking for.
So using the 4 records that you have shown, what kind of search would she like to be able to do?


----------



## walker3901 (Nov 5, 2010)

Typically she is presented with lists of supplies needed for 10 techs, maybe 40-75 items in all. She must then match those items to those items which are available from our supplier and list those items with the quip. There are somewhere between 1500 and 2500 items approved for ordering. That example might result from a list of requests reading like this:
2 Hallway light covers
6 bath light covers
3 light covers for office fixture
1 circuline light cover


----------



## OBP (Mar 8, 2005)

What would you want her to be able to search on
light covers?
Or
light & covers?


----------



## walker3901 (Nov 5, 2010)

I think either would do, other criteria might include: Light bulb incandescent, light bulb Flourescent ft40, light bulb flourescent cfl7w, supply line bsn, supply line 1/2x3/8, etc. There are common flags in the item description fields from each supplier that she can use to identify and choose from several items if Access could present them for her to select from. She might be presented with Suppy line bsn, supply line 1/2x3/8, and bsn supply line along with several others and either of those 3 would be an appropriate selection. It may appear that the complicated, but a quick glance at a list of items fitting those descriptions would enable her to accurately chose the appropriate selection.


----------



## OBP (Mar 8, 2005)

I think a Combo and free text "Form Filter" would fit the bill.
can you give me the name of the description field?
I will be nack tomorrow.


----------



## walker3901 (Nov 5, 2010)

The Item feild is the one which contains the item description. That table currently has 4 fields, ID, Cwip=Supplier part number (must be included in order), Item(description of item which may include 1-7 or 8 specs according to how many different variations exist), and CatPage(She rarely needs to refer to this page to choose between several items, usually if the prefered part isn't in stock or new items are suggested) We may want to add some fields later, as all items fit into budget categories which must be tracked and several items are prefered over others that are also acceptable. Being able to search and select are the only issues that present an obstacle at this time though.
Thanks


----------



## OBP (Mar 8, 2005)

I have created the attached database witha table using those 4 records, a Query and a Form.
The Form has a "Find Item" Combo for finding single items. For more complex searches there are 3 text fields which can be used to search for up to 3 words or short phrases, they can be used in any order. 
There is a Search Button which sets the Form Filter to find matches for the text using the "Like "*" for pattern matching.
I think it would be a good ida to be able to save those searches for future selection using a Combo, what do you think?


----------



## walker3901 (Nov 5, 2010)

I believe this is just what I need. I'll try it out now. Thanks


----------



## OBP (Mar 8, 2005)

Tell her not to use the inch symbol " as it won't work.


----------



## walker3901 (Nov 5, 2010)

Ok, Ill tell her. 
This will work well for her. I have tried it with a large number ov items in the table. The search box will return a small number of items and can be narrowed still more if needed.
Thanks


----------



## OBP (Mar 8, 2005)

Let me know if you want to store searches.


----------



## walker3901 (Nov 5, 2010)

Hello,
This allows her to quickly go to the item she would be searching for, now she will want to build a list of those items and print or save that list after all have been selected. How can this be done?


----------



## OBP (Mar 8, 2005)

I would suggest saving the IDs to a print table, which is populated, printed and then cleared after printing.


----------



## walker3901 (Nov 5, 2010)

You are saying that I can usa a querry to pass on the selected record to a new table?


----------



## OBP (Mar 8, 2005)

You can use a query to put an ID in to a temporary table, or do it directly with VBA, then print the list and clear it.
I would suggest using a Button with VBA to put the selcted item in to the table and reste the filter foe another slection. Plus a Print List button (you could show the list if necessary on an a subform) that prints the list and then clears the table ready for the next batch of selections.
Do you want me to create them for you?


----------



## walker3901 (Nov 5, 2010)

I would very much appreciate it, thought to take a stab at this myself, but it's been a long time since I took classes for or used Access and VBA. It's not like riding a bike.


----------



## OBP (Mar 8, 2005)

Do you want to post what you have, or shall I work with the one that I posted?


----------



## karlhaywood (Jan 17, 2010)

Just took a look great work OBP, i am thinking of doing something similar.


----------



## OBP (Mar 8, 2005)

Walker, I have just had a thought, why don't you add a "Print" tick box to the Item table, she can just tick any record she finds, which you can use in a query to print the list. Just have an Update query to reset the tick box back to "unticked" after printing.

Karl, it works well.


----------



## walker3901 (Nov 5, 2010)

Here is the the data I have been working with with the changes you made so far. There will be about an equal number of records to add to this later, but it will be in the same format. In case I forgot to say it to you, Karl is right, Great work!


----------



## OBP (Mar 8, 2005)

walker, did you see my remark about the "Print" check box?


----------



## OBP (Mar 8, 2005)

Have a look ate this version, I have added the Print check box to table, query & form.
Created a Reset Query and a Report query and report and some buttons to make it all work.
She can just tick the ones she wants to print and when printed they reset back to unticked.


----------



## walker3901 (Nov 5, 2010)

Yes, I say it and attempted to put it in but had some issues. Its selecting all the records.


----------



## walker3901 (Nov 5, 2010)

Excellent! Sandra can start working with this tommorrow. I only need to change the report heading and maybe replace the check box in the report with a space to indicate quantity. Even Walker can remember how to do that. 
Thanks a lot!!!


----------

