[@@أبورائد@@]

السلام عليكم ورحمة الله وبركاته

بعد إذن الأخ (تركي الحلواني)

تفضل المثال

كود :

Option Explicit '' البعض يحذف هذا السطر مما يتسبب لنفسه بالمتاعب

'' AccessDatabase4.0: store.mdb
''  Tables:-   Operations: (ID(Key)>AutoNumber , date>DateTime , ItemID>Integer , QtyIn>Single , QtyOut>Single , Operation>Byte )
''             Items:      (ID(Key)>AutoNumber , Description>Text , Quantity(محجوز)>Single , OrderPoint(محجوز)>Single )

'' 2 MSFlexGrid

Dim myConnection As New ADODB.Connection
Dim myCommand As New ADODB.Command
Dim myRecordSet As New ADODB.Recordset


Private Sub Form_Load()
    myConnection.ConnectionString = " Provider=Microsoft.Jet.OLEDB.4.0;         " & _
                                    " Data Source=" & App.Path & "\store.mdb;   " & _
                                    " Persist Security Info=False               "
    myConnection.CursorLocation = adUseClient
    
    Call OperatiosFlexGrid
    Call OperatiosDBLoad
    
    Call ItemsFlexGrid
    Call ItemsDBLoad
    
End Sub

Private Sub OperatiosFlexGrid()
    With Me.MSFlexGrid1
        .Rows = 1
        .Cols = 6
        .ColWidth(0) = 1000: .ColAlignment(0) = flexAlignCenterCenter
        .ColWidth(1) = 1200: .ColAlignment(1) = flexAlignCenterCenter
        .ColWidth(2) = 800: .ColAlignment(2) = flexAlignCenterCenter
        .ColWidth(3) = 2200: .ColAlignment(3) = flexAlignLeftCenter
        .ColWidth(4) = 800:  .ColAlignment(4) = flexAlignCenterCenter
        .ColWidth(5) = 800:  .ColAlignment(5) = flexAlignCenterCenter
        .TextMatrix(0, 0) = "رقم العملية"
        .TextMatrix(0, 1) = "التاريخ"
        .TextMatrix(0, 2) = "رقم الصنف"
        .TextMatrix(0, 3) = "وصف الصنف"
        .TextMatrix(0, 4) = "الكمية"
        .TextMatrix(0, 5) = "العملية"
    End With
End Sub

Private Sub OperatiosDBLoad()

    Dim sql As String
    sql = " SELECT *                            " & _
          "      , IIf(QtyIn = 0, QtyOut, QtyIn) AS Quantity                        " & _
          "      , IIf(Operations.Operation = 0, 'بيع' ,'شراء') AS OperationName    " & _
          "                                     " & _
          "   FROM Operations                   " & _
          "                                     " & _
          " INNER JOIN Items                    " & _
          " ON Operations.ItemID = Items.ID     " & _
          "                                     " & _
          " ORDER BY [Date]                     "
          
    myConnection.Open
    
    myCommand.ActiveConnection = myConnection
    myCommand.CommandText = sql
    myCommand.CommandType = adCmdText
    
    myRecordSet.CursorType = adOpenStatic
    myRecordSet.CursorLocation = adUseClient
    myRecordSet.LockType = adLockOptimistic
    myRecordSet.Open myCommand
  
    If myRecordSet.RecordCount > 0 Then
        With Me.MSFlexGrid1
            
            .Rows = myRecordSet.RecordCount + 1
            Dim r As Integer
            For r = 1 To myRecordSet.RecordCount
                    .TextMatrix(r, 0) = Format$(myRecordSet.Fields("Operations.id"), "0000")    '' رقم العملية
                    .TextMatrix(r, 1) = Format$(myRecordSet.Fields("Date"), "yyyy/MM/dd")   '' تاريخ العملية
                    .TextMatrix(r, 2) = Format$(myRecordSet.Fields("ItemID"), "0000")       '' رقم الصنف
                    .TextMatrix(r, 3) = myRecordSet.Fields("Description")   '' وصف الصنف
                    .TextMatrix(r, 4) = myRecordSet.Fields("Quantity")      '' الكمية
                    .TextMatrix(r, 5) = myRecordSet.Fields("OperationName") '' نوع العملية
                myRecordSet.MoveNext
            Next r
            Me.Label1.Caption = "مجموع العمليات: " & Str(myRecordSet.RecordCount)
            
        End With
    End If
        
    myConnection.Close
    
End Sub



Private Sub ItemsFlexGrid()
    With Me.MSFlexGrid2
        .Rows = 1
        .Cols = 5
        .ColWidth(0) = 1000: .ColAlignment(0) = flexAlignCenterCenter
        .ColWidth(1) = 2200: .ColAlignment(1) = flexAlignLeftCenter
        .ColWidth(2) = 1200: .ColAlignment(2) = flexAlignCenterCenter
        .ColWidth(3) = 1200: .ColAlignment(3) = flexAlignCenterCenter
        .ColWidth(4) = 1200:  .ColAlignment(4) = flexAlignCenterCenter
        .TextMatrix(0, 0) = "رقم الصنف"
        .TextMatrix(0, 1) = "وصف الصنف"
        .TextMatrix(0, 2) = "كمية الشراء"
        .TextMatrix(0, 3) = "كمية البيع"
        .TextMatrix(0, 4) = "الكمية المتبقية"
    End With
End Sub


Private Sub ItemsDBLoad()
    
    Dim sql As String
    sql = " SELECT    SUM(QtyIn)  AS SUMSale    " & _
          "         , SUM(QtyOut) AS SUMBuy     " & _
          "         , (SUM(QtyIn) - SUM(QtyOut)) AS SUMQuantity     " & _
          "         , Description, Operations.ItemID                " & _
          "                                     " & _
          "   FROM Operations                   " & _
          "                                     " & _
          " INNER JOIN Items                    " & _
          " ON Operations.ItemID = Items.ID     " & _
          "                                     " & _
          " GROUP BY Description,ItemID         " & _
          "                                     " & _
          " ORDER BY ItemID                     "
          
    myConnection.Open
    
    myCommand.ActiveConnection = myConnection
    myCommand.CommandText = sql
    myCommand.CommandType = adCmdText
    
    myRecordSet.CursorType = adOpenStatic
    myRecordSet.CursorLocation = adUseClient
    myRecordSet.LockType = adLockOptimistic
    myRecordSet.Open myCommand
  
    If myRecordSet.RecordCount > 0 Then
        With Me.MSFlexGrid2
            
            .Rows = myRecordSet.RecordCount + 1
            Dim r As Integer
            For r = 1 To myRecordSet.RecordCount
                    .TextMatrix(r, 0) = Format$(myRecordSet.Fields("ItemID"), "0000")   '' رقم الصنف
                    .TextMatrix(r, 1) = myRecordSet.Fields("Description")   '' نوع الصنف
                    .TextMatrix(r, 2) = myRecordSet.Fields("SUMSale")       '' كمية الشراء
                    .TextMatrix(r, 3) = myRecordSet.Fields("SUMBuy")        '' كمية البيع
                    .TextMatrix(r, 4) = myRecordSet.Fields("SUMQuantity")   '' الكمية المتبقية
                myRecordSet.MoveNext
            Next r
        
        End With
    End If
        
    myConnection.Close
    
End Sub

السلام عليكم ورحمة الله وبركاته