So I have a program that is supposed to support a small business. I have the work completed and the professor said it was correct but I wanted to go do a little extra.

Basically, on the Order screen, you begin to type the name of a customer/supplier/employee into a text box and a list of names that fit the criteria pops up in a list box (lbMatchingAccounts). When you select it, you can hit enter new order and be transported to the Order Details screen to enter the new order.


What I wanted to do was to create a new list box (ListBoxOrderId). What I want is to be able to select a person from lbMatchingAccounts and have all their Orders populate in ListBoxOrderId.

The Order information is in an Orders table.

Can anyone help me with where to start with this?

Here's the code I have so far (I took out my attempts with the above problem since none of them worked and the program would crash). Also I'm using Visual Basic 2010.

Imports System.Data.OleDb
Imports System.Data.SqlClient
Imports System.Data.Common
Imports System.Configuration
Imports System.Data.SqlServerCe

Public Class frmNewOrder
    Dim Cnxn As SqlCeConnection
    Dim TheCommand As SqlCeCommand
    Dim SQLStmt As String

    Private Sub frmNewOrder_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        lblAdvice.Text = "Choose the type of order and the date, then click Choose Account..."
        lbMatchingAccounts.DisplayMember = "DisplayValue"
        lbMatchingAccounts.ValueMember = "IdValue"
        txtAccountSearch.Text = ""
        Me.AcceptButton = btnChooseAccount
        gbWho.Enabled = False

        If (Not LoggedInUser.Purview = "Owner" And Not LoggedInUser.Purview = "Manager") Then
            rbPurchase.Enabled = False
            rbWork.Enabled = False

        End If
    End Sub

    Private Sub rbSales_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles rbSales.CheckedChanged
        If rbSales.Checked Then btnChooseAccount.Text = "Choose Customer"

    End Sub

    Private Sub rbPurchase_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles rbPurchase.CheckedChanged
        If rbPurchase.Checked Then btnChooseAccount.Text = "Choose Supplier"

    End Sub

    Private Sub rbWork_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles rbWork.CheckedChanged
        If rbWork.Checked Then btnChooseAccount.Text = "Choose Employee"

    End Sub

    Private Sub btnChooseAccount_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnChooseAccount.Click
        gbOrderType.Enabled = False
        dpDate.Enabled = False
        btnChooseAccount.Enabled = False
        gbWho.Enabled = True
        btnStartNewOrder.Enabled = False
        Me.AcceptButton = btnStartNewOrder
        txtAccountSearch.Focus()

    End Sub

    Private Sub txtAccountSearch_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txtAccountSearch.TextChanged
        If txtAccountSearch.Text = "" Then
            lblAdvice.Text = "Enter characters from the last name to search for a record.  Click the New button to add a new account record."
            Return
        End If
        Dim SQLStmt, WhereNoun, FirstName, AccountName As String
        Dim DisplayedInList As Integer
        Cnxn = New SqlCeConnection(My.Settings.Database1ConnectionString)
        'Debug.Write(vbCrLf & "Before Cnxn.Open CnxnString=" & CnxnString & vbCrLf)
        Cnxn.Open()
        If Cnxn.State <> ConnectionState.Open Then
            MsgBox("Unable to connect to the database...", MsgBoxStyle.Critical)
            End
        End If
        If rbSales.Checked Then
            SQLStmt = String.Format( _
               "SELECT LastName, FirstName, Id as AccountId from BEs WHERE LastName like '{0}%' ORDER BY LastName, FirstName", _
               txtAccountSearch.Text)
        Else
            If rbPurchase.Checked Then
                WhereNoun = "Supplier"
            ElseIf rbWork.Checked Then
                WhereNoun = "Employee"
            Else
                MsgBox("Somehow we got here without a radio button clicked")
                End
            End If
            
            SQLStmt = String.Format( _
               "SELECT LastName, FirstName, Id as AccountId from BEs WHERE LastName like '{0}%' ORDER BY LastName, FirstName", _
               txtAccountSearch.Text)
        End If
        Dim cmdSearch As New SqlCeCommand(SQLStmt, Cnxn)
        Dim Matches As SqlCeDataReader = cmdSearch.ExecuteReader
        If Not Matches.Read Then
            txtAccountSearch.Focus()
            lblAdvice.Text = "No matches found for '" & txtAccountSearch.Text & "'..."
            btnStartNewOrder.Enabled = False
        Else
            Dim MatchingRecords As New ArrayList
            DisplayedInList = 0
            Do
                FirstName = Matches.Item("FirstName") & ""
                AccountName = Matches.Item("LastName")
                If FirstName <> "" Then AccountName &= ", " & FirstName
                MatchingRecords.Add(New ListItem(AccountName, Str(Matches.Item("AccountId"))))
                DisplayedInList += 1
            Loop Until (DisplayedInList > 50) Or Not Matches.Read
            lbMatchingAccounts.DataSource = MatchingRecords
            lblAdvice.Text = "Enter a few characters from a word in the title to search.  Use the tab and arrow keys to hilite the desired item..."
            btnStartNewOrder.Enabled = True
            lbMatchingAccounts.Focus()
        End If
        Cnxn.Close()
        txtAccountSearch.Focus()

    End Sub

    Private Sub lbMatchingAccounts_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles lbMatchingAccounts.SelectedIndexChanged
        Dim SQLStmt As String
        SQLStmt = "select * from BEs where Id=" & lbMatchingAccounts.SelectedItem.IdValue
        Debug.Write("SQLStmt=" & SQLStmt & vbCrLf)
        Cnxn = New SqlCeConnection(My.Settings.Database1ConnectionString)
        Cnxn.Open()
        Dim cmdSelectedRecord As New SqlCeCommand(SQLStmt, Cnxn)
        Dim SelectedRecord As SqlCeDataReader = cmdSelectedRecord.ExecuteReader
        If Not SelectedRecord.Read Then
            lblAdvice.Text = "No record found for '" & lbMatchingAccounts.SelectedValue & "'..."
        Else
            
            lblAdvice.Text = "Hit enter to use the highlighted account, or start another search..."
        End If
        SelectedRecord.Close()
        Cnxn.Close()

    End Sub

    Private Sub btnStartNewOrder_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnStartNewOrder.Click
        Dim AccountId, OrderType, OrderId As String
        Dim CrDr As Integer
        AccountId = lbMatchingAccounts.SelectedItem.IdValue
        Dim SQLStmt As String
        Dim UpdateOK As Integer
        Cnxn = New SqlCeConnection(My.Settings.Database1ConnectionString)
        Cnxn.Open()
        OrderType = ""
        If rbSales.Checked Then
            OrderType = "Sales"
            CrDr = -1
        ElseIf rbPurchase.Checked Then
            OrderType = "Purchase"
            CrDr = 1
        ElseIf rbWork.Checked Then
            OrderType = "Work"
            CrDr = 1
        End If
        SQLStmt = String.Format("insert into Orders (Date, BEId, Type, DebitCredit, EnteredBy) values ('{0}', {1}, '{2}', {3}, {4})", _
        dpDate.Value, AccountId, OrderType, CrDr, LoggedInUser.AccountId)
        Dim UpdateCommand As New SqlCeCommand(SQLStmt, Cnxn)
        Debug.Write("UpdateSQLStmt=" & SQLStmt & vbCrLf)
        Try
            UpdateOK = UpdateCommand.ExecuteNonQuery()
        Catch ExMsg As Exception
            MsgBox("Exception at Insert:" & ExMsg.Message)
            End

        End Try
      

        SQLStmt = "select max(Id) as Id from Orders"
        Dim cmdCurrentOrderId As New SqlCeCommand(SQLStmt, Cnxn)
        Dim CurrentOrderId As SqlCeDataReader = cmdCurrentOrderId.ExecuteReader
        If Not CurrentOrderId.Read Then
            MsgBox("No record found for '" & lbMatchingAccounts.SelectedValue & "'...")
            End
        End If
        OrderId = CurrentOrderId.Item("Id")
        LoggedInUser.CurrentOrderId = OrderId
        LoggedInUser.CurrentOrderType = OrderType
        LoggedInUser.CurrentOrderCrDr = CrDr
        Cnxn.Close()
        Dim frmDetails As frmDetails
        frmDetails = New frmDetails
        frmDetails.Show()
        Me.Hide()
    End Sub


    Private Sub btnOrder_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)

        
    End Sub

    Private Sub lbOrderId_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs)


    End Sub
