Combo box event to select from Access database

[di.miller]

Hi,

I am using VB 2008 Express to connect to an Access 2007 database. I have a combo box called SelectYearMonth that is populated by the database. When I used the Access report VBA, I used the AfterUpdate() event to calculate a sum. I need to know how to do the same operation in VB 2008 Express using the SelectedIndexChanged event of the combo box.

When I made a report in Access using VBA I coded my calculation as:

Me.ExactReprintTextbox = DSum("Pages", "HBELP_FINAL_Q", "YEARMONTH = " & [SelectYearMonth] & " AND JOBTYPE = 'E'")

While this works great with Access VBA, this syntax does not work in VB 2008 Express.

How do I code this in VB 2008 Express?

I need to calculate the sum of all "Pages" of a particular "JOBTYPE" in the Access database for the YEARMONTH selected in the combo box.

I have a query in Access called "JobTypeExact_Query" that contains all the pages of that particular JOBTYPE that I need. What I need to do is limit the calculation results to the YEARMONTH selected in the combo box.

Any help is greatly appreciated!

-Diana

[Eekhoorn]

Hummmmmmmm, I'm not very well aquainted with vba, but I do know VB.net is a bit on its own. So first you need to make a connection to your database, then you need to querrie it, put all the stuf in a coockiejar (or dataset) and then look what you've got and do your thing.

somthing like,

Public Sub ComboBox1_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles ComboBox1.SelectedIndexChanged

        If ComboBox1.SelectedIndex = Nothing Then  'If database doesn't contain anything
            MsgBox(" No data avaiable ! ", MsgBoxStyle.Exclamation)
            InvoerFormulier.Show()
            Me.Hide()
            Exit Sub
        Else
            Naam = *specific datatabel name* 'Hier komt de string terug uit de eerste module. Klaar voor de tweede filter actie
            ComboBox2.Items.Clear()
            If ds.Tables.Contains("Continental breakfast") Then
                ds.Tables("Continental breakfast").Rows.Clear()
            End If

           

            con.ConnectionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source = 'C:\Users\****\Documents\****\Database.mdb"
            con.Open()

            Zoekvraag1 = "SELECT tblDeelsysteem.Subsyteem FROM tblDeelsysteem WHERE Machinenaam = '" & Naam & "'; "  '"SELECT * FROM tblDeelsysteem WHERE Machinenaam = '" & Naam & "'; "
            'TextBox1.Text = Zoekvraag1
            sql2 = Zoekvraag1
            da = New OleDb.OleDbDataAdapter(sql2, con)
            da.Fill(ds, "Continental breakfast")

            If ds.HasChanges Then
                da.Update(ds)
            End If

            con.Close()

            maxrows = ds.Tables("Continental breakfast").Rows.Count
            incA = -1

            If maxrows = 0 Then
                MsgBox("No data avaiable", MsgBoxStyle.Exclamation)
                ComboBox1.Items.Add(" ")
                InvoerFormulier.Show()
                Me.Hide()
                Exit Sub
            Else
                For i = -1 To maxrows - 2
                    incA = i + 1
                    Subsysteem = ds.Tables("Continental breakfast").Rows(incA).Item(0)
                    ComboBox2.Items.Add(Subsysteem) ' fill into combobox
                Next i
            End If
            ComboBox2.SelectedIndex = 0
        End If

    End Sub

After this you need to make a second sub to do your basic calculating and drop the answer in a textbox on your form, or so.

More information on coding in vb.net http://www.homeandlearn.co.uk/net/vbNet.html
More information on sql, which will help you very much in your errand http://www.semeleer.nl/

[di.miller]

Hi,

I already have the connection to the database and the query in place. All I need is to perform the calculation based on the date chosen in the combo box and return the results to my text box. This is where I am having troubles, calculating/coding the results to go into my textbox, limiting the results from the query to the date specified in the combo box.

Thanks,
Diana

[Eekhoorn]

Last night when I stepped into my bed, I realised I were a bit to quick in answering you, which is of no help to anyone. Sorry for that.

But let me resume; You first start with a combobox wich only contains al the yaermonth's you already have in your database. This information comes to you by help of the friendly giant sql who was so nice to put this in a dataset (coockiejar). From this dataset you filled your combobox.

So far so good.
All this happend by means of your form_load or Push_button action.

Now for the second part: you pick a value from your combo and select it. This triggers the public function (I called it NavigateRecords, the same as the guy from "stay home and learn", from which I learned this litle trick) which selects from your first dataset the name of the YEARMONTH you wish to filter by.

This name is given back to your selected_index_changed routine where you use it for a second filtered querrie. You don't want empty your whole database in your computer, because first of all its useless, second of all; your memorey will wear down very rapidly.
So you filter by means of specified querries and only obtain the neccesary information.
Afer this you loop around, summing all your data and there you are.

