problems with update query

[JumpinJimRivers]

Howdy,

I have this update query which I thought was working fine, but now it's being fickle. It is supposed to sum all amounts of a WorkItemID in a datasheet after a new amount has been entered, and then put that sum in another datasheet subform field (in the same main form) which has the same matching WorkItemID already. But sometimes it either does not update or, if the new amount I enter is the last record in the dataheet, it will give me the error "Syntax error (missing operator) in query expression '[workitemid]='" , which makes me think that it's trying to read a WorkItemID in the next new record where one does not exist.

I think the problem with my query (at least one of them) is it's using the WorkItemID of the current record where the new amount is entered to find and update the record with the matching ID in the other table datasheet. There must be a better way to update matching records instead of just using the current record ID; I just don't know what it is.


So here's my update query. I'd really appreciate you taking a look if you think you can help.

Set db = CurrentDb
strSQL = "UPDATE SubBillingWorkItems SET SubBillingWorkItems.total = " & DSum("amount", "[sub billing items monthly]", "[WorkItemID]=" & Me!WorkItemID)
strSQL = strSQL & " WHERE SubBillingWorkItems.WorkItemID =" & Me!WorkItemID
db.Execute strSQL
Me.Parent![billing work items subform].Requery

 

[LittleSmudge]

If you have just created a new record and then added the amount then the 'New' record won't have been updated in the table at the time you run the UpdateQuery so the Id will not be valid.

You need to requery the form to make it update the data in the underlying table before you run the query.


G LS

 

[JumpinJimRivers]

wonderful, works like a charm now. thank you!