End Class

Edited 5 Years Ago by johnsong4: n/a

One of the ways to obtain what you need is to create a Sub FillOrdersCombo using a paramenter indicating the Idvalue of the selected item in lbMatchingAccounts, to be called when the account is OK.

in order the call to this sub does not fail, you will need tochange silightly the following code:

Private Sub lbMatchingAccounts_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles lbMatchingAccounts.SelectedIndexChanged
        Dim SQLStmt As String
        SQLStmt = "select * from BEs where Id=" & lbMatchingAccounts.SelectedItem.IdValue
        Debug.Write("SQLStmt=" & SQLStmt & vbCrLf)
        Cnxn = New SqlCeConnection(My.Settings.Database1ConnectionString)
        Cnxn.Open()
        Dim cmdSelectedRecord As New SqlCeCommand(SQLStmt, Cnxn)
        Dim SelectedRecord As SqlCeDataReader = cmdSelectedRecord.ExecuteReader
        Dim IdValueExists as Boolean = SelectedRecord.Read
        SelectedRecord.Close()
        Cnxn.Close()
        Call FillOrdersCombo(lbMatchingAccounts.SelectedItem.IdValue)
        If Not IdValueExists Then
            lblAdvice.Text = "No record found for '" & lbMatchingAccounts.SelectedValue & "'..."
        Else
            
            lblAdvice.Text = "Hit enter to use the highlighted account, or start another search..."
        End If

    End Sub

Then on the fill orders combo you must:
1) clear the orders combo
2) select the orders for the IdValue selected
3) fill the combo with the obtained results.

Hope this helps.

This article has been dead for over six months. Start a new discussion instead.