?
Solved

Additem method

Posted on 2002-04-28
16
Medium Priority
?
493 Views
Last Modified: 2006-11-17
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
    'Loop
    Text7.SetFocus
   
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
   
Wend

Counter = 0
Dim AllpossibleCases As String
Do
MySympt.MoveFirst
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
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
        Wend
       
        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
    Wend

Call BubbleSort(ArrayProbability, NumOfDiseasesFound)
'MSComDlg.ShowSave
ActiveXCtl63.Object
For Counter = 1 To NumOfDiseasesFound
 'MsgBox (ArrayProbability(Counter))
'    ComboDiseaseName.ItemData(Counter) = ArrayProbability(Counter)
'    ComboDiseaseName.ad
    '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
0
Comment
Question by:ranquest
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 4
  • 2
  • +1
16 Comments
 
LVL 22

Expert Comment

by:CJ_S
ID: 6974698
Make sure that NumOfDiseasesFound does contain the correct value. Then do

ComboDeseaseName.AddItem ArrayProbability(Counter)

CJ
0
 

Author Comment

by:ranquest
ID: 6974729
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?

0
 
LVL 22

Expert Comment

by:CJ_S
ID: 6974750
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.

CJ
0
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 
LVL 22

Accepted Solution

by:
CJ_S earned 400 total points
ID: 6974752
Maybe the following will work too:

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

CJ
0
 
LVL 22

Expert Comment

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

CJ
0
 
LVL 12

Expert Comment

by:Paurths
ID: 6974755
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) & ";"


cheers
Ricky
0
 
LVL 22

Expert Comment

by:CJ_S
ID: 6974757
;-)
0
 
LVL 12

Expert Comment

by:Paurths
ID: 6974759
CJ_S, didn't see your post(s) until mine was submitted...
0
 
LVL 22

Expert Comment

by:CJ_S
ID: 6974762
I'm just glad that my code was verified :-) No worries.

CJ
0
 

Author Comment

by:ranquest
ID: 6974774
CJ S
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.
0
 
LVL 22

Expert Comment

by:CJ_S
ID: 6974779
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) & ";"
next

CJ

0
 

Author Comment

by:ranquest
ID: 6974859
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.SetFocus
ComboBoxName = ""

this doesnt work so is there another way?
0
 
LVL 54

Expert Comment

by:nico5038
ID: 6974946
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 ?

Nic;o)
0
 
LVL 22

Expert Comment

by:CJ_S
ID: 6974965
I suppose you can do combobox.rowsource=""
0
 
LVL 54

Expert Comment

by:nico5038
ID: 6975005
Use both:
ComboBoxName.rowsource = ""
ComboBoxName = ""

Thus an already selected value will also be erased.

However, I would go for the temp table appraoch.

Nic;o)
0
 

Author Comment

by:ranquest
ID: 6983135
thanks it worked
0

Featured Post

New benefit for Premium Members - Upgrade now!

Ready to get started with anonymous questions today? It's easy! Learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Suggested Courses

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question