If I were Bill Clinton, I'd say "I feel your pain.". I wish I could
offer something further, like a solution, but I can't.
I looked at your code. I am not going to get into the intricacies of it
except to say it looks OK. You aren't a novice, you know what you are
doing.
I might consider making a copy of you db and opening up the copy. Then
look at the query you are opening and it's subqueries. Start killing
off links to certain queries and opening up the recordset. I think you
migraine because it's hard to find.
Let's say Query1 calls query2 that calls query3 that calls query4.
Start off by killing queries. Query1 calls query2 that calls query3.
Then Query1 calls query2. Then query1. I think you'll find a query
that causes the others grief.
Good luck.
Quote:
>
I'm still running into the same issue with the too few parameters
error. I've done my best to get the parameters set up (see code
below), but it still won't work.
>
Who knows, I'll be doing more research tomorrow. If all else fails,
I'll collapse the query into 1 with several subqueries, and try to see
if that works.
>
Kelii
>
Private Sub cmdUseReorderPoint_Click()
'These variables are used in the record analysis and update
Dim db As Database
Dim recROPFrom As DAO.Recordset
Dim recROPTo As DAO.Recordset
Dim strSQL As String
Dim intRecordID As Integer
Dim dblReorderPoint As Double
>
'These variables are used to modify query def based on user input
Dim qdEOQParametersBuild1 As DAO.QueryDef
Dim qdEOQParametersBuild2 As DAO.QueryDef
Dim qdEOQParametersBuild3 As DAO.QueryDef
>
'These variables are used to build the query string, DAO doesn't
'accept form references (e.g., Forms!ParentForm!ChildForm) when
building
'the recordset
Dim intAnalysisPeriod As Integer
Dim intLeadTime As Integer
Dim sglSafetyStockFactor As Single
>
'Check user inputs to make sure that all required fields are
complete
If IsNull(Me.Company_Location.Value) Then
MsgBox "Please select a company.", vbOKOnly, "Company
Selection"
Exit Sub
End If
>
If IsNull(Me.BeginInventoryDate.Value) Then
MsgBox "Please enter a valid date.", vbOKOnly, "Date Entry"
Exit Sub
End If
>
If IsNull(Me.EndingInventoryDate.Value) Then
MsgBox "Please enter a valid date.", vbOKOnly, "Date Entry"
Exit Sub
End If
>
'Set current database variable
Set db = CurrentDb
>
'Set parameters for qryEOQParametersBuild1
Set qdEOQParametersBuild1 = db.QueryDefs("qryEOQParametersBuild1")
qdEOQParametersBuild1.Parameters("parBeginDate").V alue = Forms!
frmEOQAnalysis!BeginInventoryDate
qdEOQParametersBuild1.Parameters("parLocation").Va lue = Forms!
frmEOQAnalysis!Company_Location
>
'Set parameters for qryEOQParametersBuild2
Set qdEOQParametersBuild2 = db.QueryDefs("qryEOQParametersBuild2")
qdEOQParametersBuild2.Parameters("parEndDate").Val ue = Forms!
frmEOQAnalysis!EndingInventoryDate
qdEOQParametersBuild2.Parameters("parLocation").Va lue = Forms!
frmEOQAnalysis!Company_Location
>
'Set parameters for qryEOQParametersBuild3
Set qdEOQParametersBuild3 = db.QueryDefs("qryEOQParametersBuild3")
qdEOQParametersBuild3.Parameters("parBeginDate").V alue = Forms!
frmEOQAnalysis!BeginInventoryDate
qdEOQParametersBuild3.Parameters("parEndDate").Val ue = Forms!
frmEOQAnalysis!EndingInventoryDate
qdEOQParametersBuild3.Parameters("parLocation").Va lue = Forms!
frmEOQAnalysis!Company_Location
>
'No parameters necessary for qryEOQParametersBuild4
'Final query created in following code
>
'Build variables used in following SQL string
intAnalysisPeriod = (Forms!frmEOQAnalysis!EndingInventoryDate -
Forms!frmEOQAnalysis!BeginInventoryDate)
intLeadTime = Forms!frmEOQAnalysis!intDays
sglSafetyStockFactor = Forms!frmEOQAnalysis!perSafetyStock
'Run Filter Update sub proc to acquire additional updated
variables for SQL string
FilterUpdate
>
'Build SQL string to match exact recordset as shown in EOQ form
strSQL = "SELECT tblItemDetails.Item_Description_Number, " & _
"tblItemDetails.Item_Description_ID,
tblItemDetails.Item_Unit_of_Measure, " & _
"tblItemDetails.Item_Category, tblItemDetails.Item_Type,
tblItemDetails.Item_Location, " & _
"tblItemDetails.Item_SKU, qryEOQParametersBuild4.Usage,
qryEOQParametersBuild4.LastPricePerUnit, " & _
"[Usage]/" & intLeadTime & "*365 AS AnnualUsage, [Usage]/" &
intAnalysisPeriod & " " & _
"AS DailyUsage, IIf(IsNull([LastPricePerUnit]) " & _
"Or [LastPricePerUnit]<=0 Or [Usage]<=0,0,
((2*Nz([AnnualUsage]))/(Nz([LastPricePerUnit])" & _
"*" & sglSafetyStockFactor & "))^(1/2)) AS EOQ, " & _
"[Usage]/" & intAnalysisPeriod & "*" & intLeadTime & _
"*(1+" & sglSafetyStockFactor & ") AS ReorderPoint " & _
"FROM tblItemDetails INNER JOIN qryEOQParametersBuild4 ON
tblItemDetails.Item_Description_ID = " & _
"qryEOQParametersBuild4.Item_Description_ID " & _
"WHERE (qryEOQParametersBuild4.SortZero)>0 AND " & strFilter &
" "
' "GROUP BY tblItemDetails.Item_Description_Number,
tblItemDetails.Item_Description_ID, " & _
' "tblItemDetails.Item_Unit_of_Measure,
tblItemDetails.Item_Category, tblItemDetails.Item_Type, " & _
' "tblItemDetails.Item_Location, tblItemDetails.Item_SKU,
qryParametersEOQBuild4.Usage, " & _
' "qryParametersEOQBuild4.LastPricePerUnit;"
>
'Open DAO recordset with calculated reorder points
Set recROPFrom = db.OpenRecordset(strSQL, dbOpenDynaset)
Set recROPTo = db.OpenRecordset("tblItemDetails", dbOpenDynaset)
>
'Loop through recordset and make edits to table
Do While Not recROPFrom.EOF
'Set unique ID variable and variable to modify table field
intRecordID = recROPFrom!Item_Description_Number
dblReorderPoint = recROPFrom!ReorderPoint
'Find record based on unique ID
recROPTo.FindFirst ("Item_Description_Number = " &
intRecordID)
If Not recROPTo.NoMatch Then
'Set table field to variable
recROPTo.Edit
recROPTo!Item_Par = dblReorderPoint
recROPTo.Update
End If
recROPTo.MoveNext
Loop
>
' Close and clean up recordsets, querydefs
recROPFrom.Close
Set recROPFrom = Nothing
recROPTo.Close
Set recROPTo = Nothing
Set qdEOQParametersBuild1 = Nothing
Set qdEOQParametersBuild2 = Nothing
Set qdEOQParametersBuild3 = Nothing
db.Close
Set db = Nothing
>
'Singal to user that copy is complete
'TBD
>
End Sub
>