Public Class Form1
    Dim Jobtype, Result As Double
    Dim maxrows, inc, incA As Integer
    Dim sqlstring, Sql2 As String
    Dim con As New OleDb.OleDbConnection
    Dim ds As New DataSet
    Dim da As OleDb.OleDbDataAdapter

    Public Sub ComboBox1_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles ComboBox1.SelectedIndexChanged

        If ComboBox1.SelectedIndex = Nothing Then
            MsgBox(" No data available! ", MsgBoxStyle.Exclamation)
            Exit Sub
        Else
            Name = NavigateRecords() 'Here the string comes back from the  public function 
            If ds.Tables.Contains("Continental breakfast") Then     'Clear your memory, or all the old information will turn up again and again 
                ds.Tables("Continental breakfast").Rows.Clear()
            End If



            con.ConnectionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source ='C:\Users\*****\Documents\****\***\Database.mdb"
            con.Open()

            sqlstring = "SELECT tbl****.Jobtype FROM tbl***** WHERE YEARMONTH = '" & Name & "'; "  'filter your information by means of a querrie Name is the keyw ord t of ilter by

            Sql2 = sqlstring
            da = New OleDb.OleDbDataAdapter(Sql2, con)  'prepare the coockie jar
            da.Fill(ds, "Continental breakfast")        'fill the coockie jar

            If ds.HasChanges Then                       'extra 
                da.Update(ds)
            End If

            con.Close()

            maxrows = ds.Tables("Continental breakfast").Rows.Count    'this piece of code is not very usefull for your problem becuase it should contain just one row of information per colum
            incA = -1                                                  'but I might as well be mistaken and then it might be very usefull. But it counts all the coockies in your jar.

            If maxrows = 0 Then
                MsgBox(" No data available! ", MsgBoxStyle.Exclamation)
                Exit Sub
            Else
                For i = -1 To maxrows - 2
                    incA = i + 1
                    Jobtype = ds.Tables("Continental breakfast").Rows(incA).Item(0)
                    Result = Jobtype + Jobtype
                Next i
            End If

        End If

        TextBox1.Text = Jobtype

    End Sub

    Public Function NavigateRecords()
        inc = ComboBox1.SelectedIndex()
        Name = ds.Tables("Bacon Sandwich").Rows(inc).Item(0)
        Return Name
    End Function
End Class

[Eekhoorn]

Oh oh, I forgot to tell something; the dataset in in the public function NavigateRecords points to your first dataset, which only contains all your yearmonths.
Thats handy, I think.

Goodknight and happy luck to you.

[Eekhoorn]

Scheise, one litle typo

Textbox1.Text = Result

[di.miller]

Hi Eekhoorn,

Maybe I haven't made my problem very clear. The "Pages" for each record is a unique value that I need to add together. One record may have 352 "Pages" and another may have 196 "Pages". So, I need to add/sum the total actual amount of unique "Pages" for the YearMonth selected in the combo box as opposed to incrementally counting the number of records.

The Combo box is named "cboYEARMONTH" and is filled by the query:

SELECT YEARMONTH FROM YearMonth_Query

I have a data table adapter for the JobTypeExact_Query:

Me.JobTypeExact_QueryTableAdapter.Fill(Me.Operation_Emp_PagesDataSet.JobTypeExact_Query)

So, I am thinking In the cboYEARMONTH_SelectedIndexChanged event I need somehow to:

SELECT Pages FROM JobTypeExact_Query WHERE YEARMONTH = cboYEARMONTH.SelectedItem

Then calculate a SUM of the Pages and output it to the text box:

Me.ExactPagesTextbox =

Does this make my problem clearer?

I truly appreciate you taking the time to help me with this!
-Diana ;o)

[Eekhoorn]

Hi Diana,

I'm sorrie, but I had to do some overtime today, so I don't have much energie left.
I'll try to get back tou you by tommorow.

There just one question that seems rather urgent to me. How are the page values stored in your database, Because I have a bit a problem to get the picture.

[di.miller]

I found the answer on another forum. Here it is. I hope someone finds it useful:

Private Sub cboYEARMONTH_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cboYEARMONTH.SelectedIndexChanged
        If cboYEARMONTH.SelectedValue Is Nothing Then Exit Sub 'Error handling

        Dim ExactQuery As String = _
            "SELECT * " & _
            "FROM[JobTypeExact_Query] WHERE (YEARMONTH = ?)" 'Define Exact job type query

 ExactQuery = ExactQuery.Replace("?", CStr(cboYEARMONTH.SelectedValue)) 'Constrain to YEARMONTH chosen

Dim Connection As OleDb.OleDbConnection = _
        New OleDb.OleDbConnection(My.Settings.YourConnectionStringHere)

        Dim Command As New OleDb.OleDbCommand

        Dim ds As New DataSet
        Dim da As New OleDb.OleDbDataAdapter
        Dim ExactPages As Integer

Connection.Open()
        Command.Connection = Connection

        '''''This Section Calculates the number of pages by Job Type'''''

        da.SelectCommand = New OleDb.OleDbCommand(ExactQuery, Connection)
        da.Fill(ds, "ExactTable") 'Table is just a locally defined name

        If ds.Tables("ExactTable").Rows.Count <> 0 Then
            'Calculate Exact Pages

            For Each dr As System.Data.DataRow In ds.Tables("ExactTable").Rows
                ExactPages += CInt(dr.Item("Pages"))
            Next
        End If

        ExactPagesTextbox.Text = CStr(ExactPages) 'Display Exact Pages Count

[di.miller]

duplicate post. Sorry!