need help with code updating a field

[JumpinJimRivers]

I know something's wrong with the code below, but I don't know what. I am wanting to update the subform field "Total" based on WorkItemID criteria from another subform after a new amount has been entered. Right now it's giving me the error message "Too few parameters. Expected 1" whatever that means.


Dim MyDB As Database
Dim Rec As Recordset
Dim SQLString As String

SQLString = "SELECT [Sub Billing Items Monthly].WorkItemID, Sum([Sub Billing Items Monthly].Amount) AS SumOfAmount from [Sub Billing Items Monthly]GROUP BY [Sub Billing Items Monthly].WorkItemID HAVING ((([Sub Billing Items Monthly].WorkItemID)=[me]![workitemid]))"
Set MyDB = CurrentDb()
Set Rec = MyDB.OpenRecordset(SQLString)
Me.Parent![billing work items subform]![Total] = Rec!SumOfAmount

make sense????

 

[RobertT687]

Two things. 1) I usually get this error when I misspell a table or column name when creating an sql statement in code. Make sure column and table names are spelled correctly. 2) I don't see a space before the 'GROUP BY' clause.

 

[JumpinJimRivers]

Well, there's a space in the code, don't know why it didn't show up here. The compiler is putting the error on
Set Rec = MyDB.OpenRecordset(SQLString).
So maybe the SQLString is fine, but still don't know why the above is wrong.

 

[jebry]

Hi!

Try this:

Dim MyDB As Database
Dim Rec As Recordset
Dim SQLString As String

SQLString = "SELECT [Sub Billing Items Monthly].WorkItemID, Sum([Sub Billing Items Monthly].Amount) AS SumOfAmount from [Sub Billing Items Monthly]GROUP BY [Sub Billing Items Monthly].WorkItemID HAVING ((([Sub Billing Items Monthly].WorkItemID)= " & [me]![workitemid] & &quot)"

Set MyDB = CurrentDb()
Set Rec = MyDB.OpenRecordset(SQLString)

Me.Parent![billing work items subform]![Total] = Rec!SumOfAmount

Set Rec = Nothing
Set MyDB = Nothing

hth
Jeff Bridgham
bridgham@purdue.edu

 

[JumpinJimRivers]

I tried it and now it says "can't find the field 'forms' referred to your expression" for this:

SQLString = "SELECT [Sub Billing Items Monthly].WorkItemID, Sum([Sub Billing Items Monthly].Amount) AS SumOfAmount from [Sub Billing Items Monthly]GROUP BY [Sub Billing Items Monthly].WorkItemID HAVING ((([Sub Billing Items Monthly].WorkItemID)= " & [Me]![WorkItemID] & &quot)"


which doens't even refer to "forms"