Error in Form Code (Error 3061)

[klynch0803]

I have an error in this code which is error 3061 "To Few Paremater Expected 2"

I have a table "tdatInventoryRec" which has two fields "recdate" and "Cost"

On my form I have Fields "Weekstart" "WeekEnd" "GrossPurchase"

I'm trying to get this code to gather all records in table "tdatInventoryRec" that are between the "WeekStart" and the "WeekEnd" dates and sum the "Cost" and insert that value on the form in field "GrossPurchase"

Any idea what is wrong? I have checkied field names etc and cant find anything incorrect..

Private Sub WeekEnd_AfterUpdate()
'<----------- Test----------->
Dim dbs As DAO.Database
Dim rstPurchase As DAO.Recordset
Set dbs = CurrentDb
Set rstPurchase = dbs.OpenRecordset("SELECT SUM(Cost) AS Purchase FROM tdatInventoryRec WHERE RecDate BETWEEN fdatPayEmp!WeekStart AND fdatPayEmp!WeekEnd")

Purchase = GrossPurchase.Value

'<----------End Test--------->
End Sub

 

[chris davies]

I have an error in this code which is error 3061 "To Few Paremater Expected 2"

I have a table "tdatInventoryRec" which has two fields "recdate" and "Cost"

On my form I have Fields "Weekstart" "WeekEnd" "GrossPurchase"

I'm trying to get this code to gather all records in table "tdatInventoryRec" that are between the "WeekStart" and the "WeekEnd" dates and sum the "Cost" and insert that value on the form in field "GrossPurchase"

Any idea what is wrong? I have checkied field names etc and cant find anything incorrect..

End Sub[/CODE]

Have you looked at your query in SQL View? You are basing your form on a query yes?

Create a query where your criteria for the date is similar to the following:

SELECT sum(Cost) FROM tdatInventoryRec
WHERE recdate>=WeekStart AND recdate<=WeekEnd

That is to say, whatever your WeekStart andWeekEnd data may be. I use a similar method for calculating hours, but I use a Calendar object to select the start date and calculate the enddate.

 

[klynch0803]

Have you looked at your query in SQL View? You are basing your form on a query yes?

Create a query where your criteria for the date is similar to the following:

SELECT sum(Cost) FROM tdatInventoryRec
WHERE recdate>=WeekStart AND recdate<=WeekEnd

That is to say, whatever your WeekStart andWeekEnd data may be. I use a similar method for calculating hours, but I use a Calendar object to select the start date and calculate the enddate.

I am not basing it on a query so say.. I do not have a query I have built for this no..

I was trying to write the code to actually do the query itself within the VB of the form versus creating a query and adding it as a hidden part of the form to try and increase the speed of the process.

 

[klynch0803]

Maybe what I need to do is have a form prior to this that they would select the "weekStart" and "WeekEnd" and select Go to move the actual payroll form.

On the form would be the query you are referencing or suggesting I guess is more accurate. The WeekStart and the WeekEnd on the form would be filled by the previous form.

Im not so sure I know how to do that but it may be the way to go since I cant seem to find the error in the code I first created.

 

[missinglinq]

I believe the rule is that you can't run a SELECT query from within VBA, only action queries such as UPDATE, APPEND, etc.

 

[klynch0803]

Ok so I created a Query and added it to my subform here is the sql for the query:

SELECT DISTINCTROW tdatPurchases.ChkDate, Sum(tdatPurchases.ChkAmt) AS [Sum Of ChkAmt]
FROM tdatPurchases
GROUP BY tdatPurchases.ChkDate
HAVING (((tdatPurchases.ChkDate)>=[fdatpayemp]![WeekStart] And (tdatPurchases.ChkDate)<=[fdatpayemp]![WeekEnd]));[/CODE

Now my dates from my form are not loading into the query why? The form that has the dates is named "fdatPayEmp" an the firld names are "WeekStart" "WeekEnd"

 

[ajetrumpet]

I believe the rule is that you can't run a SELECT query from within VBA, only action queries such as UPDATE, APPEND, etc.

yes, that's the rule.

Now my dates from my form are not loading into the query why? The form that has the dates is named "fdatPayEmp" an the firld names are "WeekStart" "WeekEnd"

Are you sure the syntax for the subform is correct? Check out Bob's FAQ for that...

 

[klynch0803]

OK i have gooten my subform showing the value I want in a field call "Total" on subform "qdatPurchases subform"..

How can I get that value to be shown on the main form in a field named "GrossPurchase"?

 

[ajetrumpet]

You can't do it, if that box is bound to a field from that query.

The query is non-updateable, because of the aggregate grouping.