Function Problem

[Guest]

Would you please tell me the problem of the following function?
Public Function GetOrderQtyIssue(StockPN As String) As Long
Dim Stemp As String
Dim Rs As ADODB.Recordset
Set Rs = New ADODB.Recordset
Stemp = "Select Top 1 [Current_Purchasing_Order1].OrderQty From
[Current_Purchasing_Order1] Where
((([Current_Purchasing_Order1].PN)=[StockPN])) Order By
[Current_Purchasing_order1].IssueDate"
Rs.Open Stemp, CurrentProject.Connection, adOpenKeyset, adLockOptimistic
GetOrderQtyIssue = 0
If Not Rs.EOF Then
GetOrderQtyIssue = Nz(Rs("OrderQty"), 0)
Rs.Close
End If
Rs.Close
Set Rs = Nothing
End Function

 

[Stefan Hoffmann]

hi,

Would you please tell me the problem of the following function?

What problem do you have with it? Any error message?


mfG
--> stefan <--

 

[Guest]

What is the error? if there is any.
Also the StockPN parameter need to be taken out of the string of the SQL.
putting it in square brakets wont help for the SQL to recognize it


Stemp = "Select Top 1 [Current_Purchasing_Order1].OrderQty From
[Current_Purchasing_Order1] Where
((([Current_Purchasing_Order1].PN)='" & StockPN & "')) Order By
[Current_Purchasing_order1].IssueDate"

 

[Guest]

I have revised the function like your instruction. But when I used this
function in a query, it shows: "GetOrderQtyIssue has no definition"
Would you please tell me what is wrong with this function?

Thanks

Shell

What is the error? if there is any.
Also the StockPN parameter need to be taken out of the string of the SQL.
putting it in square brakets wont help for the SQL to recognize it


Stemp = "Select Top 1 [Current_Purchasing_Order1].OrderQty From
[Current_Purchasing_Order1] Where
((([Current_Purchasing_Order1].PN)='" & StockPN & "')) Order By
[Current_Purchasing_order1].IssueDate"

--
Good Luck
BS"D

Would you please tell me the problem of the following function?
Public Function GetOrderQtyIssue(StockPN As String) As Long
Dim Stemp As String
Dim Rs As ADODB.Recordset
Set Rs = New ADODB.Recordset
Stemp = "Select Top 1 [Current_Purchasing_Order1].OrderQty From
[Current_Purchasing_Order1] Where
((([Current_Purchasing_Order1].PN)=[StockPN])) Order By
[Current_Purchasing_order1].IssueDate"
Rs.Open Stemp, CurrentProject.Connection, adOpenKeyset, adLockOptimistic
GetOrderQtyIssue = 0
If Not Rs.EOF Then
GetOrderQtyIssue = Nz(Rs("OrderQty"), 0)
Rs.Close
End If
Rs.Close
Set Rs = Nothing
End Function

 

[SONIA C S]

Would you please tell me the problem of the following function?
Public Function GetOrderQtyIssue(StockPN As String) As Long
Dim Stemp As String
Dim Rs As ADODB.Recordset
Set Rs = New ADODB.Recordset
Stemp = "Select Top 1 [Current_Purchasing_Order1].OrderQty From
[Current_Purchasing_Order1] Where
((([Current_Purchasing_Order1].PN)=[StockPN])) Order By
[Current_Purchasing_order1].IssueDate"
Rs.Open Stemp, CurrentProject.Connection, adOpenKeyset, adLockOptimistic
GetOrderQtyIssue = 0
If Not Rs.EOF Then
GetOrderQtyIssue = Nz(Rs("OrderQty"), 0)
Rs.Close
End If
Rs.Close
Set Rs = Nothing
End Function

 

[Stefan Hoffmann]

hi,

I have revised the function like your instruction. But when I used this
function in a query, it shows: "GetOrderQtyIssue has no definition"
Would you please tell me what is wrong with this function?

Your function must reside in a normal module. The modul must have a
differnt name than your function. The two first lines in your module
should be

Option Compare Database
Option Explicit

Compile your database (Menue Debug/Compile).


mfG
--> stefan <--

 

[Guest]

Well done!
Thanks a lot
Shell

hi,

Your function must reside in a normal module. The modul must have a
differnt name than your function. The two first lines in your module
should be

Option Compare Database
Option Explicit

Compile your database (Menue Debug/Compile).


mfG
--> stefan <--