I have a function that uses a query to a SQL datatase to determine the quantity of an item that has been reserved. The code will not run, if I use the immediate window and type ?getqtyreserved('I_000012') I get an error. "Compile Error: Expected: Expression". I have tried adding breakpoints in the code, but I get the error before the breakpoints are reached.
I have no clue where to go from here and would appreciate any help.
I have no clue where to go from here and would appreciate any help.
Code:
Public Function GetQtyReserved(strPartNo As String) As Double
10 On Error GoTo err_GetQtyReserved
Dim qdf As dao.QueryDef
Dim db As Database
Dim rst As dao.Recordset
Dim strSQL As String
20 Dim dblOutput As Double: dblOutput = 0
Dim strConnection As String
30 strConnection = "ODBC;DRIVER=SQL Server;SERVER=ZAB-SVR-01\ZABAPPS;DATABASE=Stores;Trusted_Connection=Yes"
40 strSQL = "SELECT [I_PtNo], sum([SM_Qty]) as QR FROM [dbo].[tblItemRequest] GROUP BY [I_PtNo] HAVING [I_PtNo] = '" & strPartNo & "'"
50 Debug.Print strSQL
60 Set db = CurrentDb
70 Set qdf = db.CreateQueryDef("")
80 qdf.Connect = strConnection
90 qdf.SQL = strSQL
100 qdf.ReturnsRecords = True
110 Set rst = qdf.OpenRecordset
120 With rst
130 .MoveFirst
140 Do Until .EOF
150 Debug.Print !QR
160 dblOutput = !QR
170 .MoveNext
180 Loop
190 End With
200 rst.Close
210 db.Close
220 Set rst = Nothing
230 Set qdf = Nothing
240 Set db = Nothing
250 GetQtyReserved = dblOutput
exit_GetQtyReserved:
260 Exit Function
err_GetQtyReserved:
270 MsgBox Err.Number & " " & Err.Description & vbCr & vbCr & "Error Line: " & Erl
280 Call Logger("Error", "GetQtyReserved," & Erl, Err.Number, Err.Description)
290 Resume exit_GetQtyReserved
End Function