need help with this bit of code please

[JumpinJimRivers]

Can someone please explain to me why I am getting the error "expected sub, function or property" for the following:

Set rst = [sub billing work items].OpenRecordset("sub billing work items", dbopendynaset)
rst [sub billing work items].Index = WorkItemID
rst [sub billing work items].Seek = Me!WorkItemID
rst [sub billing work items].Edit
rst [sub billing work items].paid = DSum("[amount]", "[sub billing items monthly]", "[workitemid]=" & Me![WorkItemID])
rst [sub billing work items].Update
Me.Parent![billing work items subform].Requery

all i want to do is open a table and update a field based on WorkItemID

 

[JumpinJimRivers]

I've tried this now and it gives me "invalid qualifier."


Dim dbs As Database
Dim rec As Recordset
Dim strTable As String

strTable = "sub billing work items"

Set dbs = CurrentDb
Set rec = dbs.OpenRecordset(strTable, dbopendynaset)
rec strTable.Index = WorkItemID
rec strTable.Seek = Me!WorkItemID
rec strTable.Edit
rec strTable.paid = DSum("[amount]", "[sub billing items monthly]", "[workitemid]=" & Me![WorkItemID])
rec strTable.Update
Me.Parent![billing work items subform].Requery

 

[AccessMoron]

A good hint would be on what line the code is falling over!

are you using AC97 or AC2000?

 

[rewdee]

You need to call either Edit or AddNew before trying to assign anything. I think you are probably looking for something like this:

set rec=dbs.OpenRecordset(strTable & " WHERE WorkItemID=" & workItemID)

with rec
    .Edit
    .paid=$whatever
    .Update
end with

me.Requery

Hope this helps,
Rewdee

 

[rewdee]

Oops forgot, it should be:

set rec=dbs.OpenRecordset("SELECT * FROM " & strTable ...

 

[JumpinJimRivers]

Thanks much for the help. I tried the following


Dim rec As Recordset
Dim strTable As String

strTable = "Sub Billing Work Items"

Set rec = dbs.OpenRecordset(strTable & " WHERE WorkItemID=" & WorkItemID)
With rec
.Edit
.paid = DSum("[amount]", "[sub billing items monthly]", "[workitemid]=" & Me![WorkItemID])
.Update
End With

Me.Parent![billing work items subform].Requery

And it's saying "method or data member not found" on the ".Edit"


I'm using 2000 and DAO 3.6 is checked in the reference. So what's the deal??

 

[JumpinJimRivers]

same error with this:


Set rec = dbs.OpenRecordset("SELECT * FROM " & strTable & " WHERE WorkItemID=" & WorkItemID)

 

[rewdee]

You need to specify what library you are going to use (ADO or DAO) in Access 2000. Declarations should look like this:

Dim dbs as DAO.Database
dim rec as DAO.Recordset
...

 

[JumpinJimRivers]

sigh....

ok, first, thank you very much for your help. second, now i'm getting same error on ".paid" but not ".edit" anymore. Enlighten me, kimosabi



Dim dbs As DAO.Database
Dim rec As DAO.Recordset
Dim strTable As String
strTable = "Sub Billing Work Items"


Set rec = dbs.OpenRecordset("SELECT * FROM " & strTable & " WHERE WorkItemID=" & WorkItemID)
With rec
.Edit
.paid = DSum("[amount]", "[sub billing items monthly]", "[workitemid]=" & Me![WorkItemID])
.Update
End With

Me.Parent![billing work items subform].Requery

 

[rewdee]

Oops -- typo it should be:

!Paid=$whatever
[\code]

 

[JumpinJimRivers]

really long sigh....

Now it's saying "object variable not set or defined" for this:
Set rec = dbs.OpenRecordset("SELECT * FROM " & strTable & " WHERE WorkItemID=" & WorkItemID)

I changed the table to one word tthinking that might be the problem, but it's not.

This is what we're working with:


Dim dbs As DAO.Database
Dim rec As DAO.Recordset
Dim strTable As String
strTable = "SubBillingWorkItems"


Set rec = dbs.OpenRecordset("SELECT * FROM " & strTable & " WHERE WorkItemID=" & WorkItemID)
With rec
.Edit
!paid = DSum("[amount]", "[sub billing items monthly]", "[workitemid]=" & Me![WorkItemID])
.Update
End With

Me.Parent![billing work items subform].Requery

 

[JumpinJimRivers]

actually it says

object variable or with block variable not set

 

[rewdee]

This means that there is no real item to update. In other words, the value that WorkItemID is not not an item of the SubbillingWorkItems table. Another error could be that you must have a table named SubBillingWorkItems to refer to.

Hope this helps,
Rewdee

 

[JumpinJimRivers]

welp....all the names match up and a WorkItemID exists in both tables, so i think this is just God telling me to go home now.

...thank you very much for your help. If you think of anything else, please let me know.

 

[WildHare]

Well, another cook couldn't make this broth taste THAT much worse...

If this is what you have:

Set rec = dbs.OpenRecordset("SELECT * FROM " & strTable & " WHERE WorkItemID=" & WorkItemID)
With rec
.Edit
!paid = DSum("[amount]", "[sub billing items monthly]", "[workitemid]=" & Me![WorkItemID])
.Update
End With


Let me ask a question. Is this query going to return JUST ONE RECORD? It kinda looks that way, but we can never tell...

And are you updating just one field in this record? It kinda looks that way too, but we can never tell...

I think you may be able to get by with just this:

Set rec = dbs.OpenRecordset("SELECT PAID FROM [SUB BILLING WORK ITEMS] WHERE [SUB BILLING WORK ITEMS]![WorkItemID] =" & ME!WorkItemID)

With rec
[red] .moveFirst 'may not need but can't hurt[/color]
.Edit
!paid = DSum("[amount]", "[sub billing items monthly]", "[workitemid]=" & Me![WorkItemID])
.Update
End With

My only concern here is that you seem to be going through an awful lot of trouble to update one field in one record - I'm sure you should be able to do this with a precompiled update query at about .005% of the aggravation...

This also points out the problems with multiple-word-table-names with embedded spaces in them, like "Sub billing items monthly", and fields with the same names within a domain path. I wasn't sure WHICH "workitemid" you were referring to there in places - the table, the sub-table or the form. And I'm still not sure which WorkItemID the "ME![WORKITEMID]" object will point to, in your editing clause...


Remember, you're unique - - just like everyone else
Another free Access forum:

http://www.forumco.com/jmhare

http://www.jmhare.com

 

[JumpinJimRivers]

I don't know which Shakespeare play best represented my dilemma, "Much Ado About Nothing" or "A Comedy of Errors!" But the end proved diligence a faithful virtue... but not without the help of fellow soldiers...much thanks to each of you for your help.

Perhaps one of you will understand better than myself why I kept getting "object not defined, etc," but Jim gave me the brilliantly simple suggestion of doing an update query-something that I never even considered!!!!

So what finally worked was this:

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


--thought you deserved to know

thanks again