Page 1 of 2 12 LastLast
Results 1 to 15 of 29
  1. #1
    BakerMan is offline Novice
    Windows 10 Access 2016
    Join Date
    May 2019
    Posts
    16

    Lightbulb Creating a search form with Combo Boxes


    Hello. I have what I believe to be a simple request. I am looking to design a search form for part number lookup. The columns of the sheet contains the parts, the rows contains the unit models, and all the cells in between contain the part numbers. In excel we match the row and column to determine correct part. I would like to be able to have a form that has 2 combo boxes: select part column, and select model row, which then outputs a part number below. Can someone break this down for me, I don't have much knowledge of Microsoft Access.

    Thank you!

  2. #2
    Dave14867's Avatar
    Dave14867 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Nov 2016
    Location
    Upstate NY
    Posts
    376
    Do you have a database started we can look at? What you are asking sounds doable. It would be great to be able to see what you have done so far.

    Dave

  3. #3
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Bakerman,

    You have identified Parts, Models and Part Numbers. How do these fit in your proposed application?
    Perhaps you could give us a few examples, and show us what you want to find when you do your search.
    A brief description in plain, simple English of what you search, what you want to find and report/display would be helpful.
    Good luck.

  4. #4
    BakerMan is offline Novice
    Windows 10 Access 2016
    Join Date
    May 2019
    Posts
    16
    Click image for larger version. 

