c2LVH5Js

1Public Function CurrentStock(Drug_Id As String) As String
2    Dim TemporaryCn As New Connection
3    Dim TemporaryRs As New Recordset
4    Dim TemporaryCmd As New Command
5    
6    TemporaryCn.Open ConnectString
7      
8   'TemporaryCn.Open ConnectString
9   
10   TemporaryCmd.CommandText = "Create Temporary Table If Not Exists temp_table (current_stock Int(11))"
11   Debug.Print TemporaryCmd.CommandText
12   TemporaryCmd.CommandType = adCmdText
13   TemporaryCmd.ActiveConnection = TemporaryCn
14   TemporaryCmd.Execute
15   
16   TemporaryCmd.CommandText = "Insert Into temp_table(current_stock) Select sum(current_quantity) from epics_lots where id_epics_drug=" + Drug_Id
17   Debug.Print TemporaryCmd.CommandText
18   TemporaryCmd.CommandType = adCmdText
19   TemporaryCmd.ActiveConnection = TemporaryCn
20   TemporaryCmd.Execute
21    
22   TemporaryCmd.CommandText = "Select * from temp_table"
23   Debug.Print TemporaryCmd.CommandText
24   TemporaryCmd.CommandType = adCmdText
25   TemporaryCmd.ActiveConnection = TemporaryCn
26   TemporaryRs.Open TemporaryCmd, , adOpenStatic
27
28    If TemporaryRs.EOF <> True Then
29        'set quantity
30        If TemporaryRs(0) <> "Null" Then
31            CurrentStock = TemporaryRs(0)
32        Else
33            CurrentStock = "0"
34        End If
35    Else
36        'MsgBox ("quantity not found!")
37    End If
38   
39   TemporaryCmd.CommandText = "Delete from temp_table"
40   Debug.Print TemporaryCmd.CommandText
41   TemporaryCmd.CommandType = adCmdText
42   TemporaryCmd.ActiveConnection = TemporaryCn
43   TemporaryCmd.Execute
44
45 
46 
47End Function