Solved

MS Access and VB.NET creating forms

Posted on 2006-07-05
4
307 Views
Last Modified: 2010-04-23
So i need a little direction to create a form with in VB.net to access a ms access database. Could someone point me in a simple way to doing this? What about making it so that it calculates inventory? If someone already has something in mind in creating a database with inventory items and to take it out with a account number and the qty used, that would be great. Would also like to create reports!
0
Comment
Question by:steverpayne
  • 2
4 Comments
 
LVL 8

Expert Comment

by:bramsquad
Comment Utility
well, its actually pretty easy to do so.  however, youre going to need to study up on ado.net to access your database.  this will give you whatever functionality you need.  for example you can create, delete, and manipulate tables to your hearts content.

check this site out

http://www.devarticles.com/c/b/ADO.NET/

creating reports however is a little different.  you need software to do that, like crystal reports (and if you have visual studio professional it comes with it)  the only other option ive found is when i used to have to create reports in excel.  that way however is a huge pain.

any other questions let me know.

~b
0
 
LVL 27

Accepted Solution

by:
planocz earned 500 total points
Comment Utility
Hi steverpayne,

Here is a sample that uses an access DB, Hope it helps....


'FORM 1

Imports System.IO
Public Class frmADOToolTip
    Inherits System.Windows.Forms.Form
    Dim sField As String = String.Empty

#Region " Windows Form Designer generated code "

    Public Sub New()
        MyBase.New()

        'This call is required by the Windows Form Designer.
        InitializeComponent()

        'Add any initialization after the InitializeComponent() call
        InitializeData()
        PopulateListFromDS(DS, "ProductName")
        PrintRows(DS)
    End Sub

    'Form overrides dispose to clean up the component list.
    Protected Overloads Overrides Sub Dispose(ByVal disposing As Boolean)
        If disposing Then
            If Not (components Is Nothing) Then
                components.Dispose()
            End If
        End If
        MyBase.Dispose(disposing)
    End Sub

    'Required by the Windows Form Designer
    Private components As System.ComponentModel.IContainer

    'NOTE: The following procedure is required by the Windows Form Designer
    'It can be modified using the Windows Form Designer.  
    'Do not modify it using the code editor.
    Friend WithEvents tipView As System.Windows.Forms.ToolTip
    Friend WithEvents lstBoxResults As System.Windows.Forms.ListBox
    Friend WithEvents RadioButton1 As System.Windows.Forms.RadioButton
    Friend WithEvents RadioButton2 As System.Windows.Forms.RadioButton
    Friend WithEvents RadioButton3 As System.Windows.Forms.RadioButton
    <System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()
        Me.components = New System.ComponentModel.Container
        Me.lstBoxResults = New System.Windows.Forms.ListBox
        Me.tipView = New System.Windows.Forms.ToolTip(Me.components)
        Me.RadioButton1 = New System.Windows.Forms.RadioButton
        Me.RadioButton2 = New System.Windows.Forms.RadioButton
        Me.RadioButton3 = New System.Windows.Forms.RadioButton
        Me.SuspendLayout()
        '
        'lstBoxResults
        '
        Me.lstBoxResults.Location = New System.Drawing.Point(8, 8)
        Me.lstBoxResults.Name = "lstBoxResults"
        Me.lstBoxResults.Size = New System.Drawing.Size(176, 82)
        Me.lstBoxResults.TabIndex = 0
        Me.tipView.SetToolTip(Me.lstBoxResults, "Click to test the tool tip")
        '
        'RadioButton1
        '
        Me.RadioButton1.Location = New System.Drawing.Point(204, 4)
        Me.RadioButton1.Name = "RadioButton1"
        Me.RadioButton1.TabIndex = 1
        Me.RadioButton1.Text = "By Unit Price"
        '
        'RadioButton2
        '
        Me.RadioButton2.Location = New System.Drawing.Point(204, 32)
        Me.RadioButton2.Name = "RadioButton2"
        Me.RadioButton2.Size = New System.Drawing.Size(120, 24)
        Me.RadioButton2.TabIndex = 2
        Me.RadioButton2.Text = "By Units In Stock"
        '
        'RadioButton3
        '
        Me.RadioButton3.Location = New System.Drawing.Point(204, 60)
        Me.RadioButton3.Name = "RadioButton3"
        Me.RadioButton3.Size = New System.Drawing.Size(120, 24)
        Me.RadioButton3.TabIndex = 3
        Me.RadioButton3.Text = "By Product Name"
        '
        'frmADOToolTip
        '
        Me.AutoScaleBaseSize = New System.Drawing.Size(5, 13)
        Me.ClientSize = New System.Drawing.Size(332, 117)
        Me.Controls.Add(Me.RadioButton3)
        Me.Controls.Add(Me.RadioButton2)
        Me.Controls.Add(Me.RadioButton1)
        Me.Controls.Add(Me.lstBoxResults)
        Me.Name = "frmADOToolTip"
        Me.Text = "ADO ToolTip Test"
        Me.ResumeLayout(False)

    End Sub

#End Region
    '/ <summary>
    '/ The main entry point for the application.
    '/ </summary>
    <STAThread()> Public Shared Sub Main()
        Application.Run(New frmADOToolTip)
    End Sub
    Private Sub InitializeData()

        Cursor.Current = Cursors.WaitCursor
        sDBName = "D:\Northwind.mdb"       '<---- A standard access database from Microsoft
        sTableName = "Products"

        OpenDBADOConnection()
 
        sSql = ""
        sSql = "SELECT * "
        sSql += "FROM " & sTableName & " "
        sSql += "ORDER BY ProductName"
        Try
            'TblAdapter = New SqlDataAdapter(sSql, Connect)            'FOR SQL Server
            ADOAdapter = New OleDb.OleDbDataAdapter(sSql, Con)         'FOR ADO Access database
            ADOAdapter.Fill(DS)

        Catch Exp As Data.OleDb.OleDbException
            MsgBox("FillDataSet Procedure Error", MsgBoxStyle.Critical, "Load Report Error")
        Catch Exp As Exception
            MsgBox(Exp.Message, MsgBoxStyle.Critical, "General Error")
        End Try
        Cursor.Current = Cursors.Default
    End Sub
    Private Sub lstboxResults_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles lstBoxResults.Click
        Dim sCost As String
        Dim sUnits As String
        Dim str As String
        Select Case True
            Case RadioButton1.Checked      'By Unit Price
                sCost = DS.Tables(0).Rows(sender.selectedindex)("ProductName").ToString()
                sUnits = DS.Tables(0).Rows(sender.selectedindex)("UnitsInStock").ToString()
                str = "Product Name: " & sCost & vbCr & "Unit In Stock: " & sUnits
            Case RadioButton2.Checked      'By Units In Stock
                sCost = DS.Tables(0).Rows(sender.selectedindex)("ProductName").ToString()
                sUnits = DS.Tables(0).Rows(sender.selectedindex)("Unitprice").ToString()
                str = "Product Name: " & sCost & vbCr & "Cost: " & Format(CDbl(sUnits), "$###.00")
            Case RadioButton3.Checked      'By Product Name
                sCost = DS.Tables(0).Rows(sender.selectedindex)("Unitprice").ToString()
                sUnits = DS.Tables(0).Rows(sender.selectedindex)("UnitsInStock").ToString()
                str = "Cost: " & Format(CDbl(sCost), "$###.00") & vbCr & "Unit In Stock: " & sUnits
            Case Else
                sCost = DS.Tables(0).Rows(sender.selectedindex)("Unitprice").ToString()
                sUnits = DS.Tables(0).Rows(sender.selectedindex)("UnitsInStock").ToString()
                str = "Cost: " & Format(CDbl(sCost), "$###.00") & vbCr & "Unit In Stock: " & sUnits
        End Select

        tipView.SetToolTip(Me.lstBoxResults, str)
    End Sub
    Private Sub PopulateListFromDS(ByVal DS As DataSet, ByVal sFieldName As String)
        ' A table in a dataset is used to populate a list box with
        ' product name from the products table.
        ' With the dataset the name of the table and field must be known
        ' at design time. If they are misspelled or mistyped
        ' an error will be generated only at runtime.

        Dim s As String
        Dim i As Integer

        lstBoxResults.Items.Clear()

        For i = 0 To DS.Tables(0).Rows.Count - 1
            ' Check to see if row is flagged as deleted.
            If Not DS.Tables(0).Rows(i).RowState = DataRowState.Deleted Then
                ' Get the product name for each record.
                s = DS.Tables(0).Rows(i)(sFieldName).ToString()
                ' Add product name to the list box
                lstBoxResults.Items.Add(s)
            End If
        Next i
    End Sub
   
    Private Sub PrintRows(ByVal DS As DataSet)
        Dim i As Integer
        Dim ProductName As String
        Dim SupplierID As String
        Dim CategoryID As String
        Dim UnitPrice As String
        Dim UnitsInStock As String
        Dim UnitsOnOrder As String
        Dim ProductID As String

        Dim objWriter As StreamWriter = New StreamWriter("C:/Products.txt", False)
        objWriter.Write("Products as of " & Today & vbNewLine & vbNewLine)
        For i = 0 To DS.Tables(0).Rows.Count - 1
            ' Check to see if row is flagged as deleted.
            If Not DS.Tables(0).Rows(i).RowState = DataRowState.Deleted Then
                ' Get the  name for each record.
                ProductName = DS.Tables(0).Rows(i)("ProductName").ToString()
                SupplierID = DS.Tables(0).Rows(i)("SupplierID").ToString()
                CategoryID = DS.Tables(0).Rows(i)("CategoryID").ToString()
                UnitPrice = DS.Tables(0).Rows(i)("UnitPrice").ToString()
                UnitsInStock = DS.Tables(0).Rows(i)("UnitsInStock").ToString()
                UnitsOnOrder = DS.Tables(0).Rows(i)("UnitsOnOrder").ToString()
                ProductID = DS.Tables(0).Rows(i)("ProductID").ToString()
                objWriter.Write("ProductName: " & ProductName & ", Supplier ID: " & SupplierID & vbNewLine)
                objWriter.Write("Category ID: " & CategoryID & vbNewLine)
                objWriter.Write("Unit Price: " & UnitPrice & vbNewLine)
                objWriter.Write("Units In Stock: " & UnitsInStock & ", Units On Order: " & UnitsOnOrder & ", Product ID: " & ProductID & vbNewLine)
            End If
        Next
        objWriter.Close()
        objWriter = Nothing
    End Sub
    Private Sub RadioButtonChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles RadioButton1.CheckedChanged, RadioButton2.CheckedChanged, RadioButton3.CheckedChanged
        Select Case sender.text
            Case "By Unit Price"
                sField = "Unitprice"
            Case "By Units In Stock"
                sField = "UnitsInStock"
            Case "By Product Name"
                sField = "ProductName"
        End Select
        PopulateListFromDS(DS, sField)
    End Sub
End Class



'MODULE

Imports System.Data
Imports System.Data.OleDb
Module ModADO
#Region " MY Global Vars "
    'Const adOpenStatic = 3
    'Const adLockOptimistic = 3
    'Const adCmdText = &H1
    '********* database Var's ************
    Public sSql As String
    Public sTableName As String
    Public iCounter As Short
    Public myDataRow As DataRow
    Public DS As New DataSet
    Public ADOAdapter As OleDb.OleDbDataAdapter  '(ADO Access)

    '**** Server Connection Vars *********
    Public sDBServer As String               '<--- Server Name
    Public sUSERid As String                 '<--- User Id
    Public sDBName As String                 '<--- Database Name
    Public sDBPass As String                 '<--- Password
    Public Con As OleDb.OleDbConnection      '<--- for Access

    '******* Standard Var's *************
    Public sAppPath As String = System.Windows.Forms.Application.StartupPath
    Public bLogin As Boolean
#End Region
#Region " MY Server Connections "

    '***Sample for a Access Database***
    'Provider=Microsoft.Jet.OLEDB.4.0;
    'User ID=Admin;
    'Data Source=D:\MyDatabase.mdb;
    'Mode=Share Deny None;
    'Extended Properties="";
    'Locale Identifier=1033;
    'Persist Security Info=False;
    'Jet OLEDB:System database=D:\SYSTEM.MDW;
    'Jet OLEDB:Registry Path="";
    'Jet OLEDB:Database Password="";
    'Jet OLEDB:Engine Type=5;         'Type=4 is for Access 95
    'Jet OLEDB:Database Locking Mode=1;
    'Jet OLEDB:Global Partial Bulk Ops=2;
    'Jet OLEDB:Global Bulk Transactions=1;
    'Jet OLEDB:New Database Password="";
    'Jet OLEDB:Create System Database=False;
    'Jet OLEDB:Encrypt Database=False;
    'Jet OLEDB:Don't Copy Locale on Compact=False;
    'Jet OLEDB:Compact Without Replica Repair=False;
    'Jet OLEDB:SFP=False

    Public Sub OpenDBADOConnection()
        Try
            Con = New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sDBName & ";password=" & sDBPass & ";")
            Con.Open()
            Con.Close()
        Catch Ex As Exception
            MsgBox("Exception: " & Ex.Message & "  " & Ex.ToString, MsgBoxStyle.Critical)
            Cursor.Current = Cursors.Default
        End Try
    End Sub
#End Region
End Module
0
 

Author Comment

by:steverpayne
Comment Utility
Is there a program with source code already done around? Would Microsoft have it in Vb .net express 2005?
0
 

Author Comment

by:steverpayne
Comment Utility
Here is more information that maybe needed to keep this simple:

Thursday, July 6, 2006
10:45 AM

Project Name: Inventory Control
Presenter: Steve Payne

Description

The purpose of this Inventory Control is to keep track of the inventory for Cesar A. Caceres, MD PC.

Project Goals

The Project goals are as follows:

Medical Supplies Form

Enter item
Set order level
View inventory levels
Ability to manually adjust inventory levels

Usage Log Sub Form
Track When Items taken with Account number
Qty and by whom

Inventory reorder report

Shows all items at or below reorder level
Generate a report for items that need to be ordered

 Requirements/Resources

Visual Basic .Net
Ms Access DB or SQL

Current Status
working

Related Documents
Documentation will be included in the code
Little documentation for how it works will be done afterwards.

Copyright © 2006 Steve Payne
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

This article explains how to create and use a custom WaterMark textbox class.  The custom WaterMark textbox class allows you to set the WaterMark Background Color and WaterMark text at design time.   IMAGE OF WATERMARKS STEPS Create VB …
1.0 - Introduction Converting Visual Basic 6.0 (VB6) to Visual Basic 2008+ (VB.NET). If ever there was a subject full of murkiness and bad decisions, it is this one!   The first problem seems to be that people considering this task of converting…
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

763 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

7 Experts available now in Live!

Get 1:1 Help Now