Solved

Additem method

Posted on 2002-04-28
16
487 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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

820 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