Populate combo box with only unique values

[jdwyer05]

Hello,

I am trying to populate a combo box with only unique values. Currently I am using an access database and VB6 Enterprise. The program populates the combo box fine however, there are several repeated values in this column. What i would like to do is just show one instance of that particular record.

For example:

if the column has the values: Accounts Payable, Process Control, Shipping, Accounts Payable

i would like the combo box just to list: Accounts Payable, Process Control, Shipping


I have tried several attempts and comparing each record set as they are listed, but that seems to throw me into an infinite loop and the program will crash. Eventually i would like it to work as follows, however if there is another way I am open for suggestions.

While Not rs.EOF
if rs!Column_Name <> "" Then
strData = rs!Column_Name (strData is a string)
Call CheckRepeat (will return a boolean Unique)
if Unique = True Then
Combo1.Additem (strData)
end if
rs.MoveNext
end if
wend

Thanks!!

[sashi]

Hi there,

Use the DISTINCT keyword, all you need is a little SQL knowledge. Take a look at below sample code segment. Good luck & take care.

  Select DISTINCT fldName from tblName
 

Hello,

I am trying to populate a combo box with only unique values. Currently I am using an access database and VB6 Enterprise. The program populates the combo box fine however, there are several repeated values in this column. What i would like to do is just show one instance of that particular record.

For example:

if the column has the values: Accounts Payable, Process Control, Shipping, Accounts Payable

i would like the combo box just to list: Accounts Payable, Process Control, Shipping


I have tried several attempts and comparing each record set as they are listed, but that seems to throw me into an infinite loop and the program will crash. Eventually i would like it to work as follows, however if there is another way I am open for suggestions.

While Not rs.EOF
if rs!Column_Name <> "" Then
strData = rs!Column_Name (strData is a string)
Call CheckRepeat (will return a boolean Unique)
if Unique = True Then
Combo1.Additem (strData)
end if
rs.MoveNext
end if
wend

Thanks!!

[albertw]

hi
or use 'plain' VB

put

Public Const CB_FINDSTRING = &H14C
Public Const CB_FINDSTRINGEXACT = &H158
 
Declare Function SendMessage Lib "user32" Alias "SendMessageA" (ByVal hWnd As Long, ByVal wMsg As Long, ByVal wParam As Long, lParam As Any) As Long
 

in your general declaration

and use

Ret = SendMessage(cboComboBox.hwnd, CB_FINDSTRINGEXACT, -1, strCompare)
if Ret=-1 then cboComboBox.AddItem strCompare
 

where Ret is your ComboBox listindex
and strCompare is the string your are looking for in your ComboBox.

if you get an error-message regarding the called API
change to lParam As String

[Killer42]

... if you get an error-message regarding the called API change to lParam As String

It's been quite a while, but don't you need to define string parms for API calls as ByVal to make VB pass them as null-terminated format?

[willakawill]

Interesting couple of answers.

Sashi gets it handled before it happens while the other is an extremely involved fix after the fact. I would go with DISTINCT every time :)

[Killer42]

Interesting couple of answers.
Sashi gets it handled before it happens while the other is an extremely involved fix after the fact. I would go with DISTINCT every time :)

Depends where the info is coming from, of course.
If you can source just the unique values in the first place, that is obviously preferable to filtering them as you populate the control, however the filtering is done.