Solved

Additem method

Posted on 2002-04-28
16
486 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
  • 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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
LVL 22

Accepted Solution

by:
CJ_S earned 100 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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

831 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