Friends...A small problem
I have a combobox in which I want to add list from a field from a ACCESS databas Table.It works well
My code is here

ACRS.Open "SELECT * FROM Salary1", CN, adOpenStatic, adLockOptimistic
Sfm = ACRS!Month
Do Until ACRS.EOF
CmbSfm.AddItem ACRS!Month
ACRS.MoveNext
Loop

My problem is , I want to populate the field Month ,But the value of Month for every 4 records will be the same.Hence the combobox list will be repeated 4 times by the value of month. I want only the first one of the four records so that the list will not contain any duplicate entry. Sfm is the variable for storing the field Month.

Edited 2 Years Ago by mavtcr

Just prevent adding the string to the combobox by checking if it is already in the string collection of the combobox like (don't know if it works):

 If ComboBox1.Items.IndexOf(str) = -1 Then
                ComboBox1.Items.Add(str)
            End If

Edited 2 Years Ago by Minimalist: Forgot to insert code snppet

Dear Minimalist....
I modified my code as per your direction like this

 Do Until ACRS.EOF
 Sfm = ACRS!Month
 If CmbSfm.Items.IndexOf(Sfm) = -1 Then
 CmbSfm.Items.Add (Sfm)
 End If
 ACRS.MoveNext
 Loop

But an error message - Method or data member not found-.Items.of if clause
(Line 3) is high lighted

o.K. thought it would work but its vb.net. The following code works, I just tried it. You need to adapt so.

Private Sub Form_Load()
Dim str As String
For i = 0 To 2
str = "Hello"
Debug.Print (Combo1.ListCount)
For j = 0 To Combo1.ListCount - 1
If str = CStr(Combo1.List(j)) Then
MsgBox ("Double Entries not allowed")
Exit Sub
End If
Next
Combo1.AddItem str
Next
End Sub

Dear Minimalist ..
I adapted your suggestion. It works ,but only for the first four records Where the values are same. Here the first record from the first set of ,say 4 records, having the same value for Sfm is selected. No record is selected further.I want the first record from each group of records having same value for Sfm.ie. If I am having alltogether 20 records (four records having same value for Sfm in each set) ,I should get 5 items in the combobox

But when I use your code I am getting only one item(Only The first).I think you got it....

Edited 2 Years Ago by mavtcr

Well, you need to adopt the code to what you need. Ishowed you haw to prevent double entries. Since I used only one word, the first time it is added to the combo, after I exit the sub in line 9. You need to put your code there to handle it - means you can't exit the sub.

I am confused.. No success ... can you modify my code

Private Sub ShowSfm()
    Dim i, j As Integer
    Dim str As String
     For i = 0 To 2
    Set ACRS = New ADODB.Recordset
  If ACRS.State = adStateOpen Then ACRS.Close
 ACRS.Open "SELECT * FROM Salary1", CN, adOpenStatic, adLockOptimistic
    'Sfm = ACRS!Month
     str = ACRS!Month
    Debug.Print (CmbSfm.ListCount)
    For j = 0 To CmbSfm.ListCount - 1
     If str = CStr(CmbSfm.List(j)) Then
      MsgBox ("Double Entries not allowed")
      Exit Sub
    End If
    Next
    CmbSfm.AddItem str
    Next
    End Sub

Sfm is different months of a year

O.K I just go back to the original post as this seemed to work and change it to:

ACRS.Open "SELECT * FROM Salary1", CN, adOpenStatic, adLockOptimistic
dim j as integer 'need to count items in combo
Sfm = ACRS!Month
Do Until ACRS.EOF
  For j = 0 To CmbSfm.ListCount - 1 'each item check against all items in the combo
   If Sfm = CStr(CmbSfm.List(j)) Then
      MsgBox ("Double Entries not allowed")
     else
     CmbSfm.AddItem ACRS!Month
    End If
ACRS.MoveNext
Loop

Oh dear ... I just copied the code and tried, Then there is an error message
"Loop without Do" I don't know why. Do is already there.My doubt is that there is no 'Next' in the For loop.I tested after inserting 'Next' after 'Endif'. But then the combobox is blank.Nothing is populated. I tried 'Next'
just before 'Loop'. Then the programm hangs
What to do now?

Well, you can't just copy my code into your program before thinking about or trying it on its own. If you copy the code I give you now into a small testing program as it is it will work. You need to modify to your needs so.

Private Sub Form_Load()
Dim str As String
Dim ar(5) As String
ar(0) = "apple"
ar(1) = "banana"
ar(2) = "plum"
ar(3) = "apple"
ar(4) = "plum"

Debug.Print (Combo1.ListCount)
For i = 0 To 4
    str = ar(i)
    For j = 0 To Combo1.ListCount - 1
        If str = CStr(Combo1.List(j)) Then
        MsgBox ("Double Entries not allowed")
        Exit For
        End If
    Next
    If str <> CStr(Combo1.List(j)) Then
        Combo1.AddItem str
    End If
Next
End Sub

Oh Dear.... I tried and tried ..but no success..When ar is a fixed number it is OK. But in my case the number is unknown it is derived from the table.
I tried another way,instead of using existing table I created another table where the Sfm- field is not duplicated . So my task is fulfilled indirectly
Any way thank you so much for spending your valuable time for my silly questions...Thank you.. Thank you..

if you got a solution please mark the thread as solved. Also, you wouldn't need my array because you are feeding the values from your database. Also check out dynamic arrays and how to resize these without loosing data.

Edited 2 Years Ago by Minimalist

This question has already been answered. Start a new discussion instead.