We help IT Professionals succeed at work.

Combine fields with same value for search

jleval
jleval asked
on
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
Comment
Watch Question

SILVER EXPERT
Commented:
Add the keyword DISTINCT right after SELECT in the lookup query in your combo box.
Jeffrey CoachmanMIS Liason
SILVER EXPERT
Most Valuable Expert 2012

Commented:
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 & "'"
me.FilterOn=true

Author

Commented:
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
frmMeasurement.accdb

Author

Commented:
Be aware that a GUID is used as the primary key, so code is involved in the afterUpdate event
Jeffrey CoachmanMIS Liason
SILVER EXPERT
Most Valuable Expert 2012

Commented:
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...
Database26.mdb

Author

Commented:
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

Commented:
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
Jeffrey CoachmanMIS Liason
SILVER EXPERT
Most Valuable Expert 2012

Commented:
<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.

JeffCoachman

Author

Commented:
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
Jeffrey CoachmanMIS Liason
SILVER EXPERT
Most Valuable Expert 2012

Commented:
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...

Explore More ContentExplore courses, solutions, and other research materials related to this topic.