1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Form to search & select records from a table

Discussion in 'Business Applications' started by walker3901, Nov 5, 2010.

Thread Status:
Not open for further replies.
Advertisement
  1. walker3901

    walker3901 Thread Starter

    Joined:
    Nov 4, 2010
    Messages:
    13
    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.
     
  2. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    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.
     
  3. walker3901

    walker3901 Thread Starter

    Joined:
    Nov 4, 2010
    Messages:
    13
    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 Cwip 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 PANEL:pRISMATIC:CLR:2X4 251
    255 26261 LENS:14":WHT ACRYL:F/DRUM FIXT 227
     
  4. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    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?
     
  5. walker3901

    walker3901 Thread Starter

    Joined:
    Nov 4, 2010
    Messages:
    13
    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
     
  6. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    What would you want her to be able to search on
    light covers?
    Or
    light & covers?
     
  7. walker3901

    walker3901 Thread Starter

    Joined:
    Nov 4, 2010
    Messages:
    13
    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.
     
  8. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    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.
     
  9. walker3901

    walker3901 Thread Starter

    Joined:
    Nov 4, 2010
    Messages:
    13
    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
     
  10. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    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?
     

    Attached Files:

  11. walker3901

    walker3901 Thread Starter

    Joined:
    Nov 4, 2010
    Messages:
    13
    I believe this is just what I need. I'll try it out now. Thanks
     
  12. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    Tell her not to use the inch symbol " as it won't work.
     
  13. walker3901

    walker3901 Thread Starter

    Joined:
    Nov 4, 2010
    Messages:
    13
    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
     
  14. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    Let me know if you want to store searches.
     
  15. walker3901

    walker3901 Thread Starter

    Joined:
    Nov 4, 2010
    Messages:
    13
    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?
     
  16. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/960769

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice