Parameter Problem

S

Shell

I made a function based on a query. There is a criteria on the query. The
criteria is set on a form. When I input criteria on the form and then run the
function, it always shows " one of parameters has no definition". But when I
input the criteria directly into design mode of the query and save the query,
the function runs very well. Would you please tell me how to solve this
problem if I want to put the criteria on a form?
The details are as below:
The first Query is: SELECT Monthly_Forecast.Year1, Monthly_Forecast.Month1,
Monthly_Forecast.PN, Monthly_Forecast.APOForecast, IIf([Qty] Is Null,0,[Qty])
AS Orders, IIf([APOForecast]>=[Orders],[APOForecast],[Orders]) AS Estimate1,
Monthly_Forecast.Month2
FROM Monthly_Forecast LEFT JOIN Monthly_Orders ON (Monthly_Forecast.Month2 =
Monthly_Orders.Month2) AND (Monthly_Forecast.PN = Monthly_Orders.PN)
WHERE (((Monthly_Forecast.Month2)>=Format(Date(),"yyyy-mm") And
(Monthly_Forecast.Month2)<=[Forms]![Production]![Month2]));

The second query is:SELECT Monthly_Estimate1.PN,
Sum(Monthly_Estimate1.Estimate1) AS Estimate1
FROM Monthly_Estimate1
GROUP BY Monthly_Estimate1.PN;

The third query is:SELECT Monthly_Estimate_Total1.PN,
Monthly_Estimate_Total1.Estimate1, Shipping_Orders_Total2.Qty1, IIf([Qty1] Is
Null,[Estimate1],[Estimate1]-[Qty1]) AS Estimate2, Single_BOM.ChildPN AS
1ChildPN, Single_BOM.MaterialUsage AS 1MaterialUsage,
[Estimate2]*[1MaterialUsage] AS 1Require, Single_BOM_1.ChildPN AS 2ChildPN,
Single_BOM_1.MaterialUsage AS 2MaterialUsage, [1Require]*[2MaterialUsage] AS
2Require, Single_BOM_2.ChildPN AS 3ChildPN, Single_BOM_2.MaterialUsage AS
3MaterialUsage, [2Require]*[3MaterialUsage] AS 3Require, Single_BOM_3.ChildPN
AS 4ChildPN, Single_BOM_3.MaterialUsage AS 4MaterialUsage,
[3Require]*[4MaterialUsage] AS 4Require
FROM ((((Monthly_Estimate_Total1 LEFT JOIN Shipping_Orders_Total2 ON
Monthly_Estimate_Total1.PN = Shipping_Orders_Total2.PN) INNER JOIN Single_BOM
ON Monthly_Estimate_Total1.PN = Single_BOM.ParentPN) LEFT JOIN Single_BOM AS
Single_BOM_1 ON Single_BOM.ChildPN = Single_BOM_1.ParentPN) LEFT JOIN
Single_BOM AS Single_BOM_2 ON Single_BOM_1.ChildPN = Single_BOM_2.ParentPN)
LEFT JOIN Single_BOM AS Single_BOM_3 ON Single_BOM_2.ChildPN =
Single_BOM_3.ParentPN
ORDER BY Monthly_Estimate_Total1.PN;

The function is:public Function GetEstimateRequirementA(EstimatePN As
String) As Long
Dim Stemp As String
Dim Rs As ADODB.Recordset
Set Rs = New ADODB.Recordset
Stemp = "Select Sum([Monthly_Estimate2].[1Require]) As Require From
[Monthly_Estimate2] Where ((([Monthly_Estimate2].[1ChildPN])='" &
[EstimatePN] & "')) Group By [Monthly_Estimate2].[1ChildPN]"
Rs.Open Stemp, CurrentProject.Connection, adOpenKeyset, adLockOptimistic
If Not Rs.EOF Then
GetEstimateRequirementA = Nz(Rs("Require"))
Rs.Close
End If
Set Rs = Nothing
End Function
 
Top