frm_sales_update_form.frm

很好一套库存管理

            While d.EOF <> True
                If d.Fields(1).Value <> SYSTEM_QTY Then
                    MsgBox "Qty of some Item is less or greater than the required Qty", vbCritical, "Enter Proper Qty from Each Items"
                    Exit Sub
                End If
                d.MoveNext
            Wend
End If

If Len(Combo1.Text) = 0 Then
    MsgBox "Customer Name not found ...", vbInformation, "Enter Party Name ..."
    Exit Sub
End If

SNAME = "SAVED"
FNAME = Clear







DoEvents
rs_cur_invoice_item.Requery
rs_cur_record_count.Requery




Dim rs_update_it_master As New ADODB.Recordset
Dim rs_aps As New ADODB.Recordset

If rs_cur_record_count.Fields(0).Value > 0 Then
    
    
    
    
    TOTAL_TRAN_AMT = TOTAL_AMT("SALES")
    
    While rs_cur_invoice_item.EOF <> True
        Dim rs_profit As New ADODB.Recordset
        rs_profit.Open "select price_per_unit from Purchase_master where Invoice_no='" & rs_cur_invoice_item.Fields(6).Value & "' AND Party_name='" & rs_cur_invoice_item.Fields(5).Value & "' AND Item_type='" & rs_cur_invoice_item.Fields(4).Value & "' AND Item_name='" & rs_cur_invoice_item.Fields(0).Value & "'", db, adOpenDynamic, adLockOptimistic
        Dim AC_VAL As Double
        AC_VAL = rs_profit.Fields(0).Value * rs_cur_invoice_item.Fields(1).Value
        Dim SAL_VAL As Double
        SAL_VAL = rs_cur_invoice_item.Fields(2).Value * rs_cur_invoice_item.Fields(1).Value
        Dim PROFIT As Double
        PROFIT = VAL(SAL_VAL) - VAL(AC_VAL)
        
        Dim R As New ADODB.Recordset
        R.Open "SELECT * FROM DATE_PROFIT", db, adOpenDynamic, adLockOptimistic
        R.AddNew
        R.Fields(0).Value = DTPicker1.Value
        R.Fields(1).Value = PROFIT
        R.Fields(2).Value = Text1(0).Text
        R.Update
        R.Close
        
        rs_cur_invoice_item.MoveNext
        rs_profit.Close
    Wend
    
    
    rs_cur_record_count.Requery
    rs_cur_invoice_item.Requery
    
    
    
    
    RS_SALES_INVOICE_DESC.AddNew
    RS_SALES_INVOICE_DESC.Fields(0).Value = Text1(0).Text
    RS_SALES_INVOICE_DESC.Fields(1).Value = Text2.Text
    RS_SALES_INVOICE_DESC.Update

    
    While rs_cur_invoice_item.EOF <> True
        rs_update_it_master.Open "select * from Item_master where Item_name='" & rs_cur_invoice_item.Fields(0).Value & "' AND Itemtype='" & rs_cur_invoice_item.Fields(4).Value & "'", db, adOpenDynamic, adLockOptimistic
        rs_aps.Open "select * from AVAILABLE_PURCHASED_STOCK where Party_name='" & rs_cur_invoice_item.Fields(5).Value & "' AND Item_type='" & rs_cur_invoice_item.Fields(4).Value & "' AND Item_name='" & rs_cur_invoice_item.Fields(0).Value & "' AND Invoice_no='" & rs_cur_invoice_item.Fields(6).Value & "'", db, adOpenDynamic, adLockOptimistic
        rs_sales_master.AddNew
        rs_sales_master.Fields(0).Value = Text1(0).Text
        rs_sales_master.Fields(1).Value = Text1(4).Text
        rs_sales_master.Fields(2).Value = Combo1.Text
        rs_sales_master.Fields(3).Value = DTPicker1.Value
        rs_sales_master.Fields(4).Value = rs_cur_invoice_item.Fields(4).Value
        rs_sales_master.Fields(5).Value = rs_cur_invoice_item.Fields(0).Value
        rs_sales_master.Fields(6).Value = rs_cur_invoice_item.Fields(1).Value
        rs_sales_master.Fields(7).Value = rs_cur_invoice_item.Fields(2).Value
        rs_sales_master.Fields(8).Value = rs_cur_invoice_item.Fields(6).Value
        rs_sales_master.Fields(9).Value = rs_cur_invoice_item.Fields(5).Value
        rs_sales_master.Fields(10).Value = rs_cur_invoice_item.Fields(7).Value
        rs_sales_master.Fields(11).Value = rs_cur_invoice_item.Fields(8).Value
        
        rs_update_it_master.Fields(3).Value = VAL(rs_update_it_master.Fields(3).Value) - VAL(rs_cur_invoice_item.Fields(1).Value)
        rs_update_it_master.Update
        rs_update_it_master.Close
        ''' CHANGE
        rs_aps.Fields(5).Value = VAL(rs_aps.Fields(5).Value) - VAL(rs_cur_invoice_item.Fields(1).Value)
        rs_aps.Fields(7).Value = VAL(rs_aps.Fields(5).Value) * VAL(rs_aps.Fields(6).Value)
        rs_aps.Update
        If rs_aps.Fields(5).Value = 0 Then
            rs_aps.Delete
        End If
        
        rs_aps.Close
        rs_cur_invoice_item.MoveNext
    Wend
    
    rs_sales_master.Update
    
    If SALE_TYPE = "SYSTEM" Then
    Dim UPDATE_SYS_REPORT As New ADODB.Recordset
    Dim sysnors As New ADODB.Recordset
    UPDATE_SYS_REPORT.Open "SELECT * FROM Customer_System_datail", db, adOpenDynamic, adLockOptimistic
    
    
    
    
    sysnors.Open "SELECT * FROM INVOICE_NUMBER_SYSTEM_ID WHERE INVOICE_NUMBER='" & Text1(0).Text & "'", db, adOpenDynamic, adLockOptimistic

    
    
    
    While sysnors.EOF <> True
        UPDATE_SYS_REPORT.AddNew
        rs_cur_invoice_item.Requery
        rs_cur_invoice_item.MoveFirst
        UPDATE_SYS_REPORT.Fields(0).Value = sysnors.Fields(0).Value
    
    While rs_cur_invoice_item.EOF <> True
    
    If rs_cur_invoice_item.Fields(4).Value = "Processor" Then
            UPDATE_SYS_REPORT.Fields(1).Value = rs_cur_invoice_item.Fields(0).Value
    ElseIf rs_cur_invoice_item.Fields(4).Value = "MotherBoard" Then
            UPDATE_SYS_REPORT.Fields(2).Value = rs_cur_invoice_item.Fields(0).Value
    ElseIf rs_cur_invoice_item.Fields(4).Value = "Harddisk" Then
            UPDATE_SYS_REPORT.Fields(3).Value = rs_cur_invoice_item.Fields(0).Value
    ElseIf rs_cur_invoice_item.Fields(4).Value = "Floppydisk" Then
            UPDATE_SYS_REPORT.Fields(4).Value = rs_cur_invoice_item.Fields(0).Value
    ElseIf rs_cur_invoice_item.Fields(4).Value = "RAM" Then
            UPDATE_SYS_REPORT.Fields(5).Value = rs_cur_invoice_item.Fields(0).Value
    ElseIf rs_cur_invoice_item.Fields(4).Value = "CD ROM" Then
            UPDATE_SYS_REPORT.Fields(6).Value = rs_cur_invoice_item.Fields(0).Value
    ElseIf rs_cur_invoice_item.Fields(4).Value = "Speaker" Then
            UPDATE_SYS_REPORT.Fields(7).Value = rs_cur_invoice_item.Fields(0).Value
    ElseIf rs_cur_invoice_item.Fields(4).Value = "Mouse" Then
            UPDATE_SYS_REPORT.Fields(8).Value = rs_cur_invoice_item.Fields(0).Value
    ElseIf rs_cur_invoice_item.Fields(4).Value = "Keyboard" Then
            UPDATE_SYS_REPORT.Fields(9).Value = rs_cur_invoice_item.Fields(0).Value
    ElseIf rs_cur_invoice_item.Fields(4).Value = "Printer" Then
            UPDATE_SYS_REPORT.Fields(10).Value = rs_cur_invoice_item.Fields(0).Value
    ElseIf rs_cur_invoice_item.Fields(4).Value = "Scaner" Then
            UPDATE_SYS_REPORT.Fields(11).Value = rs_cur_invoice_item.Fields(0).Value
    ElseIf rs_cur_invoice_item.Fields(4).Value = "SoundCard" Then
            UPDATE_SYS_REPORT.Fields(12).Value = rs_cur_invoice_item.Fields(0).Value
    ElseIf rs_cur_invoice_item.Fields(4).Value = "CD WRITER" Then
            UPDATE_SYS_REPORT.Fields(13).Value = rs_cur_invoice_item.Fields(0).Value
    ElseIf rs_cur_invoice_item.Fields(4).Value = "Modem" Then
            UPDATE_SYS_REPORT.Fields(14).Value = rs_cur_invoice_item.Fields(0).Value
    ElseIf rs_cur_invoice_item.Fields(4).Value = "Monitor" Then
            UPDATE_SYS_REPORT.Fields(15).Value = rs_cur_invoice_item.Fields(0).Value
    ElseIf rs_cur_invoice_item.Fields(4).Value = "Web-Cam" Then
            UPDATE_SYS_REPORT.Fields(16).Value = rs_cur_invoice_item.Fields(0).Value
    ElseIf rs_cur_invoice_item.Fields(4).Value = "Stebilizer" Then
            UPDATE_SYS_REPORT.Fields(17).Value = rs_cur_invoice_item.Fields(0).Value
    ElseIf rs_cur_invoice_item.Fields(4).Value = "ZIP Drive" Then
            UPDATE_SYS_REPORT.Fields(18).Value = rs_cur_invoice_item.Fields(0).Value
    End If
    
    
        
    rs_cur_invoice_item.MoveNext
    
    Wend
    
    UPDATE_SYS_REPORT.Update
    sysnors.MoveNext
    
    Wend
    rs_cur_invoice_item.Requery
    
    While rs_cur_invoice_item.EOF <> True
        rs_cur_invoice_item.Delete
        rs_cur_invoice_item.MoveNext
    Wend
    SALE_TYPE = Clear
    FRM_AMT_PAID_NOT_PAID.Label3(5).Caption = "Sales"
    FRM_AMT_PAID_NOT_PAID.Label3(2).Caption = Text1(0).Text
    FRM_AMT_PAID_NOT_PAID.Label3(0).Caption = Combo1.Text
    FRM_AMT_PAID_NOT_PAID.dt = Format(DTPicker1.Value, "dd-MMM-yyyy")
    FRM_AMT_PAID_NOT_PAID.Label2(2).Caption = TOTAL_TRAN_AMT
    Unload Me
       ' Dim f As New FileSystemObject
       ' f.CopyFile App.Path & "\Master_Database.mdb", App.Path & "\data\" & cur_company_name & "\Master_Database.mdb", True

    
    FRM_AMT_PAID_NOT_PAID.Show vbModal
    
    Exit Sub
    End If


    
    rs_cur_invoice_item.Requery
    
    While rs_cur_invoice_item.EOF <> True
        rs_cur_invoice_item.Delete
        rs_cur_invoice_item.MoveNext
    Wend
    
With Form1.salesbill
            .DataFiles(0) = App.Path & "\Master_Database.mdb"
            .ReportFileName = App.Path & "\Report\sales_bill.rpt"
            .SelectionFormula = "{Sales_master.Invoice_no} = '" & Text1(0).Text & "'"
            .username = "Admin"
            .Password = "1010101010" & Chr(10) & "1010101010"
            .Action = 1
End With

    
    FRM_AMT_PAID_NOT_PAID.Label3(5).Caption = "Sales"
    FRM_AMT_PAID_NOT_PAID.Label3(2).Caption = Text1(0).Text
    FRM_AMT_PAID_NOT_PAID.Label3(0).Caption = Combo1.Text
    FRM_AMT_PAID_NOT_PAID.dt = Format(DTPicker1.Value, "dd-MMM-yyyy")
    Unload Me
    
    FRM_AMT_PAID_NOT_PAID.Label2(2).Caption = TOTAL_TRAN_AMT
    FRM_AMT_PAID_NOT_PAID.Show vbModal
    
    
    
    
Else
    MsgBox "There is no item in the bill, you can not save it ...", vbCritical, "No item Found ..."
    
End If


End Sub



Private Sub cmd_op_Click(Index As Integer)

If Index = 0 Then
cmd.Enabled = False
DataGrid2.Enabled = False

If SALE_TYPE <> "SYSTEM" Then
If Combo1.Enabled <> False Then

Else
    SendKeys "{TAB}"
    SendKeys "{TAB}"
    SendKeys "{TAB}"
    
End If

Else
    SendKeys "{TAB}"
    SendKeys "{TAB}"
    SendKeys "{TAB}"
End If


    Status = True
    cmd.Enabled = False
    
    Call OP_STATUS(False)
    ENABLE_DISABLE (True)
    rs_cur_invoice_item.AddNew
    clear_box
    Combo5.Enabled = False
    Text1(2).Enabled = False
    
ElseIf Index = 1 Then
    
        If SALE_TYPE = "SYSTEM" Then
            Dim CHECK_ITEM_NAME As New ADODB.Recordset
            CHECK_ITEM_NAME.Open "SELECT * from SYS_CURRENT_SALES_ITEMS", db, adOpenDynamic, adLockOptimistic
            While CHECK_ITEM_NAME.EOF <> True
                
                If CHECK_ITEM_NAME.Fields(4).Value = Combo3.Text Then
                        If CHECK_ITEM_NAME.Fields(0).Value <> Combo2.Text Then
                                MsgBox "All System must have same " & Combo3.Text & "...", vbCritical, "Item name must be same ..."
                                Exit Sub
                        End If
                End If
            
                CHECK_ITEM_NAME.MoveNext
            
            Wend
            
        End If
        
    
    
        If VAL(Combo5.Text) <> 0 And VAL(Text1(2).Text) <> 0 Then
                Text1(3).Text = VAL(Combo5.Text) * VAL(Text1(2).Text)
        End If

    Status = False
    If Len(Combo3.Text) > 0 And Len(Combo2.Text) > 0 And VAL(Text1(5).Text) > 0 And VAL(Combo5.Text) > 0 And VAL(Text1(2).Text) > 0 Then
    rs_cur_invoice_item.Fields(0).Value = Combo2.Text
    rs_cur_invoice_item.Fields(1).Value = Combo5.Text
    rs_cur_invoice_item.Fields(2).Value = Text1(2).Text
    rs_cur_invoice_item.Fields(3).Value = Text1(3).Text
    rs_cur_invoice_item.Fields(4).Value = Combo3.Text
    rs_cur_invoice_item.Fields(5).Value = Combo4.Text
    rs_cur_invoice_item.Fields(6).Value = INVOICE_NUMBER
    rs_cur_invoice_item.Fields(7).Value = Format(DTPicker1.Value, "DD-MMM-YYYY")
    rs_cur_invoice_item.Fields(8).Value = VAL(Text1(6).Text)
    
    On Error GoTo updateerr:
    rs_cur_invoice_item.Update
    rs_cur_invoice_item.UpdateBatch
    cmd.Enabled = True
    Set DataGrid2.DataSource = Nothing
    rs_cur_invoice_item.Requery
    Set DataGrid2.DataSource = rs_cur_invoice_item
    DataGrid2.Enabled = True
    ENABLE_DISABLE (False)
    OP_STATUS (True)
If SALE_TYPE <> "SYSTEM" Then

Else

End If

    cmd.Enabled = True
    Exit Sub
updateerr:
        rs_cur_invoice_item.CancelBatch
        rs_cur_invoice_item.CancelUpdate
        MsgBox "Item is already Exist in the bill" & vbCrLf & "Update Qty in the existing item entry ...", vbCritical, "Error: Duplicate item entry ..."
        ENABLE_DISABLE (False)
        OP_STATUS (True)
        rs_cur_record_count.Requery
        If rs_cur_record_count.Fields(0).Value > 0 Then
            rs_cur_invoice_item.MoveFirst
        End If
        cmd.Enabled = True
    Else
        MsgBox "Enter Proper and Sufficient Data", vbCritical, "Check your Data ..."
    End If
    
ElseIf Index = 2 Then
    If SALE_TYPE = "SYSTEM" Then

     Else

            
    
    End If
    
    rs_cur_record_count.Requery
    
    If rs_cur_record_count.Fields(0).Value > 0 Then
        rs_cur_invoice_item.Delete
        rs_cur_invoice_item.MoveNext
        If rs_cur_invoice_item.EOF <> True Then
            
            Call FILLTEXT
        Else
            rs_cur_record_count.Requery
            If rs_cur_record_count.Fields(0).Value > 0 Then
                    rs_cur_invoice_item.MoveLast
                    Call FILLTEXT
            Else
                clear_box
                MsgBox "All Items Deleted ...", vbInformation, "Items Deleted.."
 
                
            End If
        End If
        
        OP_STATUS (True)
    Else
        clear_box
        MsgBox "All Items Deleted ...", vbInformation, "Items Deleted.."
        
        OP_STATUS (True)
    End If
    Call Combo4_Click
ElseIf Index = 3 Then
cmd.Enabled = False
ENABLE_DISABLE (True)