avoiding combo box duplicate values

[VBVines]

I am using this simple loop to populate a combo box from an access field (loggeddate),that has several duplicate values. Can anyone show me some code I can add that will just show one of each of the values.

Set rs = conn.Execute("Select * From Clerical_Careers&quot
Do Until rs.EOF
Combo1.AddItem rs("loggeddate&quot
Combo1.ItemData(Combo1.NewIndex) = rs("LogID&quot
rs.MoveNext
Loop


Thanks for any info aspvbwannab

 

[oneshadow]

Can you put DISTINCT in your sql statement?

 

[VBVines]

Oneshadow, Thanks for taking the time. I tried various ways of trying your suggestion but Couldn't get it to happen. I even tried to develope the query using Access Query window and couldn't get it to happen. It will come to me eventually, it always does. Thanks again. aspvbwannab

 

[dpaulson]

Here is an example from MSDN

SELECT DISTINCT LastName FROM Employees


David Paulson

 

[oneshadow]

From your code above it seems like you store 2 fields from Clerical_Careers table. The sql statement should be like this:

"SELECT DISTINCT loggeddate, logID FROM Clerical_Careers"

however, if you have records which have same loggeddate but different logID, this way won't work. In this case, we need to try another approach.

 

[VBVines]

OneShadow, Thanks again. The Select statement you provided is one of the many ways I tried. Unfortunately last thing you said is exactly what I am running up against. Different records with the same loggedDate but different LogID (primary Key). This isn't critical but would be more asthetically pleasing if I could to it and thanks again. aspvbwannab

 

[CybOrg]

Hi!
If what you want to do is display a list (or combo box) of all the distinct date, have the user click on a date, and then produce a selectable list of all the log_id's for the clicked date then you can do like this...

Create the first combo box and populate it with a statement like 'SELECT DISTINCT LoggedDate FROM etc, ORDER BY 1'.

The second combo box should have 'SELECT DISTINCT LogID From Table WHERE LoggedDate = ' & ComboBox1.

Then place a COMBOBOX2.REQUERY in the ComboBox1_AfterUpdate event - you're done!

/Cy

 

[oneshadow]

CybOrg, as far as I know combo box in VB doesn't have AfterUpdate event, only Access has it.

But didn't VBVines makes VB as front end, Access as back end? Am I miss understanding the problem?s-)

 

[VBVines]

Man, first of all I appreciate you guys jumping in and helping out like this second this is just one combo box. I am not looking for a selectable list of Logid's, that field is the index (Primary Key)The selectable list is the LoggedDate list. Right now when I drop the combo box I see something simular to this:
Dec-01
Dec-01
Dec-01
Jan-01
Jan-01
feb-01
feb-01
feb-01
I would like to see
Dec-01
Jan-01
Feb-01 aspvbwannab

 

[oneshadow]

Since you can have same loggeddate but different logID, there's now way you can use DISTINCT with those two fields selected. There are 2 options I can figure out.

1. If your need is only to display the loggeddate then let the logID remains in table. You can access it whenever you need it.

2. Point to CybOrg's idea, you'll need two combo boxes to store the loggeddate and logID. The logID combo can stay hide but you may get a little trouble when try to find out whose logID belongs to selected date....:-(

 

[Guest_imported]

Since you don't care for the LOGID, did you try an IF/THEN clause?

ex:

Set rs = conn.Execute("Select * From Clerical_Careers&quot
Do Until rs.EOF
IF Combo1.List(Combo1.ListIndex -1) <> rs(&quot;loggeddate&quot THEN
Combo1.AddItem rs(&quot;loggeddate&quot
Combo1.ItemData(Combo1.NewIndex) = rs(&quot;LogID&quot
rs.MoveNext
END IF
Loop

I know that this isn't not going to work, cause there's more that needs to be written in but...it may do the trick or you could just change

Combo1.ItemData(Combo1.NewIndex) = rs(&quot;LogID&quot
to
Combo1.ItemData(Combo1.NewIndex) = Combo1.ListCount - 1

Try it and write back.

--MiggyD

 

[VBVines]

Thanks OneShadow, CyBorg, MiggyD, I think if I want to do it right I will have to make another table. I should have done that in the first place. Too busy trying to learn ADO and not spending enough time on relational db's. aspvbwannab