By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,414 Members | 2,919 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,414 IT Pros & Developers. It's quick & easy.

Problems copying query results to table - novice issue

P: n/a
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

Jan 31 '07 #1
Share this Question
Share on Google+
9 Replies


P: n/a
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.
Jan 31 '07 #2

P: n/a
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

Jan 31 '07 #3

P: n/a
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
Jan 31 '07 #4

P: n/a
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

Jan 31 '07 #5

P: n/a
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

Jan 31 '07 #6

P: n/a
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
Jan 31 '07 #7

P: n/a
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.
Jan 31 '07 #8

P: n/a
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

Feb 1 '07 #9

P: n/a
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
Feb 1 '07 #10

This discussion thread is closed

Replies have been disabled for this discussion.