[RESOLVED] Prevent duplicates in Combo

[mel_flynn]

Ok, I am loading data to a form from an AccessDB via ADODB. While I am only displaying the first record, I want to populate a couple of Combo boxs also.

rsTable.Open "SELECT * FROM Table ORDER BY ID", CN,adOpenForwardOnly, adLockReadOnly
txtID = rsTable!ID
txtText1 = rsTable!Text1
etc

Do While Not rsTable.EOF
cboFName = rsTable!FName
cboSurname = rsTable!Surname
etc
rsTable.MoveNext
Loop

However this is causing duplicate entries in my Combo boxes, if there is more than one "Ann" in the database. Will have have to open a separate DISTINCT recordset to populate the combo boxes, or can I just check if "Ann" is already in the Combo?

Thanks in advance

[Hack]

Private Declare Function SendMessage Lib "user32" Alias "SendMessageA" _
(ByVal hwnd As Long, ByVal wMsg As Long, ByVal wParam As Long, _
lParam As Any) As Long

Private Const CB_FINDSTRING = &H14C

Private Sub AddToCombobox(pstrComboBItem As String, pCB As ComboBox)
 Dim lngComboIndex As Long
    ' lngComboIndex is the ComboIndex if the item is found
    lngComboIndex = SendMessage(pCB.hwnd, CB_FINDSTRING, -1, ByVal pstrComboBItem)
    If lngComboIndex = -1 Then
        pCB.AddItem strComboBItem
    Else
        MsgBox pstrComboBItem & " is already in the Combobox"
    End If
End Sub

Private Sub Command1_Click()
AddToCombobox "Ann", Combo1
End Sub

[RobDog888]

Its better logic to prevent the recordset from containing any duplicates. Since the user is not manually entering in values it should suffice.

rsTable.Open "SELECT DISTINCT * FROM Table ORDER BY ID", CN,adOpenForwardOnly, adLockReadOnly

That should eliminate your dups depending on yor table data.

[mel_flynn]

Thanks Hack will give it a go.
RobDog888, I see your point, however there may be "Ann Ryan"; "Ann Harris"; "Ann Sullivan"; "Tony Harris" etc in the database, and all be classified as Distinct records even though there would be duplicate first names and surnames.

[leinad31]

Thanks Hack will give it a go.
RobDog888, I see your point, however there may be "Ann Ryan"; "Ann Harris"; "Ann Sullivan"; "Tony Harris" etc in the database, and all be classified as Distinct records even though there would be duplicate first names and surnames.

Then the duplication is caused by other columns in the query, or the entire row is distinct because of primary key column. You should specify the columns to be returned.

rsTable.Open "SELECT DISTINCT FName, Surname FROM Table ORDER BY ID", CN,adOpenForwardOnly, adLockReadOnly

[RobDog888]

"Ann Ryan"; "Ann Harris"; "Ann Sullivan"; "Tony Harris

These would not be considered duplicates.

[Hack]

Perhaps not by us, but it is for his app it would seem.

[RobDog888]

Well going by his post it seems that the design may be flawed as hes saying people with "Ann" as a first name is a dup. when clearly having a duplicate first name is not a primary key. If that was true we all would be duplicates as there is like 6 billion people and you know there is a few with the same first names.