Whats wrong with this

[dmyoungsal]

I am reworking a DB to simplify maintenance for salesman if/when I leave this company. The intent is to consolidate three tables into one flat file (which will work just fine).

However, I still need to be able use three cascading combo boxes to filter the search.

My first combo box (cboBrand), has this code as RowSource SELECT DISTINCT qryPurchaseInfoNew.Make FROM qryPurchaseInfoNew;



The second combo box will filter the results of the first, but it's not showing the distinct values and I cannot figure out why. Here is the code:
SELECT DISTINCT qryPurchaseInfoNew.ID, qryPurchaseInfoNew.TracModel, qryPurchaseInfoNew.Make FROM qryPurchaseInfoNew WHERE ((qryPurchaseInfoNew.Make)=[Forms]![frmTractorPriceToolNew]![cboBrand]) ORDER BY qryPurchaseInfoNew.TracModel;

[ranman256]

you have too many fields...3 fields ESPECIALLY: id,TracModel, will not produce a unique.

only 1 field can be unique:
SELECT DISTINCT qryPurchaseInfoNew.TracModel from qryPurchaseInfoNew WHERE ((qryPurchaseInfoNew.Make)=[Forms]![frmTractorPriceToolNew]![cboBrand]) ORDER BY qryPurchaseInfoNew.TracModel;

[dmyoungsal]

Well, that did not work.

I went from having all the related models (not distinct), to no models being displayed.

I repasted the original code back in and am now showing all the lines again. So, still looking for a solution

[June7]

Agree with Ranman.

Although RowSource SQL can be simpler

SELECT DISTINCT TracModel FROM qryPurchaseInfoNew WHERE Make=[cboBrand] ORDER BY TracModel;

Then need code in TracModel GotFocus event to requery: Me.cboTrac.Requery

Wonder why pulling from query instead of table?

Post examples of data or provide db for analysis. Follow instructions at bottom of my post.

Cross post https://www.access-programmers.co.uk...d.php?t=299887

[dmyoungsal]

Not sure why I am using a qry other than it allows me to look at it in a sorted fashion, but probably dont need to.

[dmyoungsal]

Below is a sample of the three columns I am selecting from. There are about 20 columns to the right containing price and tech information of each.

cboBrand = SELECT DISTINCT tblPurchaseInfoNew.Make FROM tblPurchaseInfoNew;

cbModel = SELECT DISTINCT TracModel FROM tblPurchaseInfoNew WHERE make [cboBrand] order by TracModel
Format
Column count 3
Column widths 1, 1, 0
Data
bound Column 1




I played with this a bit more using the SELECT statement format you suggested (and RanMan) and I am getting correct results. Now I just need to pass this to the third combo.

If you have suggestions, for that column, please do...

THANK YOU both

[June7]

Just make the third dependent on the second.

[dmyoungsal]

Now I get a Type Mismatch

I used the code: SELECT DISTINCT TracModDescr FROM tblPurchaseInfoNew WHERE TracModel=[cboModel];

I had to take the "Order By" statement off because I got a "order by clause causes a conflict" error. I removed it and I am seeing the combo boxes with the correct info. Since I took the form from another process, I need to pass the selected record to the main part of the form, which I am guessing is the cause of the mismatch error.

[June7]

TracModel is a text data type field? You are not using Lookups in table I hope.

[CJ_London]

for the benefit of responders crossposted here with answers

https://www.access-programmers.co.uk...d.php?t=299887

@dm - please read this thread about the etiquette of crossposting http://www.excelguru.ca/content.php?184

[dmyoungsal]

When saw something regarding Crossposting, I thought it dealt with posting in different forum sections (General vs Forms vs Query, etc). for that I apologize. In the past I used Access Programmers, UK and It.Toolbox.com with the need for mentioning the site.