Get the total

'Form1
Public cn As ADODB.Connection
Dim x As XArrayDB
 
Sub Load(lpORDOCNO As String)
Dim s As dbAccess
Dim rs As ADODB.Recordset
Dim i As Long, BALANCE As Double
Set s = New dbAccess
s.OPEN_CON "Mydb", "Winpos"
Set rs = s.Get_Stock(lpORDOCNO)
INITG
With rs
     If .BOF = True And .EOF = True Then
     Else
        i = 0
        .MoveLast
        x.ReDim 0, .RecordCount, 0, 3
        .MoveFirst
        Do While Not .EOF
                x(i, 0) = CStr(!ProCode)
                x(i, 1) = CStr(!Proname)
                x(i, 2) = QTYONHAND(CStr(!Menu_Code), s)
                If IsNull(!um) Then
                    x(i, 3) = ""
                Else
                    x(i, 3) = Trim(!um)
                End If
                i = i + 1
            .MoveNext
        Loop
        x.DeleteRows i
     End If
End With
Set rs = Nothing
Set s = Nothing
tg.Array = x
tg.ReBind
End Sub
 
Private Sub Command1_Click()
Load "B" & txtinvoiceno.Text
End Sub
Private Sub Form_Load()
INITG
End Sub
Private Sub INITG()
Dim MAXROW As Long, MAXCOL As Long
Set x = New XArrayDB
x.ReDim 0, 0, 0, 4
    For MAXROW = x.LowerBound(1) To x.UpperBound(1)
        For MAXCOL = x.LowerBound(2) To x.UpperBound(2)
            x(MAXROW, MAXCOL) = " "
        Next MAXCOL
    Next MAXROW
With tg
        .Close
        .Columns(3).Locked = True
        .Columns(4).Locked = True
        .Columns(1).AutoDropDown = True
        .Array = x
End With
End Sub
 
Private Function QTYONHAND(M_code As String, x As dbAccess) As Double
    Dim rs As ADODB.Recordset
    Set rs = x.GET_QUANTITY_ON_HAND(M_code)
    With rs
        If .BOF = True And .EOF = True Then
            QTYONHAND = 0
        Else
            QTYONHAND = !QUANTITY
        End If
        .Close
    End With
    Set rs = Nothing
End Function
 
' Class Modules
 
Option Explicit
Public cn As ADODB.Connection
Public Sub OPEN_CON(db As String, ServerName As String)
Set cn = New ADODB.Connection
cn.Provider = "sqloledb"
cn.ConnectionTimeout = 25
cn.Properties("Data source").Value = Trim(ServerName)
cn.Properties("Initial Catalog").Value = db
cn.Properties("User ID").Value = "sa"
cn.Properties("Password").Value = ""
cn.CursorLocation = adUseClient
cn.Open
 
End Sub
 
Public Function GET_QUANTITY_ON_HAND(lpCode As String) As ADODB.Recordset
Dim strSQL As String
Dim rs As ADODB.Recordset
strSQL = "select quantity from stock where Menu_Code='" & lpCode & "'"
Set rs = New ADODB.Recordset
Set rs.ActiveConnection = cn
rs.CursorLocation = adUseClient
rs.LockType = adLockReadOnly
rs.CursorType = adOpenForwardOnly
rs.Open strSQL
Set rs.ActiveConnection = Nothing
Set GET_QUANTITY_ON_HAND = rs
Set rs = Nothing
End Function

                                  

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:
44:
45:
46:
47:
48:
49:
50:
51:
52:
53:
54:
55:
56:
57:
58:
59:
60:
61:
62:
63:
64:
65:
66:
67:
68:
69:
70:
71:
72:
73:
74:
75:
76:
77:
78:
79:
80:
81:
82:
83:
84:
85:
86:
87:
88:
89:
90:
91:
92:
93:
94:
95:
96:
97:
98:
99:
100:
101:
102:
103:
104:
105:
106:
107:
108:
109: