Click here to Skip to main content
15,920,633 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I have three tables in my MS ACCESS database clients,products and sales.the fields of the table clients are clientid,name,address,phoneno
products are productid, name, unitprice
sales are salesid, fkclientid,fkproductid,salesdate,quantity. FK means foreign key.

this tables are connected to visual basic.

now i want a sales form where i record the sales transactions.
a combobox in the sales form which shows the productlist and its unitprice
and a calculated field subtotal where unitprice*quantity
Posted
Comments
OriginalGriff 14-Feb-14 7:41am    
And?
What have you tried?
Where are you stuck?

1 solution

Imports System.Data.OleDb
Public Class Form2


    Public oCon As New System.Data.OleDb.OleDbConnection("provider=microsoft.jet.OLEDB.4.0;data source=" & Application.StartupPath & "\Data\mydata.mdb")
    Public Trans As OleDbTransaction
    Dim oCom As New OleDbCommand


    Private Sub Form2_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        fillCombo(Me.cmb_products, "productid", "pro_name", "products")
    End Sub

    Private Sub cmb_products_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmb_products.SelectedIndexChanged
        Me.txt_unitprice.Text = GetValueByQuery("select unitprice from products where productid='" & Get_ComboID(Me.cmb_products) & "'")
    End Sub

    Private Sub txt_quantity_KeyPress(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles txt_quantity.KeyPress
        Check_Digit(e)
    End Sub

    Private Sub txt_quantity_TextChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles txt_quantity.TextChanged
        If Me.txt_quantity.TextLength > 0 Then
            Me.txt_totalprice.Text = Val(Me.txt_unitprice.Text) * Val(Me.txt_quantity.Text)
        Else
            Me.txt_totalprice.Clear()
        End If
    End Sub

    Public Sub fillCombo(ByVal cmb As ComboBox, ByVal tabid As String, ByVal tabVal As String, ByVal mytable As String)
        Dim str As String
        Dim ocom As New OleDbCommand
        Dim oRead As OleDbDataReader = Nothing
        str = "select " & tabid & "," & tabVal & " from " & mytable & ""
        ocom.CommandText = str
        If oCon.State = ConnectionState.Closed Then
            oCon.Open()
        End If
        ocom.Connection = oCon
        Try
            oRead = ocom.ExecuteReader
            str = ""
            cmb.Items.Clear()
            While oRead.Read()
                cmb.Items.Add(New ValueDescriptionPair(oRead(0), oRead(1)))
            End While
        Catch ex As Exception
            MsgBox(ex.Message)
        Finally
            oRead.Close()
            oCon.Close()
        End Try
    End Sub

    Function GetValueByQuery(ByVal Query As String) As String
        Dim temp As String
        Dim ocom As New OleDbCommand
        Dim oRead As OleDbDataReader
        If oCon.State = ConnectionState.Closed Then
            oCon.Open()
        End If

        ocom.Connection = oCon
        ocom.CommandText = Query

        oRead = ocom.ExecuteReader
        If oRead.HasRows = True Then
            oRead.Read()
            If IsDBNull(oRead(0)) = True Then
                temp = "0"
            Else
                temp = oRead(0)
            End If
            oRead.Close()

        Else
            temp = "0"
            oRead.Close()
        End If
        Return temp
    End Function
End Class
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900