frmpurchaseorderae.frm

Inventory control system

End Sub

Private Sub nsdVendor_Change()
    If nsdVendor.DisableDropdown = False Then
        txtLocation.Text = nsdVendor.getSelValueAt(3)
        txtGenDiscPerc.Text = nsdVendor.getSelValueAt(4)
        txtCreditTerm.Text = nsdVendor.getSelValueAt(5)
    End If
    
    nsdStock.sqlwCondition = "VendorID = " & nsdVendor.BoundText
End Sub

Private Sub txtContact_GotFocus()
    HLText txtContact
End Sub

Private Sub txtDiscPercent_Change()
    ComputeGrossNet
End Sub

Private Sub txtDiscPercent_GotFocus()
    HLText txtDiscPercent
End Sub

Private Sub txtDiscPercent_KeyPress(KeyAscii As Integer)
    KeyAscii = isNumber(KeyAscii)
End Sub

Private Sub txtExtDiscAmt_KeyPress(KeyAscii As Integer)
    KeyAscii = isNumber(KeyAscii)
End Sub

Private Sub txtExtDiscAmt_Validate(Cancel As Boolean)
    txtExtDiscAmt.Text = toMoney(toNumber(txtExtDiscAmt.Text))
End Sub

Private Sub txtExtDiscPerc_Change()
    ComputeGrossNet
End Sub

Private Sub txtExtDiscPerc_Click()
    txtQty_Change
End Sub

Private Sub cmdCancel_Click()
    Unload Me
End Sub

Private Sub txtExtDiscPerc_GotFocus()
    HLText txtExtDiscPerc
End Sub

Private Sub txtExtDiscPerc_KeyPress(KeyAscii As Integer)
    KeyAscii = isNumber(KeyAscii)
End Sub

Private Sub txtExtDiscPerc_Validate(Cancel As Boolean)
    txtExtDiscPerc.Text = toNumber(txtExtDiscPerc.Text)
End Sub

Private Sub cmdSave_Click()
    'Verify the entries
    If nsdVendor.Text = "" Then
        MsgBox "Please select a vendor.", vbExclamation
        nsdVendor.SetFocus
        Exit Sub
    End If
    
    If cIRowCount < 1 Then
        MsgBox "Please enter item to purchase before you can save this record.", vbExclamation
        nsdStock.SetFocus
        Exit Sub
    End If
       
    If MsgBox("This save the record. Do you want to proceed?", vbQuestion + vbYesNo) = vbNo Then Exit Sub

    Screen.MousePointer = vbHourglass

    Dim c As Integer

    On Error GoTo err

    CN.BeginTrans

    DeleteItems
    
    'Save the record
    With RS
        If State = adStateAddMode Or State = adStatePopupMode Then
            .AddNew
            ![POID] = PK
            ![VendorID] = nsdVendor.BoundText
            ![PONo] = txtPONo.Text
            ![DateAdded] = Now
            ![AddedByFK] = CurrUser.USER_PK
        Else
            RS.Close
            RS.Open "SELECT * FROM Purchase_Order WHERE POID=" & PK, CN, adOpenStatic, adLockOptimistic
            
            ![DateModified] = Now
            ![LastUserFK] = CurrUser.USER_PK
        End If
        
        ![Date] = dtpDate.Value
        ![Contact] = txtContact.Text
        ![DeliveryDate] = dtpDeliveryDate.Value
        ![DeliveryTime] = txtDeliveryTime.Text
        ![Status] = IIf(cboStatus.Text = "Ordered", True, False)
        ![Notes] = txtNotes.Text
        
        ![Gross] = toNumber(txtGross(2).Text)
        ![Discount] = txtDesc.Text
        ![TaxBase] = toNumber(txtTaxBase.Text)
        ![Vat] = toNumber(txtVat.Text)
        ![NetAmount] = toNumber(txtNet.Text)

        ![Status] = IIf(cboStatus.Text = "Ordered", True, False)
        ![Notes] = txtNotes.Text
        
        
        .Update
    End With
   
    Dim RSDetails As New Recordset

    RSDetails.CursorLocation = adUseClient
    RSDetails.Open "SELECT * FROM Purchase_Order_Detail WHERE POID=" & PK, CN, adOpenStatic, adLockOptimistic
   
    With Grid
        'Save the details of the records
        For c = 1 To cIRowCount
            .Row = c
            If State = adStateAddMode Or State = adStatePopupMode Then
