tmp2.frm

Inventory control system

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

Private Sub cmdUsrHistory_Click()
    On Error Resume Next
    Dim tDate1 As String
    Dim tUser1 As String
    
    tDate1 = Format$(rs.Fields("DateAdded"), "MMM-dd-yyyy HH:MM AMPM")
    
    tUser1 = getValueAt("SELECT PK,CompleteName FROM tbl_SM_Users WHERE PK = " & rs.Fields("AddedByFK"), "CompleteName")
    
    MsgBox "Date Added: " & tDate1 & vbCrLf & _
           "Added By: " & tUser1 & vbCrLf & _
           "" & vbCrLf & _
           "Last Modified: n/a" & vbCrLf & _
           "Modified By: n/a", vbInformation, "Modification History"
           
    tDate1 = vbNullString
    tUser1 = vbNullString
End Sub

Private Sub dcProd_Click(Area As Integer)
    On Error Resume Next
    If Area = 2 Then
        If dcProd.BoundText <> "" Then
            ResetEntry
            DiplayProdInfo
        End If
    End If
End Sub

Private Sub Form_Activate()
    If CloseMe = True Then Unload Me
End Sub

Private Sub Form_Load()
    dtpDate.Value = Date
    
    'Bind the data combo
    bind_dc "SELECT * FROM tbl_AR_Van", "VanName", dcVan, "PK", True
    
    InitGrid
    
    'Check the form state
    If State = adStateAddMode Or State = adStatePopupMode Then
        frmVanInventoryAEPickFrom.show vbModal
        
        'Set the recordset
        rs.Open "SELECT * FROM tbl_IC_Loading WHERE PK=" & PK, CN, adOpenStatic, adLockOptimistic
        'Bind the combo
        bind_dc "SELECT * FROM tbl_IC_Products", "ProductCode", dcProd, "PK", True
        Caption = "Create New Entry"
        cmdUsrHistory.Enabled = False
        GeneratePK
        DiplayProdInfo
    Else
        Screen.MousePointer = vbHourglass
        'Set the recordset
        rs.Open "SELECT * FROM qry_IC_Loading WHERE PK=" & PK, CN, adOpenStatic, adLockOptimistic

        Caption = "View Record"
        cmdCancel.Caption = "Close"
        cmdUsrHistory.Enabled = True
        btnProdAvailable.Enabled = False
        txtEntry(0).Width = txtDate.Width
        DisplayForViewing
        Me.show
        MsgBox "This is use for viewing the record only." & vbCrLf & _
               "You cannot perform any changes in this form." & vbCrLf & vbCrLf & _
               "Note:If you have mistake in adding this record then " & vbCrLf & _
               "void this record and re-enter.", vbExclamation
        txtEntry(0).SetFocus
        Screen.MousePointer = vbDefault
    End If
    
End Sub

'Procedure used to generate PK
Private Sub GeneratePK()
    PK = getIndex("tbl_IC_Loading")
    txtEntry(0).Text = GenerateID(PK, Format$(Date, "yyyy") & Format$(Date, "mm") & Format$(Date, "dd") & "-", "0")
End Sub

