[Last Call] Learn how to a build a cloud-first strategyRegister Now


Combine fields with same value for search

Posted on 2012-08-16
Medium Priority
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
  • 5
  • 4
LVL 29

Accepted Solution

IrogSinta earned 1000 total points
ID: 38301700
Add the keyword DISTINCT right after SELECT in the lookup query in your combo box.
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 38302025
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

ID: 38302356
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!


Author Comment

ID: 38302383
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
ID: 38302647
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, ...here is a simple example database...

Author Comment

ID: 38302729
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

ID: 38303382
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
ID: 38303383
<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

ID: 38305978
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
ID: 38318681
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

Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses
Course of the Month17 days, 18 hours left to enroll

829 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