Solved

Additem method

Posted on 2002-04-28
16
482 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
Comment Utility
Make sure that NumOfDiseasesFound does contain the correct value. Then do

ComboDeseaseName.AddItem ArrayProbability(Counter)

CJ
0
 

Author Comment

by:ranquest
Comment Utility
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
Comment Utility
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
 
LVL 22

Accepted Solution

by:
CJ_S earned 100 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
;-)
0
 
LVL 12

Expert Comment

by:Paurths
Comment Utility
CJ_S, didn't see your post(s) until mine was submitted...
0
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
LVL 22

Expert Comment

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

CJ
0
 

Author Comment

by:ranquest
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
I suppose you can do combobox.rowsource=""
0
 
LVL 54

Expert Comment

by:nico5038
Comment Utility
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
Comment Utility
thanks it worked
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

744 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now