AddNew:
                'Add qty received in Local Purchase Details
                RSDetails.AddNew

                RSDetails![POID] = PK
                RSDetails![StockID] = toNumber(.TextMatrix(c, 13))
                RSDetails![Qty] = toNumber(.TextMatrix(c, 3))
                RSDetails![Unit] = getUnitID(.TextMatrix(c, 6))
                RSDetails![Price] = toNumber(.TextMatrix(c, 7))
                RSDetails![DiscPercent] = toNumber(.TextMatrix(c, 9)) / 100
                RSDetails![ExtDiscPercent] = toNumber(.TextMatrix(c, 10)) / 100
                RSDetails![ExtDiscAmt] = toNumber(.TextMatrix(c, 11))
                
                RSDetails.Update
            ElseIf State = adStateEditMode Then
                RSDetails.Filter = "StockID = " & toNumber(.TextMatrix(c, 13))
            
                If RSDetails.RecordCount = 0 Then GoTo AddNew

                RSDetails![Qty] = toNumber(.TextMatrix(c, 3))
                RSDetails![Unit] = getUnitID(.TextMatrix(c, 6))
                RSDetails![Price] = toNumber(.TextMatrix(c, 7))
                RSDetails![DiscPercent] = toNumber(.TextMatrix(c, 9)) / 100
                RSDetails![ExtDiscPercent] = toNumber(.TextMatrix(c, 10)) / 100
                RSDetails![ExtDiscAmt] = toNumber(.TextMatrix(c, 11))
                
                RSDetails.Update
            End If
        Next c
    End With

    'Clear variables
    c = 0
    Set RSDetails = Nothing

    CN.CommitTrans

    HaveAction = True
    Screen.MousePointer = vbDefault

    If State = adStateAddMode Then
        MsgBox "New record has been successfully saved.", vbInformation
        If MsgBox("Do you want to add another new record?", vbQuestion + vbYesNo) = vbYes Then
            ResetFields
            GeneratePK
            txtPONo.Text = Format(PK, "0000000000")
         Else
            Unload Me
        End If
    Else
        MsgBox "Changes in record has been successfully saved.", vbInformation
        Unload Me
    End If

    Exit Sub
err:
    CN.RollbackTrans
    prompt_err err, Name, "cmdSave_Click"
    Screen.MousePointer = vbDefault
End Sub

Private Sub Form_Activate()
    On Error Resume Next
    If CloseMe = True Then
        Unload Me
    Else
        nsdVendor.SetFocus
    End If
End Sub

Private Sub Form_KeyPress(KeyAscii As Integer)
    If KeyAscii = 13 Then SendKeys ("{tab}")
End Sub

Private Sub Form_Load()
    InitGrid
    
    'Check the form state
    If State = adStateAddMode Or State = adStatePopupMode Then
        InitNSD
        
        'Set the recordset
         RS.Open "SELECT * FROM Purchase_Order WHERE POID=" & PK, CN, adOpenStatic, adLockOptimistic
         dtpDate.Value = Date
         txtDeliveryTime.Text = Time()
         mnu_History.Enabled = False
         mnu_ReceiveItem.Enabled = False
         cmdFreightCharges.Visible = False
         GeneratePK
         txtPONo.Text = Format(PK, "0000000000")
    Else
        Screen.MousePointer = vbHourglass
        'Set the recordset
        RS.Open "SELECT * FROM qry_Purchase_Order WHERE POID=" & PK, CN, adOpenStatic, adLockOptimistic
        
        If State = adStateViewMode Then
            cmdCancel.Caption = "Close"
            mnu_History.Enabled = True
                   
            DisplayForViewing
        Else
            InitNSD
            DisplayForEditing
            
            mnu_ReceiveItem.Enabled = False
            
            nsdStock.sqlwCondition = "VendorID = " & RS!VendorID
        End If
        
        If ForCusAcc = True Then
            Me.Icon = frmPurchaseOrder.Icon
        End If

        Screen.MousePointer = vbDefault
    End If
    'Initialize Graphics
End Sub

'Procedure used to generate PK
Private Sub GeneratePK()
    PK = getIndex("Purchase_Order")
End Sub