Name:	Data.jpg 
Views:	32 
Size:	57.6 KB 
ID:	38330Attached is a photo of the table a will import

  5. #5
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Having a hard time reading that image but between what I can see and your narrative, appears to be non-normalized data structure.

    Here is example of a conventional search form http://allenbrowne.com/ser-62.html. Your requirement is not conventional. A DLookup() might accomplish.

    DLookup("[" & cbxPartName & "]", "tablename", "Model='" & cbxModelName & "'")

    The trick is figuring out where to put that code. It could be in a textbox ControlSource.

    This is actually fairly basic Access functionality. Have you studied an introductory tutorial book?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  6. #6
    BakerMan is offline Novice
    Windows 10 Access 2016
    Join Date
    May 2019
    Posts
    16

    Attachment

    Below I have attached my database

    Last edited by BakerMan; 05-10-2019 at 10:04 AM. Reason: Change Attachment

  7. #7
    BakerMan is offline Novice
    Windows 10 Access 2016
    Join Date
    May 2019
    Posts
    16
    I need assistance making these drop downs work and output just filter matching lines in the embedded form below the combo boxes. Thank you.

  8. #8
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Bakerman,

    Please provide a 5-6 line overview in simple, plain English of the business this database is intended to support. I'm sure the search is important, but it "fits" into your business processes in some manner.
    It will help you and readers to communicate your requirements and possible approaches.
    Good luck.

  9. #9
    BakerMan is offline Novice
    Windows 10 Access 2016
    Join Date
    May 2019
    Posts
    16
    We need to use this to quickly look up a part number. If a customer calls us, we get the model and the part they need, and we would select these from the drop down and we would like to be able to output a part number.

  10. #10
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Bakerman,

    Please be specific with your requirement if you want assistance. It is helpful to readers if you give an overview of the business in which your posted "issue" occurs. Your Parts table appears to use your Parts name/description as field names not field value. Your table has no Primary key.

    Your comboboxes on the form both show Model. There is no Event code to react to a combobox selection.

    In my opinion, your requirement is not clear and your table appears to be poorly designed. However, I have asked for the requirement of WHAT you are trying to do, and you continue to show us HOW you have done something that doesn't work to your needs.

    If you can't describe it, then who can build or help design it. We need to know WHAT before we can advise on HOW to do something.

    Step back and give the context of your issue in plain English. What you have shown in your database doesn't appear to be a complete set up for search/report.

    Also, your diagram in post #4 is too small to read.

    I suggest you review some of the articles in the Database Planning and Design link in my signature.

    Good luck.

  11. #11
    BakerMan is offline Novice
    Windows 10 Access 2016
    Join Date
    May 2019
    Posts
    16
    I have given the layout and how I expect the drop downs to affect the output. I understand I have not assigned the comboboxes to perform any function (no previous access experience, the reason im asking for help), I have just given the "template". I don't really understand what you are asking. I WILL NOT POST ACTUAL LIVE DATA FROM OUR COMPANY. that information is confidential. I have spelled everything out in plain English. We have a spreadsheet utilizing and XY access that has cells filled in between the axis's. I need the combo boxes to filter those results. This forum does not look very promising considering I have explained my requests multiple times in a clear fashion. If someone asks me again to explain it im going to just delete my account and move on. Thank you for your time.

  12. #12
    Dave14867's Avatar
    Dave14867 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Nov 2016
    Location
    Upstate NY
    Posts
    376
    BakerMan,

    So what would a Part Number look like? Give me an example

    Dave

  13. #13
    BakerMan is offline Novice
    Windows 10 Access 2016
    Join Date
    May 2019
    Posts
    16
    SG400Cap# SG600Cap# SG400Blower# SG600Blower# are all part numbers that live between the X & Y axis in my table.

  14. #14
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Bakerman,

    Nobody is asking for confidential information. Make up data --Porky Pig, Donald Duck, Idi Amin, Heesa Payne, Central City, Unknown Province, Some Country.... to show us a representation of how the data relates.
    If you do not understand "what / business description" is, then here's an example of the kind of detail to help readers.

    Sample narrative from RogersAccessLibrary Catering Business:
    Sue Johnson is starting a catering business. She is looking for a program to help her maintain her business. She wants to start by tracking customers and orders. She will eventually want to add accounting features like accounts payable, accounts receivable, and inventory control, but wants to start small.

    Customers are the people to whom she sells her catering services. Customer information includes: Name, Address, Phone, Alternate Phone, Fax, and Email. A customer may place many orders.

    An Order is a group of items delivered as a single unit to a single Customer. An Order contains the customer information, the order date, delivery instructions and an itemized list of the Items delivered. An order applies to only one customer at a time, but each order can have many items on it.

    Items are the individual items that appear on an order. Each item has an associated item number, item description, quantity, price and extended price. Each item can appear on many orders, and can appear on one or more orders.


    You have not described Parts nor Models in any context.

    Below is a draft data model from a different thread showing Parts and how they fit together in the user's environment based on the user's description.

    I am only trying to get you to describe WHAT is involved in your proposed database, What you would search for and where does the result fit/go. There is more to your application than a single search and that is important to get focused responses.

    Click image for larger version. 

Name:	ProductAssembledFromParts.PNG 
Views:	23 
Size:	44.0 KB 
ID:	38371

  15. #15
    Dave14867's Avatar
    Dave14867 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Nov 2016
    Location
    Upstate NY
    Posts
    376
    Here is what I came up with quickly based on what I understood you are asking for.

    People aren't asking for confidential info, just a clear understanding of what posters are trying to accomplish.

    MasterPartList-DB.zip

    Anyone here will help you out and most have a fantastic understand of Access, Like Orange for example.

    I work in manufacturing and I believe I understand what you are trying to accomplish, bit I am not sure your table structure will accomplish what you desire.

    Dave

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 5
    Last Post: 09-22-2016, 08:42 AM
  2. Replies: 2
    Last Post: 01-28-2016, 08:23 AM
  3. Replies: 5
    Last Post: 08-05-2013, 09:47 AM
  4. Search Form - Cascading Combo Boxes
    By WeeTerrier in forum Forms
    Replies: 7
    Last Post: 12-05-2011, 08:26 PM
  5. Creating a Form with 2 combo boxes and a text box
    By smorris4804 in forum Access
    Replies: 1
    Last Post: 06-17-2011, 02:06 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums