Additem method

i have done some piece of search code for my database and now all i need is to output the search results to a combo box and now i have tried using the additem method and it cant work now is there another way i can be able to output all my search results to the combo box ComboDiseaseName since only one result is appearing there? the results are stored in the array ArrayProbability.

here is the code.

Private Sub search_click()
'On Error GoTo Err_search_Click

    Dim MyData
    Dim MySympt
    Dim MyMed
    Dim MyPrev
    Set MyData = CurrentDb.openrecordset("disease")
    Set MySympt = CurrentDb.openrecordset("Symptoms")
    Set MyMed = CurrentDb.openrecordset("medication")
    Set MyPrev = CurrentDb.openrecordset("prevention")
    'With MyData
    'Do While Not MyData.EOF
    Dim Match1 As String
Dim NumOfSymt As Integer
Dim Counter As Integer
Dim ArraySymtoms(5) As String '5 is max allowed symtomps to search
Dim LastCommaPos 'where we found last comma in symtomps entered
LastCommaPos = 0
NumOfSymt = 0
Counter = 1
Dim SympEntered 'what has been typed in by user
SympEntered = Text7.Text & ","

While Counter <= Len(SympEntered)
    Counter = Counter + 1
    If Mid$(SympEntered, Counter, 1) = "," Then 'Find the position with a comma
        NumOfSymt = NumOfSymt + 1
        ArraySymtoms(NumOfSymt) = Mid$(SympEntered, LastCommaPos + 1, Counter - LastCommaPos - 1)
        LastCommaPos = Counter
    End If

Counter = 0
Dim AllpossibleCases As String
Counter = Counter + 1
    Do While Not MySympt.EOF
        Match1 = MySympt.Fields("Sname1")
            If CBool(InStr(Match1, ArraySymtoms(Counter))) Then
                AllpossibleCases = AllpossibleCases & MySympt.Fields("dname") & "#"
            End If
        If Not MySympt.EOF Then MySympt.MoveNext
Loop Until Counter = NumOfSymt

'Find out which has the highest occurance
Counter = 0
Dim LastHashPos As Integer
Dim DiseasePossible As String 'the disease that has a possilbiliy of having max occurance
Dim ArrayProbability(100) As String
Dim NumOfDiseasesFound As Integer
NumOfDiseasesFound = 0

LastHashPos = 0
While Counter <= Len(AllpossibleCases)
    Counter = Counter + 1
    If Mid$(AllpossibleCases, Counter, 1) = "#" Then 'if u encounter a hash
        DiseasePossible = Mid$(AllpossibleCases, LastHashPos + 1, Counter - LastHashPos - 1)
        LastHashPos = Counter
        Dim DiseasePos As Integer
        Dim EqualSignPos As Integer
        DiseasePos = 1 ' finding the positions of diseases encountered
        Dim DiseaseInProb As Boolean
        DiseaseInProb = False
        While ArrayProbability(DiseasePos) <> ""
            EqualSignPos = InStr(ArrayProbability(DiseasePos), "=")
            If Mid$(ArrayProbability(DiseasePos), 1, EqualSignPos - 1) = DiseasePossible Then
                ArrayProbability(DiseasePos) = DiseasePossible & "=" & CDbl(Mid$(ArrayProbability(DiseasePos), EqualSignPos + 1, (Len(ArrayProbability(DiseasePos)) - EqualSignPos))) + 1
            DiseaseInProb = True ' if false we add to the disease possible
            End If
            DiseasePos = DiseasePos + 1
        If Not DiseaseInProb Then
           NumOfDiseasesFound = NumOfDiseasesFound + 1 'incrementing the number of disease found
           ArrayProbability(NumOfDiseasesFound) = DiseasePossible & "=1" 'count of disease possible
        End If
    End If

Call BubbleSort(ArrayProbability, NumOfDiseasesFound)
For Counter = 1 To NumOfDiseasesFound
 'MsgBox (ArrayProbability(Counter))
'    ComboDiseaseName.ItemData(Counter) = ArrayProbability(Counter)
    'find way of adding everything to combo
    RowSource = "value list"
'    ComboDiseaseName.ItemData(2) = ArrayProbability(Counter + 1)
Next Counter
End Sub

Private Sub BubbleSort(ArrayProbability, NumOfDiseaseFound)

Dim i, j, EqualSignPos1, EqualSignPos2 As Integer
For i = 1 To NumOfDiseaseFound
    For j = 1 To NumOfDiseaseFound
        EqualSignPos1 = InStr(ArrayProbability(j), "=")
        EqualSignPos2 = InStr(ArrayProbability(j + 1), "=")
        If Val(Mid$(ArrayProbability(j), EqualSignPos1 + 1, Len(ArrayProbability(j)) - EqualSignPos1)) < Val(Mid$(ArrayProbability(j + 1), EqualSignPos2 + 1, Len(ArrayProbability(j + 1)) - EqualSignPos2)) Then
            Dim Temp As String
            Temp = ArrayProbability(j)
            ArrayProbability(j) = ArrayProbability(j + 1)
            ArrayProbability(j + 1) = Temp
        End If
    Next j
Next i

End Sub
Who is Participating?
CJ_SConnect With a Mentor Commented:
Maybe the following will work too:

ComboBoxName.RowSourceType = "Value List"
ComboBoxName.RowSource = "Your option;" & ComboBoxName.RowSource

Make sure that NumOfDiseasesFound does contain the correct value. Then do

ComboDeseaseName.AddItem ArrayProbability(Counter)

ranquestAuthor Commented:
hey CJ S

i have since tried using the
ComboDiseaseName.AddItem ArrayProbability(Counter) but i will just get the error that "the method or data member is not found" the problem is the Additem method and iam so sure of it so is there an alternative?

The new generation of project management tools

With’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

Uhm, that's indeed correct. In VB there is an AddItem, while there is not in VBA.

When you add a combobox to the form you have two choices. Either it holds information from the database, or you add some options yourself (hardcoded).

Which way have you chosen? For the first method you only have to add a new record to the table (and it will show up right away in the combobox) for the other choice I do not know.

If the last method works, then I think it will only work if you have specified a query, otherwise not.

there is no AddItem method for a combobox...
only a listbox!

example of filling a combobox

dim strString as String
dim i as Integer

For i =1 to 15
strString = strString & i & ";"
next i

ComboBoxName.RowsourceType = "Value List"
ComboBoxName.Rowsource = strString

so u would use your counter
like this

strString =  strString & ArrayProbability(Counter) & ";"

CJ_S, didn't see your post(s) until mine was submitted...
I'm just glad that my code was verified :-) No worries.

ranquestAuthor Commented:
your last method does work but now it outputs everythimg in a list that is in just one line so now i need to seperate the list so as to take the form of a combo box with a drop down thing how can i do that otherwise so far so good.
You seperate uasing the ;-sign. So, loop through the array. Create the string and after every entry you add the ;

sStr = ""
For i = lbound(yourarray) to ubound(yourarray)
   sStr = sStr & yourarray(i) & ";"


ranquestAuthor Commented:
ok CJS
but before you get the 100 points for the work now is it posssible for one to delist the combo box values every time you runn a programm ?iam talking here of the case similar to the setting like resetting it.

like saying
ComboBoxName = ""

this doesnt work so is there another way?
The best way is to use a temp table.
Just add a loop to insert all values in that temp table and base the combobox on that.
The problem with the valuelist is it's limited length. When you have a lot of hits this can ruin the combobox...

It also saves the trouble of sorting the results as you can place a sort on the desease field.

Need more info ?

I suppose you can do combobox.rowsource=""
Use both:
ComboBoxName.rowsource = ""
ComboBoxName = ""

Thus an already selected value will also be erased.

However, I would go for the temp table appraoch.

ranquestAuthor Commented:
thanks it worked
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.