'Procedure used to initialize the grid
Private Sub InitGrid()
    cIRowCount = 0
    With Grid
        .Clear
        .ClearStructure
        .Rows = 2
        .FixedRows = 1
        .FixedCols = 1
        .Cols = 14
        .ColSel = 12
        'Initialize the column size
        .ColWidth(0) = 315
        .ColWidth(1) = 0
        .ColWidth(2) = 2430
        .ColWidth(3) = 465
        .ColWidth(4) = 900
        .ColWidth(5) = 700
        .ColWidth(6) = 1000
        .ColWidth(7) = 1005
        .ColWidth(8) = 900
        .ColWidth(9) = 690
        .ColWidth(10) = 995
        .ColWidth(11) = 1150
        .ColWidth(12) = 1000
        .ColWidth(13) = 0
        'Initialize the column name
        .TextMatrix(0, 0) = ""
        .TextMatrix(0, 1) = "Barcode"
        .TextMatrix(0, 2) = "Item"
        .TextMatrix(0, 3) = "Qty"
        .TextMatrix(0, 4) = "Qty Rcvd"
        .TextMatrix(0, 5) = "Qty Due"
        .TextMatrix(0, 6) = "Unit"
        .TextMatrix(0, 7) = "Price" 'Supplier Price
        .TextMatrix(0, 8) = "Gross"
        .TextMatrix(0, 9) = "Disc(%)"
        .TextMatrix(0, 10) = "Ext. Disc(%)"
        .TextMatrix(0, 11) = "Ext. Disc(Amt)"
        .TextMatrix(0, 12) = "Net Amount"
        .TextMatrix(0, 13) = "Stock ID"
        'Set the column alignment
        .ColAlignment(0) = vbLeftJustify
        .ColAlignment(1) = vbLeftJustify
        .ColAlignment(2) = vbLeftJustify
'        .ColAlignment(3) = vbLeftJustify
'        .ColAlignment(4) = vbRightJustify
'        .ColAlignment(5) = vbLeftJustify
'        .ColAlignment(6) = vbRightJustify
'        .ColAlignment(7) = vbRightJustify
'        .ColAlignment(8) = vbRightJustify
'        .ColAlignment(9) = vbRightJustify
'        .ColAlignment(10) = vbRightJustify
    End With
End Sub

Private Sub ResetEntry()
    nsdStock.ResetValue
    txtQty.Text = "0"
    txtPrice.Tag = 0
    txtPrice.Text = "0.00"
    txtDiscPercent.Text = "0"
    txtExtDiscPerc.Text = "0"
    txtExtDiscAmt.Text = "0"
End Sub

Private Sub Form_Unload(Cancel As Integer)
    If HaveAction = True Then
        frmPurchaseOrder.RefreshRecords
    End If
    
    Set frmPurchaseOrderAE = Nothing
End Sub

Private Sub Grid_Click()
    With Grid
        If State = adStateViewMode Then Exit Sub
        
        On Error Resume Next
        bind_dc "SELECT * FROM qry_Unit WHERE StockID=" & .TextMatrix(.RowSel, 13), "Unit", dcUnit, "UnitID", True
        On Error GoTo 0
        
        nsdStock.Text = .TextMatrix(.RowSel, 2)
        nsdStock.Tag = .TextMatrix(.RowSel, 13) 'Add tag coz boundtext is empty
        txtQty.Text = .TextMatrix(.RowSel, 3)
        dcUnit.Text = .TextMatrix(.RowSel, 6)
        txtPrice.Text = toMoney(.TextMatrix(.RowSel, 7))
        txtGross(1).Text = toMoney(.TextMatrix(.RowSel, 8))
        txtDiscPercent.Text = toMoney(.TextMatrix(.RowSel, 9))
        txtExtDiscPerc.Text = toMoney(.TextMatrix(.RowSel, 10))
        txtExtDiscAmt.Text = toMoney(.TextMatrix(.RowSel, 11))
        txtNetAmount.Text = toMoney(.TextMatrix(.RowSel, 12))
    
        If Grid.Rows = 2 And Grid.TextMatrix(1, 13) = "" Then '13 = StockID
            btnRemove.Visible = False
        Else
            btnRemove.Visible = True
            btnRemove.Top = (Grid.CellTop + Grid.Top) - 20
            btnRemove.Left = Grid.Left + 50
        End If
    End With
End Sub

Private Sub Grid_Scroll()
    btnRemove.Visible = False
End Sub

Private Sub Grid_SelChange()
    Grid_Click
End Sub

Private Sub nsdStock_Change()
    On Error Resume Next
    
    txtQty.Text = "0"
    
    dcUnit.Text = ""
    bind_dc "SELECT * FROM qry_Unit WHERE StockID=" & nsdStock.BoundText & " ORDER BY qry_Unit.Order ASC", "Unit", dcUnit, "UnitID", True
    
    nsdStock.Tag = nsdStock.BoundText
    
    txtPrice.Text = toMoney(nsdStock.getSelValueAt(3)) 'Supplier Price
    txtExtDiscPerc.Text = toMoney(nsdStock.getSelValueAt(5)) 'Ext Disc (%)
    txtExtDiscAmt.Text = toMoney(nsdStock.getSelValueAt(6)) 'Ext Disc (Amt)