Combine fields with same value for search

Posted on 2012-08-16
Last Modified: 2012-08-21
I have a form that has a combo box for looking up a particular part number. Within the table, there are several instances of the same part number. What I would like to do is display only one instance of the part number for a look up, but once the part number is selected, show every record for that numberToolMeasurementForm
So instead of having multiple showings of partnumber, have one showing, but return all the records for that part number
Question by:jleval
    LVL 29

    Accepted Solution

    Add the keyword DISTINCT right after SELECT in the lookup query in your combo box.
    LVL 74

    Expert Comment

    by:Jeffrey Coachman
    1. It is hard to see how that form is designed...
    (Main Form/sub Form?, ...etc)
    2. It is not clear of how this the combo-box is working currently...
    (Based on a numeric Id, but displaying your text value, ...etc)

    This is why posting a sample database is always helpful...

    But in a nutshell:
    To filter a form based on a text value in a combobox,  you would use code like this:

    Me.Filter="PartNumber="& "'" & me.cboPartNumber & "'"

    Author Comment

    Okay, Here is my form with the related tables. Two things I would like to do
    1) Have only one selection available in the combo box (PartNumber) instead of multiple selections-
    2) When I make that selection from the combo box, display all of the selections(all of the partnumbers that match the single part number

    Author Comment

    Be aware that a GUID is used as the primary key, so code is involved in the afterUpdate event
    LVL 74

    Expert Comment

    by:Jeffrey Coachman
    1. If this form has multiple instances of the same part, you should not be trying to "Select" a part, you should be "Filtering" for it...

    2. <1) Have only one selection available in the combo box (PartNumber) instead of multiple selections- >
    I am confused,
    The purpose of a combobox is to display multiple Items...
    If you only want "one selection available in the combo box", then don't use a combobox, ...use a textbox and hardcode the ID in the textbox's controlsource

    2. Your Main form and the subform have the same recordsource, so I am confused as to why you are using a subform?

    3. In your posts you say "part number", yet there is no "Part Number" field in any of the tables...?

    4. Not sure why your ToolID is a Replication ID field...?

    ...In other words, ... can you first take a step back and explain the purpose of this form?

    Based on my interpretation of what you have here, is a simple example database...

    Author Comment

    1) Yes I guess I would be filtering it
    2) When the PartNumber combo box is selected, there are several instances of the same PartNumber. I just want a single instance of the PartNumber to show even if there are several of them. That way, when the person chooses to filter on a part number, there are not several items of the same PartNumber to choose from
    2?? The subform actually has two record sources CutterToolMeasData and CutterToolMeasurements. Each Record Expands to include other measurements
    3) There is a PartNumber Field in the CutterToolMeasurement table, don't know how you missed that
    4)This is a replication ID because this database is part of a larger program and data is transferred to it from that program.

    So, the purpose of the original form was to take batch measurements from another program and place them in the database. However, our customer wants to view more than the batch information. He wants to be able to see all the measurements for a particular tool as well as for a particular batch.

    So, this form is a copy of the batch form, with the primary difference being that instead of viewing a particular batch of measurements, he wants to view measurements for a particular tool. Basicly a summary of all the batch measurements.

    The purpose of this form is to be able to see all the measurements for a particular tool based on a PartNumber.

    Hope that clears things up for you a bit. If you take a deeper look at the relationships and the tables, it would be easier to understand.

    This form is based on everything in the original batch form except that the parent batch table is no longer a part of this form

    Author Comment

    I've requested that this question be deleted for the following reason:

    I received more questions than answers and all the questions were irrelevant to what I was trying to do
    LVL 74

    Expert Comment

    by:Jeffrey Coachman
    <questions were irrelevant to what I was trying to do>
    My questions actually were relevant:

    1. You stated: "Select", and I clarified that you wanted to "Filter"

    2. You stated: "Have only one selection available in the combo box"
    I asked for clarification, then you stated:
     "I just want a single instance of the PartNumber to show even if there are several of them."
    "One Selection" and "single instance of the PartNumber to show even if there are several of them", ...are two different things.
    Thus my questions seemed to be quite relevant in clearing those points up, ...leading to the sample I posted.

    The theory behind my sample is that you should have a "Parts" table and base your combox on that (Not the same table as the form)
    ...Then "Filter" the form, (not "select" a record)

    So please take a moment to examine the sample I posted, and see if the basic functionality is what you need.
    Then we can work together and apply those same principles to your own sample database.


    Author Comment

    I checked out the database you sent, and it doesn't work for  what I am doing. You have only one instance of each tool, I have several instances of each tool. I need to combine each instance so that those tools and there measurements show in the subform below
    LVL 74

    Expert Comment

    by:Jeffrey Coachman
    The database was just a sample to show that you really wanted "Filtering" not "selections"

    Given the opportunity we could have worked toward adapting it to work in your database...

    Featured Post

    Maximize Your Threat Intelligence Reporting

    Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

    Join & Write a Comment

    Suggested Solutions

    This isn't a frequent question on EE. I must have seen it three or four times (among several thousand questions). However, I use this trick quite often, most frequently as a delayed Current event. A form does not expose it's calculation dependenc…
    A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
    Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
    Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now