move next???

[boblarson]

but isn't ado the new one and dao the one (supposedly) being phased out?

Old rumours - that changed a long time ago. Both are available, but DAO is native to the internal workings of Access.

 

[CHAOSinACT]

ok, first, bob - thank you. great effort, much appreciated. seems to work without hanging. seems to come back with wrong result too, but i'll look into that...

 

[boblarson]

ok, first, bob - thank you. great effort, much appreciated. seems to work without hanging. seems to come back with wrong result too, but i'll look into that...

Well, the other recordset is just staying on the first item, so you'll have to reconcile that one. I wasn't sure what you are trying to do from it but you'll need to move it too if you are wanting to work your way through it.

 

[CHAOSinACT]

is it possible the filter statement there needs adjusting???

the first filter result (checked) is [Civil Job Number] = 001* which is right and access accepts. i run that in main query manually i get no records returned which is correct. when i run this in vba it gives me the first record in the table if unfiltered....leads me to think i'm not filtering correctly?

 

[CHAOSinACT]

maybe some kind of update after the filter property set?

 

[Banana]

Access suggest it in Intellisense. seemed logical.

Ahh, unfortunately, no. Just because you see it in the Intellisense does not actually mean it is available. It can only lists all possible options but it's still the programmer's responsibility to validate that the options specified actually makes sense.

In which case, you want your help files to be your bestest buddy, not the Intellisense.

 

[CHAOSinACT]

ok everything seems ok, the only prob is that

rsCivilActivitySlips.Filter = "JobNumber Like " & RJobNum(rsJobsList![Civil Job Number].Value) & "*"

seems not to work although it seems fine. access doesn't stop there a Debug.Print on rsCivilActivitySlips.Filter returns:

JobNumber Like 001*

which is exactly what i want. that in the query manually def returns the right result... but when i get to

TempCacheAmount = TempCacheAmount + rsCivilActivitySlips!TotalSpent.Value

it acts like there is no filter (first result returned is what is in the query if not filtered at all)....

 

[CHAOSinACT]

ok did some reading, i need to re-load the filtered record into another recordset:

rsCivilActivitySlipsUnfiltered.Filter = "JobNumber Like 001*"
Set rsCivilActivitySlipsFiltered = rsCivilActivitySlipsUnfiltered.OpenRecordset

but i get told i have a missing operator on this.... i wouldn't think so but never tried to filter this way before...

 

[LPurvis]

Well if Banana's jumped on this one then I'm allowed to too.
(I try to abstain for fear of monotony ;-)

First things that spring to mind - the cursor choices should be largely redundant.
They all allow you to advance through the recordset and update it (assuming the data and lock type permits it).

Not focussing on the recent changes, but the original issues, you said you've confirmed that the recordset has records.
Is that based upon a check on EOF - or that you've run the query used in the recordset separately?
The classic mistake here is assuming that the query opened in the UI matches that of the recordset. Very often in such examples the OP is including wildcard criteria
WHERE FieldName Like [EnterName] & "*"
Works fine in DAO and the Access UI - but with the OLEDB provider accepting % as a wildcard it returns zero in the recordset in code.
So confirm in code that your recordset has the records you'd expect.

Your latest attempt at filtering
(rsCivilActivitySlipsUnfiltered.Filter = "JobNumber Like 001*")
would seem to confirm this suspiscion. Even though that is now in DAO and so valid.

I agree with Banana about the updating.
(At least I think I do - we'd had such a discussion before :-s)

There is no need to call an Edit method in ADO. Indeed - as you've found, there is no such method. Changes to the values imply editing directly.

While I wholeheartedly agree that DAO is intrinsically Jet oriented and appropriate for use in Access I wouldn't personally advocate leaving ADO behind if that's what you're using - just because a procedure is putting up a bit of a fight. That just means you get to have fun for a while. (Enjoy the times when developing fights you more frequently! They grow ever fewer. :-s)
They each have their benefits. (And yes, I've known ADO outperform DAO at times even against Jet data - but that's not the norm).

So - on to the last issue...
rsCivilActivitySlipsUnfiltered.Filter = "JobNumber Like '001*'"
or
rsCivilActivitySlipsUnfiltered.Filter = "JobNumber Like ""001*"""
if you particularly love double quotes, I know Bob likes 'em ;-)

Cheers.

 

[CHAOSinACT]

ok - thanks so much for that (i'm in australia hence why i took so long to respond, just got into work). i found the last response quite helpful, and putting this into code got EVERYTHING working.

so thanks... god i love learning on the fly like this.... :/
but i can't thank you all enough (i'm a Lisp programmer if anything traditionally)

i actually had 1 more filter problem, but as so often happens debugging to talk to you guys made it clear so i guess your teaching me stuff for sure.

i greatly look forward to the day i catch up enough to help others

 

[boblarson]

Glad to hear things are working out for you.

 

[CHAOSinACT]

dead set for all that frustration, success felt so good i need a cigarette LMAO!