'Procedure used to initialize the grid
Private Sub InitGrid()
    clRowCount = 0
    With Grid
        .Clear
        .ClearStructure
        .Rows = 2
        .FixedRows = 1
        .FixedCols = 1
        .Cols = 13
        .ColSel = 11
        'Initialize the column size
        .ColWidth(0) = 315
        .ColWidth(1) = 2025
        .ColWidth(2) = 2505
        .ColWidth(3) = 1300
        .ColWidth(4) = 1300
        .ColWidth(5) = 1300
        .ColWidth(6) = 1300
        .ColWidth(7) = 1300
        .ColWidth(8) = 1300
        .ColWidth(9) = 1300
        .ColWidth(10) = 1300
        .ColWidth(11) = 0
        .ColWidth(12) = 0
        'Initialize the column name
        .TextMatrix(0, 0) = ""
        .TextMatrix(0, 1) = "Product Code"
        .TextMatrix(0, 2) = "Description"
        .TextMatrix(0, 3) = "Unit Cost(Each)"
        .TextMatrix(0, 4) = "Cases"
        .TextMatrix(0, 5) = "Boxes"
        .TextMatrix(0, 6) = "Pieces"
        .TextMatrix(0, 7) = "Qty Load"
        .TextMatrix(0, 8) = "Van Inv"
        .TextMatrix(0, 9) = "Total Load"
        .TextMatrix(0, 10) = "Amount"
        .TextMatrix(0, 11) = "ProductFK"
        .TextMatrix(0, 12) = "PK"
        'Set the column alignment
        .ColAlignment(0) = vbLeftJustify
        .ColAlignment(1) = vbLeftJustify
        .ColAlignment(2) = vbLeftJustify
        .ColAlignment(3) = vbLeftJustify
        .ColAlignment(4) = vbLeftJustify
        .ColAlignment(5) = vbLeftJustify
        .ColAlignment(6) = vbLeftJustify
        .ColAlignment(7) = vbLeftJustify
        .ColAlignment(8) = vbLeftJustify
        .ColAlignment(9) = vbLeftJustify
        .ColAlignment(10) = vbLeftJustify
        .ColAlignment(11) = vbLeftJustify
        .ColAlignment(12) = vbLeftJustify
    End With
End Sub

'Procedure used to display product information
Private Sub DiplayProdInfo()
    Screen.MousePointer = vbHourglass
    
    Dim rsPI As New Recordset
    
    With rsPI
        .CursorLocation = adUseClient
        
        .Open "SELECT * FROM tbl_IC_Products WHERE PK =" & dcProd.BoundText, CN, adOpenStatic, adLockReadOnly
        
        txtEntry(1).Text = ![Description]
        txtUC.Text = toMoney(toNumber(![UnitCost]))
        PCase = ![PiecesPerCase]
        PBox = ![PiecesPerBox]
        
    End With
    
    Set rsPI = Nothing
    
    If PCase = 0 Then
        Label3.Visible = True
        Label5.Visible = True
        'Loaded Entry
        txtEntry(2).BackColor = &HE6FFFF
        txtEntry(2).ForeColor = &H0&
        txtEntry(2).Locked = True
        'Van Inventory Entry
        txtEntry(5).BackColor = &HE6FFFF
        txtEntry(5).ForeColor = &H0&
        txtEntry(5).Locked = True
    Else
        Label3.Visible = False
        Label5.Visible = False
        'Loaded Entry
        txtEntry(2).BackColor = &H80000005
        txtEntry(2).ForeColor = &H80000008
        txtEntry(2).Locked = False
        'Van Inventory Entry
        txtEntry(5).BackColor = &H80000005
        txtEntry(5).ForeColor = &H80000008
        txtEntry(5).Locked = False
    End If
    
    If PBox = 0 Then
        Label4.Visible = True
        Label7.Visible = True
        'Loaded Entry
        txtEntry(3).BackColor = &HE6FFFF
        txtEntry(3).ForeColor = &H0&
        txtEntry(3).Locked = True
        'Van Inventory Entry
        txtEntry(6).BackColor = &HE6FFFF
        txtEntry(6).ForeColor = &H0&
        txtEntry(6).Locked = True
    Else
        Label4.Visible = False
        Label7.Visible = False
        'Loaded Entry
        txtEntry(3).BackColor = &H80000005
        txtEntry(3).ForeColor = &H80000008
        txtEntry(3).Locked = False
        'Van Inventory Entry
        txtEntry(6).BackColor = &H80000005
        txtEntry(6).ForeColor = &H80000008
        txtEntry(6).Locked = False
    End If
    
    Screen.MousePointer = vbDefault
End Sub


Private Sub ResetEntry()
    txtEntry(2).Text = "0"
    txtEntry(3).Text = "0"
    txtEntry(4).Text = "0"
    
    txtEntry(5).Text = "0"
    txtEntry(6).Text = "0"
    txtEntry(7).Text = "0"
        
    txtLQty.Text = "0"
    txtVIQty.Text = "0"
   
    txtQty.Text = "0"
    txtAmount.Text = "0.00"
End Sub

