Thread: Combo box - list box - Access database

Hi!
Basically I need to create a form that will display a summary of the population trend for each species of butterfly from a database that I have.
I will do this by having the user select which butterfly species he/she wants to view from a combo box and then the list box will display the count_2010 and count_2011 information for that butterfly species.

I have managed to link my combo box to the database but I am unable to get any information to be shown in my list box. I can use a select case and input text myself but I don't want to do that as in another form records need to be added.
Is it possible to have the relevant fields from the access database show up in the list box after the user selects a species from the combo box?
How would I do this?
Also I cannot use the wizard.

Code:

Imports System.Data.OleDb
Module Connection_Module




    Public acsconn As New OleDb.OleDbConnection
    Public acsdr As OleDbDataReader
    Public sql As String
    Sub connect()


        acsconn.ConnectionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0; Data Source = B:\Documents\Second Year\VB\Final\Observation\butterflies.mdb"
        acsconn.Open()


    End Sub
End Module



Public Class Screen2
    Dim ds As New DataSet ' Variable for dataset
    Dim da As OleDb.OleDbDataAdapter ' da variable for my OLEDB data adapter
    Dim Inc As Integer
    Sub fillcombo()


        sql = "select * from Observation"
        Dim acscmd As New OleDb.OleDbCommand
        acscmd.CommandText = sql
        acscmd.Connection = acsconn
        acsdr = acscmd.ExecuteReader
        While (acsdr.Read())
            cmbspec.Items.Add(acsdr("Species"))
            cmloc.Items.Add(acsdr("Loc_ID"))
        End While
        acscmd.Dispose()
        acsdr.Close()


    End Sub
 
    Private Sub Screen2_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Connection_Module.connect()
        Me.fillcombo()
    End Sub




    Private Sub btnexit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnexit.Click
        Me.Close()
    End Sub


    Private Sub ListBox1_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ListBox1.SelectedIndexChanged


    End Sub


    Private Sub cmbspec_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmbspec.SelectedIndexChanged
        ListBox1.Items.Clear()
        Select Case cmbspec.Text


            'Random text for list box currently If i select large blue these items do display! But how can i make it so that its from the database?


            Case "Glanville fritillary"


                ListBox1.Items.Add(("Observation").Item(3)) ' this does not work! 


                ListBox1.Items.Add("1-1/2 cups  BREAKSTONE'S® or KNUDSEN® Sour Cream")


                ListBox1.Items.Add("1 pkg. 1-1/4 oz. TACO BELL® HOME ORIGINALS® Taco Seasoning Mix")








            Case "Large blue"


                ListBox1.Items.Add("1 pkg. (8 oz.) PHILADELPHIA® Cream Cheese, softened")


                ListBox1.Items.Add("1 cup  KRAFT 2% Milk Shredded Italian* Three Cheese Blend ")


                ListBox1.Items.Add("1 pkt. (1 oz.) dry onion soup mix")


                ListBox1.Items.Add("2 Tbsp.  BREAKSTONE'S® Reduced Fat or KNUDSEN® Light Sour Cream")


                ListBox1.Items.Add("2/3 cup finely chopped PLANTERS® Pecans, toasted")




        End Select


    End Sub
















End Class