Do I just have a typo I missed? (1 Viewer)

[dcavaiani]

HAPPY NEW YEAR TO ALL!!

Can anyone quickly see why the code aborts at the debug line ?

Private Sub Command2_Click()
DoCmd.OpenQuery "weeklytotallabor"
DoCmd.OpenQuery "weeklytotalmatl"

' get weekly totals of labor + materials for CUSTOMER
DoCmd.RunSQL ("delete * from weeklygraph;")
DoCmd.RunSQL "insert into weeklygraph (custname, yearweeknumber, sumlabor) select cust, yearweeknumber, sumoflabor from weeklytotallabor;"
DoCmd.RunSQL "insert into weeklygraph (custname, yearweeknumber, summatl) select customer, yearweeknumber, sumofmatl from weeklytotalmatl;"
 DoCmd.Close acTable, "weeklygraph", acSaveYes
 DoCmd.Close acQuery, "weeklytotalLabor", acSaveYes
 DoCmd.Close acQuery, "weeklytotalMatl", acSaveYes
On Error GoTo ErrorHandler
Dim db As DAO.Database
Dim rstquery As DAO.Recordset
Dim rsttable As DAO.Recordset
   Set db = CurrentDb
   ' Close ("weeklytotalmatl")
   Set rsttable = db.OpenRecordset("weeklygraph")
' *** CODE ABORTS AT THE NEXT LINE ***
   Set rstquery = db.OpenRecordset("weeklytotalMatl")
   
   rstquery.MoveFirst
Do Until rstquery.EOF
' rstprep.Update
' Update table code will be added next
     rstquery.MoveNext
   Loop
'_________________________________
On Error GoTo ErrorHandler
ErrorHandlerExit:
quitnow:
    Exit Sub
ErrorHandler:
    If Err = 3021 Then    ' no current record
    
            Else
'        MsgBox "Error No: " & Err.Number & "; Description: " & Err.Description
        Resume ErrorHandlerExit
    End If

DoCmd.OpenReport "weeklytotalgraph", acViewPreview

End Sub

 

[isladogs]

I see no Debug line!

 

[theDBguy]

Hi. What do you mean by "debug line?"

 

[dcavaiani]

My Bad, this is line it errors out on:

' *** CODE ABORTS AT THE NEXT LINE ***
Set rstquery = db.OpenRecordset("weeklytotalMatl")

 

[jdraw]

If you remove the ' from this line

'        MsgBox "Error No: " & Err.Number & "; Description: " & Err.Description

do you get an error number and description??
Can you step through the code? And??

 

[dcavaiani]

err 3061 - too few parms , expected 1

 

[isladogs]

What is the sql for query WeeklyCounyMatl?

BTW I see no reason to open then close those tables and queries in order to run the sql statements in between

 

[dcavaiani]

SELECT materials.customer, DatePart("yyyy",[purchasedate],7) &  DatePart("ww",[purchaseDate],7) AS YearWeekNumber, Sum(materials.extended) AS SumOfMatl
FROM materials
WHERE materials.customer=[Forms]![SelectCustBobsWeekly].[simcust]
GROUP BY materials.customer, DatePart("yyyy",[purchaseDate],7), DatePart("ww",[purchaseDate],7);

 

[isladogs]

Add delimiters to the WHERE clause
Assuming its a text field then

SELECT materials.customer, DatePart("yyyy",[purchasedate],7) &  DatePart("ww",[purchaseDate],7) AS YearWeekNumber, Sum(materials.extended) AS SumOfMatl
FROM materials
WHERE materials.customer= '" & [Forms]![SelectCustBobsWeekly].[simcust] & "'
GROUP BY materials.customer, DatePart("yyyy",[purchaseDate],7), DatePart("ww",[purchaseDate],7);

 

[dcavaiani]

This clears the Table, inserts records for all of a customer's job where there is Labor. Then, inserts records into the table where a year/week# had NO labor, and then the final step will be to UPDATE the records in the Table WITH THE MATERIAL AMOUNT - WHERE there is ALREADY a record for the YEAR/WEEK# WITH A LABOR AMOUNT - in the TABLE.

 

[dcavaiani]

Add delimiters to the WHERE clause
Assuming its a text field then

SELECT materials.customer, DatePart("yyyy",[purchasedate],7) &  DatePart("ww",[purchaseDate],7) AS YearWeekNumber, Sum(materials.extended) AS SumOfMatl
FROM materials
WHERE materials.customer= '" & [Forms]![SelectCustBobsWeekly].[simcust] & "'
GROUP BY materials.customer, DatePart("yyyy",[purchaseDate],7), DatePart("ww",[purchaseDate],7);

The query was working fine .... how cud that cause an error when referring again to its name ?

 

[isladogs]

Try that sql instead of the query name in the recordset

You should also set both recordsets to Nothing at the end

 

[dcavaiani]

Try that sql instead of the query name in the recordset

You should also set both recordsets to Nothing at the end

The pasted in sql shows error at the "yyyy" ?

 

[dcavaiani]

Let's have a New Years cocktail and check this out at a later time ;-)

 

[theDBguy]

err 3061 - too few parms , expected 1

Hi. Sorry for the delay. I hope this is not too late now.


So, instead of using this:

Set rstquery = db.OpenRecordset("weeklytotalMatl")

Try using this:

Set rstquery = fDAOGenericRst("weeklytotalMatl")

You can find that generic recordset function here.


Hope it helps...

 

[dcavaiani]

Hi. Sorry for the delay. I hope this is not too late now.


So, instead of using this:

Set rstquery = db.OpenRecordset("weeklytotalMatl")

Try using this:

Set rstquery = fDAOGenericRst("weeklytotalMatl")

You can find that generic recordset function here.


Hope it helps...

Set rstquery = fDAOGenericRst("weeklytotalMatl")[code]

Wow - looks like the above actually BYPASSES the ERROR  !!   All who responded to me here are pretty much AMAZING.   I will continue on tomorrow, if NOT hungover, to finish up my "update" coding VBA.  Thank You!

 

[theDBguy]

Set rstquery = fDAOGenericRst("weeklytotalMatl")

Wow - looks like the above actually BYPASSES the ERROR !! All who responded to me here are pretty much AMAZING. I will continue on tomorrow, if NOT hungover, to finish up my "update" coding VBA. Thank You!

Hi. That's correct! Leigh's generic recordset code/function takes care of the parameters to avoid the error. Happy New Year! Cheers!