Private Sub Form_Unload(Cancel As Integer)
    If HaveAction = True Then
        frmVanInventory.RefreshRecords
        MAIN.UpdateInfoMsg
    End If
    
    'Clean up all used variables
    Set rs = Nothing
    
    PK = 0

    PCase = 0
    PBox = 0

    clAmount = 0
    clRowCount = 0

    HaveAction = False
    
    Set frmVanInventoryAE = Nothing
End Sub

Private Sub Grid_Click()
    If State = adStateEditMode Then Exit Sub
    If Grid.Rows = 2 And Grid.TextMatrix(1, 11) = "" Then
        btnRemove.Visible = False
    Else
        btnRemove.Visible = True
        btnRemove.Top = (Grid.CellTop + Grid.Top) - 20
        btnRemove.Left = Grid.Left + 50
    End If
End Sub


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

Private Sub Grid_SelChange()
    Grid_Click
End Sub

Private Sub txtAmount_GotFocus()
    HLText txtAmount
End Sub

Private Sub txtCLAmount_GotFocus()
    HLText txtCLAmount
End Sub

Private Sub txtDate_GotFocus()
    HLText txtDate
End Sub

Private Sub txtEntry_Change(Index As Integer)
    If Index > 1 And Index < 8 Then
        txtLQty.Text = (toNumber(txtEntry(2).Text) * PCase) + (toNumber(txtEntry(3).Text) * PBox) + toNumber(txtEntry(4).Text)
        txtVIQty.Text = (toNumber(txtEntry(5).Text) * PCase) + (toNumber(txtEntry(6).Text) * PBox) + toNumber(txtEntry(7).Text)
        txtQty.Text = toNumber(txtLQty.Text) + toNumber(txtVIQty.Text)
        
        txtAmount.Text = toMoney(toNumber(txtQty.Text) * toNumber(txtUC.Text))
    End If
End Sub

Private Sub txtEntry_GotFocus(Index As Integer)
    HLText txtEntry(Index)
End Sub

Private Sub txtEntry_KeyPress(Index As Integer, KeyAscii As Integer)
    If Index > 1 And Index < 8 Then
        KeyAscii = isNumber(KeyAscii)
    End If
End Sub

Private Sub txtEntry_Validate(Index As Integer, Cancel As Boolean)
    If Index > 1 And Index < 8 Then
        txtEntry(Index).Text = toNumber(txtEntry(Index).Text)
    End If
End Sub

Private Sub txtLQty_GotFocus()
    HLText txtLQty
End Sub

Private Sub txtQty_Change()
    If toNumber(txtQty.Text) < 1 Then
        btnLoad.Enabled = False
    Else
        btnLoad.Enabled = True
    End If
End Sub

Private Sub txtQty_GotFocus()
    HLText txtQty
End Sub

Private Sub txtUC_Change()
    txtLQty.Text = (toNumber(txtEntry(2).Text) * PCase) + (toNumber(txtEntry(3).Text) * PBox) + toNumber(txtEntry(4).Text)
    txtVIQty.Text = (toNumber(txtEntry(5).Text) * PCase) + (toNumber(txtEntry(6).Text) * PBox) + toNumber(txtEntry(7).Text)
    txtQty.Text = toNumber(txtLQty.Text) + toNumber(txtVIQty.Text)
    
    txtAmount.Text = toMoney(toNumber(txtQty.Text) * toNumber(txtUC.Text))
End Sub

Private Sub txtUC_GotFocus()
    HLText txtUC
End Sub

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

Private Sub txtUC_Validate(Cancel As Boolean)
    txtUC.Text = toMoney(toNumber(txtUC.Text))
End Sub

Private Sub txtVIQty_GotFocus()
    HLText txtVIQty
End Sub

'Procedure used to reset fields
Private Sub ResetFields()
    clearText Me
    InitGrid
    
    dtpDate.Value = Date
    
    ResetEntry
    
    clAmount = 0
    
    txtUC.Text = "0.00"
    txtCLAmount.Text = "0.00"
    
    dcVan.BoundText = RightSplitUF(dcVan.Tag)
    dcProd.BoundText = RightSplitUF(dcProd.Tag)
    DiplayProdInfo
    
    dtpDate.SetFocus
