I've been trying to get this piece to work for a few hours, but have
given up. I hope someone out there can help, I think the issue is
relatively straightforward, but being a novice, I'm stumped. Below you
will find the code I've written and the error that results. I'm hoping
that someone can give me some direction as to what syntax or parameter
is missing from the code that is expected by VBA.
Overview:
I'm trying to copy calculated results from a complex query back to a
source table. Before anyone gets too hot under the collar, this
calculated value can be "over-ridden" by the user. The query is simply
a way for the user to set the field values quickly, based on data, and
in an "all-at-once" approach. The source tables are definately still
in 3NF if not 4th or 5th.
Since it may be relevant to the question, the query is not editable
(due to aggregate functions) and the query results can be filtered by
the user. Only those records that are part of the recordset after the
filter is applied will be subjected to the procedure where the
calculated value is copied to the source table. For example, if their
are 500 records in the source table, but the query results in only 2
records, then only 2 updates to the source table will be made.
Anyhow, the query results are shown in a datasheet subform. There is a
button on the main form that should transfer one field from the query
(which is calculated) back to one of the source tables. I have written
the following (see bottom) piece of code that should get the job done
(in my novice opinion), however I get an error in the procedure at:
Set recROPFrom = db.OpenRecordset(strSQL, dbOpenDynaset)
The error says "Run Time Error '3061': Too few parameters. Expected
6."
Question: What syntax or parameter is missing from the code that is
expected by VBA?
Complete code piece:
Private Sub cmdUseReorderPoint_Click()
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
'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
'Run Filter Update sub proc to acquire Public variable
'(at module level) strFilter
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, qryEOQBuild4.Usage,
qryEOQBuild4.LastPricePerUnit, " & _
"[Usage]/(Forms!frmEOQAnalysis!EndingInventoryDate-Forms!
frmEOQAnalysis!BeginInventoryDate)" & _
"*365 AS AnnualUsage, [Usage]/(Forms!frmEOQAnalysis!
EndingInventoryDate" & _
"-Forms!frmEOQAnalysis!BeginInventoryDate) AS DailyUsage,
IIf(IsNull([LastPricePerUnit]) " & _
"Or [LastPricePerUnit]<=0 Or [Usage]<=0,0,
((2*Nz([AnnualUsage]))/(Nz([LastPricePerUnit])" & _
"*Forms!frmEOQAnalysis!perHoldCostFactor))^(1/2)) AS EOQ, " &
_
"[Usage]/(Forms!frmEOQAnalysis!EndingInventoryDate-Forms!
frmEOQAnalysis!BeginInventoryDate)*" & _
"Forms!frmEOQAnalysis!intDays*(1+Forms!frmEOQAnaly sis!
perSafetyStock) AS ReorderPoint " & _
"FROM tblItemDetails INNER JOIN qryEOQBuild4 ON
tblItemDetails.Item_Description_ID = " & _
"qryEOQBuild4.Item_Description_ID " & _
"WHERE (qryEOQBuild4.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,
qryEOQBuild4.Usage, qryEOQBuild4.LastPricePerUnit;"
'Open DAO recordset with calculated reorder points
Set db = CurrentDb
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
recROPFrom.Close
Set recROPFrom = Nothing
recROPTo.Close
Set recROPTo = Nothing
db.Close
Set db = Nothing
' Finalize with signal to user that procedure is complete
' Work in process, not complete
End Sub
Thanks in advance for any help you can provide.
Best,
Kelii
9  3012 
Kelii wrote:
I've been trying to get this piece to work for a few hours, but have
given up. I hope someone out there can help, I think the issue is
relatively straightforward, but being a novice, I'm stumped. Below you
will find the code I've written and the error that results. I'm hoping
that someone can give me some direction as to what syntax or parameter
is missing from the code that is expected by VBA.
Overview:
I'm trying to copy calculated results from a complex query back to a
source table. Before anyone gets too hot under the collar, this
calculated value can be "over-ridden" by the user. The query is simply
a way for the user to set the field values quickly, based on data, and
in an "all-at-once" approach. The source tables are definately still
in 3NF if not 4th or 5th.
Since it may be relevant to the question, the query is not editable
(due to aggregate functions) and the query results can be filtered by
the user. Only those records that are part of the recordset after the
filter is applied will be subjected to the procedure where the
calculated value is copied to the source table. For example, if their
are 500 records in the source table, but the query results in only 2
records, then only 2 updates to the source table will be made.
Anyhow, the query results are shown in a datasheet subform. There is a
button on the main form that should transfer one field from the query
(which is calculated) back to one of the source tables. I have written
the following (see bottom) piece of code that should get the job done
(in my novice opinion), however I get an error in the procedure at:
Set recROPFrom = db.OpenRecordset(strSQL, dbOpenDynaset)
The error says "Run Time Error '3061': Too few parameters. Expected
6."
Question: What syntax or parameter is missing from the code that is
expected by VBA?
Complete code piece:
Private Sub cmdUseReorderPoint_Click()
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
'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
'Run Filter Update sub proc to acquire Public variable
'(at module level) strFilter
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, qryEOQBuild4.Usage,
qryEOQBuild4.LastPricePerUnit, " & _
"[Usage]/(Forms!frmEOQAnalysis!EndingInventoryDate-Forms!
frmEOQAnalysis!BeginInventoryDate)" & _
"*365 AS AnnualUsage, [Usage]/(Forms!frmEOQAnalysis!
EndingInventoryDate" & _
"-Forms!frmEOQAnalysis!BeginInventoryDate) AS DailyUsage,
IIf(IsNull([LastPricePerUnit]) " & _
"Or [LastPricePerUnit]<=0 Or [Usage]<=0,0,
((2*Nz([AnnualUsage]))/(Nz([LastPricePerUnit])" & _
"*Forms!frmEOQAnalysis!perHoldCostFactor))^(1/2)) AS EOQ, " &
_
"[Usage]/(Forms!frmEOQAnalysis!EndingInventoryDate-Forms!
frmEOQAnalysis!BeginInventoryDate)*" & _
"Forms!frmEOQAnalysis!intDays*(1+Forms!frmEOQAnaly sis!
perSafetyStock) AS ReorderPoint " & _
"FROM tblItemDetails INNER JOIN qryEOQBuild4 ON
tblItemDetails.Item_Description_ID = " & _
"qryEOQBuild4.Item_Description_ID " & _
"WHERE (qryEOQBuild4.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,
qryEOQBuild4.Usage, qryEOQBuild4.LastPricePerUnit;"
'Open DAO recordset with calculated reorder points
Set db = CurrentDb
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
recROPFrom.Close
Set recROPFrom = Nothing
recROPTo.Close
Set recROPTo = Nothing
db.Close
Set db = Nothing
' Finalize with signal to user that procedure is complete
' Work in process, not complete
End Sub
Thanks in advance for any help you can provide.
Best,
Kelii
Sometimes what I'll do after I've built a dynamic SQL string, and it
doesn't function correctly due to an error, is put a line like
Debug.Print strSQL
under the line assigning the value to strSQL. It depends on the error
I'm debugging, maybe I'll do
MsgBox strSQL
But if you debug.print it, you can go to the immediate window and copy
the SQL statement to the clipboard. Now do a Query/New/Design/Close and
press ViewSQL from the menu and paste and run the SQL statement.
Sometimes the error is obvious.
On another note, is the To recordset only to be updated by records that
already exist in to To recordset?
On a coding note, you might want to consider
If IsNull(x) then
msgbox "Error x"
Elseif IsNull(y) then
msgbox "Error y"
Elseif IsNull(z) then
msgbox "Error z"
Else
'passed error checks
Endif
Other than that, the code looks fine. It's got to be your SQL statement.
Salad,
Thanks for the response.
Believe it or not, I've tested the strSQL in the exact manner that you
suggest below. The statement produces the exact records that I expect.
When I go back to the problem line of code (Set recROPFrom =
db.OpenRecordset(strSQL, dbOpenDynaset) , it doesn't work with
anything that I put in there, including a plain old query name. For
example, I've tried
Set recROPFrom = db.OpenRecordset("qryEOQBuild5", dbOpenDynaset)
Where qryEOQBuild5 is the unfiltered query. From here I get the same
error.
Any other thoughts?
Kelii
Kelii wrote:
Salad,
Thanks for the response.
Believe it or not, I've tested the strSQL in the exact manner that you
suggest below. The statement produces the exact records that I expect.
When I go back to the problem line of code (Set recROPFrom =
db.OpenRecordset(strSQL, dbOpenDynaset) , it doesn't work with
anything that I put in there, including a plain old query name. For
example, I've tried
Set recROPFrom = db.OpenRecordset("qryEOQBuild5", dbOpenDynaset)
Where qryEOQBuild5 is the unfiltered query. From here I get the same
error.
Any other thoughts?
Just one. What happens if you use dbOpensnapshot instead of dbOpenDynaset?
Another. What happens if you put in another Select. Like
strSQL = "Select * from Customers"
and under the Set recRPOFRom line an
exit sub
simply to see if it opens the recordset.
Kelii
Salad,
Hmmmm, interesting, I believe you've identified the problem.
dbOpenSnapshot has no effect. However, if I use a simple query as you
suggest, then the code makes it through the set recordset statement
that has been giving me trouble.
So, as you correctly pointed out in your first post, there is
something wrong with the query. In particular, I believe that the
issue is that the query takes several criteria from user inputs on the
form. For example, the calculated field in the query that will be
passed to the source table subtracts end date from begin date. Both of
these are set by the user in the interface / parent form. A little bit
of research in this group has suggested that you can't use form
references when building a recordset in VBA. Rather, it appears as
though setting these to variables within the code, then referencing
the variable (as opposed to the form) will work.
Does this sound right?
For example:
Dim datBeginDate as Date
Dim datEndDate as Date
datBeginDate = Forms!ParentForm!cboBeginDate
datEndDate = Forms!ParentForm!cboEndDate
strSQL = "Select " & datEndDate & " - " & datBeginDate & " AS intDays"
& _
Etc.....................
Apologies for being dismissive with your first post, I really thought
that the query worked fine.
Best,
Kelii
UPDATE
Well, I went through and set all new variables in the code to replace
the "Forms!ParentForm!FormObject" references in my SQL statement. I
tested the query by copying the result into the QBE window in Access,
and it provides accurate results. However, THE CODE STILL DOESN'T
WORK. Why, you may ask?
Well, in brief, I believe the issue is due to the fact that the query
(named qryEOQBuild5) references other querries in the SQL statement
(i.e., qryEOQBuild4). So for example, in the SELECT piece of my SQL
string, you'll find [qryEOQBuild4.LastPricePerUnit] and in the FROM
piece of my SQL string you'll find [qryEOQBuild4]. The issue with
qryEOQBuild4 is that it references objects on the form in its SQL
statement. So for example, in the SQL statement for qryEOQBuild4,
you'll find references like "Forms!ParentForm!FormObject" all over the
place.
QUESTION
Are the Forms!ParentForm!FormObject references in the embedded query
going to be a problem if I want to build a recordset using DAO? This
appears to be the case given that I continue to get the Error 3061,
however my missing references are now down to 3.
The abbreviated code now looks like the following:
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 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
'Cut for brevity
'Run Filter Update sub proc to acquire Public variable
'(at module level) strFilter
FilterUpdate
'Build SQL string to match exact recordset as shown in EOQ form
intAnalysisPeriod = (Forms!frmEOQAnalysis!EndingInventoryDate -
Forms!frmEOQAnalysis!BeginInventoryDate)
intLeadTime = Forms!frmEOQAnalysis!intDays
sglSafetyStockFactor = Forms!frmEOQAnalysis!perSafetyStock
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, qryEOQBuild4.Usage,
qryEOQBuild4.LastPricePerUnit, " & _
"[Usage]/" & intLeadTime & "*365 AS AnnualUsage, [Usage]/" &
intAnalysisPeriod & " " & _
"AS DailyUsage, IIf(IsNull([LastPricePerUnit]) " & _
'Remainder cut for brevity
'Open DAO recordset with calculated reorder points
Set db = CurrentDb
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
recROPFrom.Close
Set recROPFrom = Nothing
recROPTo.Close
Set recROPTo = Nothing
db.Close
Set db = Nothing
End Sub
Kelii wrote:
Salad,
Hmmmm, interesting, I believe you've identified the problem.
dbOpenSnapshot has no effect. However, if I use a simple query as you
suggest, then the code makes it through the set recordset statement
that has been giving me trouble.
So, as you correctly pointed out in your first post, there is
something wrong with the query. In particular, I believe that the
issue is that the query takes several criteria from user inputs on the
form. For example, the calculated field in the query that will be
passed to the source table subtracts end date from begin date. Both of
these are set by the user in the interface / parent form. A little bit
of research in this group has suggested that you can't use form
references when building a recordset in VBA. Rather, it appears as
though setting these to variables within the code, then referencing
the variable (as opposed to the form) will work.
Does this sound right?
Yes. And most likely your parameters are coming from a sub query called
by the query.
Look at/for PARAMETERS Declaration in help.
>
For example:
Dim datBeginDate as Date
Dim datEndDate as Date
datBeginDate = Forms!ParentForm!cboBeginDate
datEndDate = Forms!ParentForm!cboEndDate
strSQL = "Select " & datEndDate & " - " & datBeginDate & " AS intDays"
& _
Etc.....................
Apologies for being dismissive with your first post, I really thought
that the query worked fine.
Best,
Kelii
Kelii wrote:
UPDATE
Well, I went through and set all new variables in the code to replace
the "Forms!ParentForm!FormObject" references in my SQL statement. I
tested the query by copying the result into the QBE window in Access,
and it provides accurate results. However, THE CODE STILL DOESN'T
WORK. Why, you may ask?
Well, in brief, I believe the issue is due to the fact that the query
(named qryEOQBuild5) references other querries in the SQL statement
(i.e., qryEOQBuild4). So for example, in the SELECT piece of my SQL
string, you'll find [qryEOQBuild4.LastPricePerUnit] and in the FROM
piece of my SQL string you'll find [qryEOQBuild4]. The issue with
qryEOQBuild4 is that it references objects on the form in its SQL
statement. So for example, in the SQL statement for qryEOQBuild4,
you'll find references like "Forms!ParentForm!FormObject" all over the
place.
QUESTION
Are the Forms!ParentForm!FormObject references in the embedded query
going to be a problem if I want to build a recordset using DAO? This
appears to be the case given that I continue to get the Error 3061,
however my missing references are now down to 3.
The abbreviated code now looks like the following:
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 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
'Cut for brevity
'Run Filter Update sub proc to acquire Public variable
'(at module level) strFilter
FilterUpdate
'Build SQL string to match exact recordset as shown in EOQ form
intAnalysisPeriod = (Forms!frmEOQAnalysis!EndingInventoryDate -
Forms!frmEOQAnalysis!BeginInventoryDate)
intLeadTime = Forms!frmEOQAnalysis!intDays
sglSafetyStockFactor = Forms!frmEOQAnalysis!perSafetyStock
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, qryEOQBuild4.Usage,
qryEOQBuild4.LastPricePerUnit, " & _
"[Usage]/" & intLeadTime & "*365 AS AnnualUsage, [Usage]/" &
intAnalysisPeriod & " " & _
"AS DailyUsage, IIf(IsNull([LastPricePerUnit]) " & _
'Remainder cut for brevity
'Open DAO recordset with calculated reorder points
Set db = CurrentDb
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
recROPFrom.Close
Set recROPFrom = Nothing
recROPTo.Close
Set recROPTo = Nothing
db.Close
Set db = Nothing
End Sub
I think you might, if this query is opened with a "Docmd.Openquery"
elsewhere within the system, want to make a copy of it and call the copy
(with mods). If the query is only used in this app, simply update it.
What to update? Where you have Forms!xxx!yyy stuff change that to [Enter
Whatever] statements in the criteria. Then look at the Parameters
declaration and it's examples and set the parameters prior to calling
the recordset.
Good luck.
Salad,
You've been incredibly kind to help me through this far. I'm tired and
I have to go to sleep, but not before I let you know that the sub
still isn't working.
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
Kelii wrote:
Salad,
You've been incredibly kind to help me through this far. I'm tired and
I have to go to sleep, but not before I let you know that the sub
still isn't working.
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.
Now, if I were in your place...:-)
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
might find one subquery that affects the others and provides you with a
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.
>
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
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: DBNovice |
last post by:
I have a database that keeps records on the issue and failure of an item.
Currently, the database is poorly desisned; therefore I'm performing queries
to break the data into normalized tables and...
|
by: netpurpose |
last post by:
I need to extract data from this table to find the lowest prices of
each product as of today. The product will be listed/grouped by the
name only, discarding the product code - I use...
|
by: Sami |
last post by:
Maybe I am going about this totally wrong....
What I have written is a query pulling SSN, Student-ID, LastName,
FirstName, MiddleInitital from a table.
Sort is so LastName, FirstName,...
|
by: Jan Szymczuk |
last post by:
Thanks again guys, for your recent assistance. Now as you were so kind to
assist the first time (the solution was so simple and elegant) I was
wondering if you could assist with another couple of...
|
by: zerbie45 |
last post by:
I have a high number of computers that at logon write some information
to a sql 2005 database. Information such as computer name, user name,
logon date and logon time are entered.
Because...
|
by: Paul |
last post by:
Hi,
I seem to be having an issue with a query's results being paged out to
disk during runtime. I notice in processlist it saying "Copying to tmp
table on disk".
select col1, col2, count(ip)...
|
by: AJ |
last post by:
Hi all,
I have this monster query (at least i think it is).
SELECT
c.ID, c.Company_Name, p., 1 As QueryNbr
FROM
(Company AS c LEFT JOIN Sale AS s ON c.ID = s.Company_ID)
LEFT JOIN
Package...
|
by: webhead74 |
last post by:
Hi,
I'm having intermittent problems with queries from my php script to a
postgresql database.
I have a form where I can enter a search query - for instance a last
name. This leads to a...
|
by: Shiriah |
last post by:
I feel that this is a rather trivial concept that I cannot quite wrap my head around to make work in PHP. I consider myself an intermediate user in SQL (both MSSQL and MySQL) and somewhat novice in...
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and...
| |
