How to filter out the same values in the drop-down list of a combo

[Malekius]

I've got

2 tables: tblMajor and tblClass;
and a form called frmPromt with
two combo boxes: cboClass and cboValue
and a Query1 which binds them all

tblClass has two fields
ClassID (primary key)
ClassName

tblMajor has 5 fields
NumberID (primary key)
ClassID (Foreign Key)
ClassName
ItemValue
ItemName

cboValue is triggered by cboClass

Afterupdate event of cboClass sets the rowsource of cboValue.
so cboValue selects values Where tblMajor.ClassID = " & Me.cboClass

(at the first attempt I did this for the ItemNames and It worked. A list box
bound to Query1 and combo boxes did trick. But in time tblMajor got bigger
with ItemNames which have same names but different Values. So now I have to
select Values rather ItemNames in the Second combo box.)

ItemValues are numbers varying between -20 to 20 but this number interval
changes with respect to Selected Class

For Example
Let say Class A has 4 Items in it

ItemA with value of 0,25
ItemA with value of 0,5
ItemB with value of 0,25
ItemC with value of 0,75

If I select Class A cboValue will probably (drop-down) list all the 4 Values
(which I don't want it to happen)

Is it possible to filter out the same values so drop-down list of the
cboValue only
list as
0,25
0,50
0,75

 

[Al Campagna]

Malekius,
Your combo box should have a query behind it... as it's RowSource.
Make that query a Totals query (View/Totals in query design), and
GroupBy on your value field.
Only distinct values will display... no dupes.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

 

[Malekius]

But a VBA code sets the Rowsource of the combo box.

Such as

Private Sub cboTry_AfterUpdate()

Me.cboProducts.RowSource = "SELECT tblLensler.ClassID, tblLensler.Name FROM
tblLensler WHERE tblLensler.ClassID = " & Me.cboTry & _
" ORDER BY Name"
Me.cboTry.Requery

End Sub

Can I code it so combo box filters out the duplicates?

 

[Al Campagna]

Malekius,

I'm not sure why you're defining the combo RowSource via
VBA, unless you have multiple rowsource queries that you
need to apply to cboProducts.
If cboProducts always uses the same Rowsource, it should
be hard-wired using the combo's rowsource property.
Just paste your SQL statement right into the cboProduct
RowSource. (more on that later)

Some minor points...
In tblLensler you have a field called Name.
Avoid using "Name", as it is a reserved word in Access.
Try ProductName or something that like.
Please don't snip off the previous posts on this thread.
We need to see the complete flow of the problem, without
having to open past posts.

Create a Totals query, with just one column... ItemValue,
and GroupBy on that column.
When you have the query working, then select View -
SQL View, Copy the SQL statement, and plug in into
the Rowsource property of cboProduct.
OR
Replace the old SQL statement that you load via VBA,
with the new one.

Your cboProducts should now show only one of each value,
from all of the values, that satisfies cboTry.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

 

[Larry Linson]

I am not certain what you are objecting to. Whether you set the RowSource in
VBA or type it directly into the RowSource property, the SQL you are using
does not "filter out the duplicates".

One way to do that is to use a Totals Query that uses a GroupBy on the
duplicated field; or with a little experimentation, you might use the
QueryBuilder to create a query with a specified Unique Values, which
translates to SQL with the DISTINCT operator.

Eliminating duplicates from the RowSource by proper construction of your
Query or SQL is, as far as I know, the only way to "filter out duplicates".

Larry Linson
Microsoft Office Access MVP

But a VBA code sets the Rowsource of the combo box.

Such as

Private Sub cboTry_AfterUpdate()

Me.cboProducts.RowSource = "SELECT tblLensler.ClassID, tblLensler.Name
FROM
tblLensler WHERE tblLensler.ClassID = " & Me.cboTry & _
" ORDER BY Name"
Me.cboTry.Requery

End Sub

Can I code it so combo box filters out the duplicates?

__________ Information from ESET Smart Security, version of virus
signature database 4035 (20090425) __________

The message was checked by ESET Smart Security.

http://www.eset.com

__________ Information from ESET Smart Security, version of virus signature database 4035 (20090425) __________

The message was checked by ESET Smart Security.

http://www.eset.com

 

[Malekius]

GROUP BY statement did the trick.

I've included that statement into VBA code and it worked.

Thank you very much for your help