End Sub

'Used to display record
Private Sub DisplayForViewing()
    txtEntry(0).Text = rs![LoadingNo]
    txtDate.Text = Format$(rs![Date], "MMM-dd-yyyy")
    dcVan.BoundText = rs![VanFK]
    txtCLAmount.Text = toMoney(rs![TotalAmount])
    'Display the details
    Dim RSDetails As New Recordset
    
    RSDetails.CursorLocation = adUseClient
    RSDetails.Open "SELECT * FROM qry_IC_LoadingDetails WHERE LoadingFK=" & PK & " ORDER BY PK ASC", CN, adOpenStatic, adLockOptimistic
    If RSDetails.RecordCount > 0 Then
        RSDetails.MoveFirst
        While Not RSDetails.EOF
            With Grid
                If .Rows = 2 And .TextMatrix(1, 11) = "" Then
                    .TextMatrix(1, 1) = RSDetails![ProductCode]
                    .TextMatrix(1, 2) = RSDetails![Description]
                    .TextMatrix(1, 3) = toMoney(RSDetails![UnitCost(Each)])
                    .TextMatrix(1, 4) = RSDetails![Cases]
                    .TextMatrix(1, 5) = RSDetails![Boxes]
                    .TextMatrix(1, 6) = RSDetails![Pieces]
                    .TextMatrix(1, 7) = RSDetails![QtyLoad]
                    .TextMatrix(1, 8) = RSDetails![VanInv]
                    .TextMatrix(1, 9) = RSDetails![TotalLoad]
                    .TextMatrix(1, 10) = toMoney(RSDetails![Amount])
                    .TextMatrix(1, 11) = RSDetails![ProductFK]
                Else
                    .Rows = .Rows + 1
                    .TextMatrix(.Rows - 1, 1) = RSDetails![ProductCode]
                    .TextMatrix(.Rows - 1, 2) = RSDetails![Description]
                    .TextMatrix(.Rows - 1, 3) = toMoney(RSDetails![UnitCost(Each)])
                    .TextMatrix(.Rows - 1, 4) = RSDetails![Cases]
                    .TextMatrix(.Rows - 1, 5) = RSDetails![Boxes]
                    .TextMatrix(.Rows - 1, 6) = RSDetails![Pieces]
                    .TextMatrix(.Rows - 1, 7) = RSDetails![QtyLoad]
                    .TextMatrix(.Rows - 1, 8) = RSDetails![VanInv]
                    .TextMatrix(.Rows - 1, 9) = RSDetails![TotalLoad]
                    .TextMatrix(.Rows - 1, 10) = toMoney(RSDetails![Amount])
                    .TextMatrix(.Rows - 1, 11) = RSDetails![ProductFK]
                End If
            End With
            RSDetails.MoveNext
        Wend
        Grid.Row = 1
        Grid.ColSel = 11
    End If
    
    RSDetails.Close
    
    'Disable commands
    LockInput Me, True
    
    dtpDate.Visible = False
    txtDate.Visible = True
    picProd.Visible = False
    cmdSave.Visible = False
    btnLoad.Visible = False
    
    'Resize and reposition the controls
    Shape3.Top = 900
    Label11.Top = 900
    Grid.Top = 1200
    Grid.Height = 3565
    txtCLAmount.Top = txtCLAmount.Top - 2000
    Label9.Top = Label9.Top - 2000
    cmdUsrHistory.Top = cmdUsrHistory.Top - 2000
    btnProdAvailable.Top = btnProdAvailable.Top - 2000
    cmdSave.Top = cmdSave.Top - 2000
    cmdCancel.Top = cmdCancel.Top - 2000
    ctrlLiner1.Top = cmdSave.Top - 150
    Me.Height = Me.Height - 2000
    Me.Top = (Screen.Height - Me.Height) / 2
    Line1(0).Visible = False
    Line1(1).Visible = False
    Line2(0).Visible = False
    Line2(1).Visible = False
    'Clear variables
    Set RSDetails = Nothing
End Sub