Filter a drop down list based on what is selected in prev. list

[babs]

I am trying to filter a drop-down list for job # after the Client id drop
down is selected. Right now it is working with the code below. However
there can be the same job # listed multiple times for a given Client. For
example there can be three records with job 123. On the drop down list with
the code below I see all three job 123 listed - I would like it to show up
only ONE time on the drop down.

Please let me know what I need to add to the code to be able to do this.


Private Sub cboClientId_AfterUpdate()
Dim sSQL As String

'This function sets the RowSource of ClientId, based on the value selected
in ClientId
sSQL = "SELECT Job, Taxex, Clientid " & "FROM tblQUOTEJOE " & "WHERE
ClientId = """ & Me.cboClientId & """ " & "ORDER BY Job"
Me.cbojob.RowSource = sSQL

Thanks,
Barb

 

[Brian Bastl]

Try "SELECT DISTINCT Job, Taxes, .....

HTH,
Brian

 

[babs]

Wanted to include all of my current Code so you can see the problem easier
maybe


Private Sub cboClientId_AfterUpdate()
Dim sSQL As String

'This function sets the RowSource of ClientId, based on the value selected
in ClientId
sSQL = "SELECT DISTINCT Job, Taxex, Jobdesc " & "FROM tblQUOTEJOE " & "WHERE
ClientId = """ & Me.cboClientId & """ " & "ORDER BY Job"
Me.cbojob.RowSource = sSQL

End Sub

 

[babs]

I did the Select Distinct - for some reason even occasionally when all three
fields for multiple records Job, Taxex, Job desc(not using Client id) ( the
job desc- is even blank for these records) are the same- there may be 7
entries the same and sometimes 2-3 show up in the drop down- REALLY only want
to see it ONE time?????

 

[Brian Bastl]

Hi babs,

sorry, but I must have been half-asleep when I initially replied.In order to
get the Job to appear only once, you'll have to remove those fields which
are non-repeating from the rowsource. For example, I'd assume that Jobdesc
will be unique for each job quote; this will cause the job to repeat in your
list, regardless of whether you use DISTINCT or GROUP BY.

Brian