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

 

[di.miller]

I found the answer on another forum. Here it is, I hope this helps someone:

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

My thanks to TnTinMN at the Microsoft Forums (social.msdn.microsoft.com) for helping me with this problem.