Access 2010 VBA Update Field in Second Recordset

Hi

I have two tables, which are both open.  My code needs to take some data from the first, find the correct first record (I'll deal with second, third, etc records later) and then, based on whether something is true or not, change a field in the second.

I've set the second recordset as a snapshot as that was the only way I could find that would let me use FindFirst and I really don't want to loop through every record as the table will get pretty big.  I think it may be this that is stopping me changing anything in the second recordset, but I'm not sure.

I've attached my code (rubbish though it probably is!) in the hope that someone might be able to point me in the right direction.

I'm awarding 500 points for a quick, working and easy to understand solution so that I can move on to the next bit.

Thanks
Sarah
ScuzzyJoAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jeffrey CoachmanMIS LiasonCommented:
Then, for me at least, you would need to attach a sample of your database...

Not sure what this all means with no context:
take some data from the first, find the correct first record (I'll deal with second, third, etc records later) and then, based on whether something is true or not, change a field in the second.
???


Sample database notes:
1. Back up your database(s).
2. If the database is split, combine the front and back ends into one database file.
3. Remove any startup options, unless they are relevant to the issue.
4. Remove any extraneous records unless they are relevant to the issue.
5. Delete any objects that do not relate directly to the issue.
6. Remove any references to any "linked" files (files outside of the database, Images, OLE Files, ...etc)
7. Remove any references to any third party Active-x Controls (unless they are relevant to the issue)
8. Remove, obfuscate, encrypt, or otherwise disguise, any sensitive data.
9. Unhide any hidden database objects
10. Compile the code. (From the VBA code window, click: Debug-->Compile)
11. Run the Compact/Repair utility.
12. Remove any Passwords, Security and/or login prompts.
13. If a form is involved in the issue, set the Modal and Popup properties to: No
    (Again, unless these properties are associated with the issue)
14. Post the explicit steps to replicate the issue.
15. Test the database before posting.

In other words, ...post a database that we can easily open and immediately see and/or troubleshoot the issue.
And if applicable, also include a clear, graphical representation of the *Exact* results you are expecting, based on the sample data.

JeffCoachman
0
ScuzzyJoAuthor Commented:
Hi Jeff

Thanks for replying.  It will take ages to get the data into a database that I can post as much of it is confidential, but I'll try to do that.

I had thought that my explanation would convey the issue OK as I had already searched in Google and found several answers, though none of them worked.  I had also attached my code, though I can't see it as an attachment here.

Thanks
Sarah
0
Jeffrey CoachmanMIS LiasonCommented:
As the list states, just put enough fake sample data in the tables so that the issue can be demonstrated...

<I had thought that my explanation would convey the issue OK>
...Not to me
;-)

"My code needs to take some data"
...define "Some"

"find the correct first record"
Define "Correct"

"change a field in the second."
Change a field to what exactly?

" based on whether something is true or not"
Define "Something"

So as you can see, I don't know how to create a targeted solution, without seeing what you have set up already,
Nor do I know the need for doing this, in case there might be a better way...
;-)

JeffCoachman
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

ScuzzyJoAuthor Commented:
Hi Jeff

Thanks for looking at this.  I've stumbled across a solution and have it working.  I just needed to set the second record set to a dynaset.  I'll allocate some of the points to you as you have tried to take a look at it.  Is that OK?

Onwards and upwards until I come across the next bit I don't know how to do!

Thanks
Sarah
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jeffrey CoachmanMIS LiasonCommented:
It is against the rules here to assign points for "trying", ...unless it leads to a solution.
My post was just seeking clarification.
If you have solved the issue yourself, you can post the solution, then accept your own post as the solution...
;-)

JeffCoachman
0
ScuzzyJoAuthor Commented:
OK, Jeff, sorry I can't give you any points then as you tried to help so I think you should get some.

Thanks
Sarah
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Hi ...

"I've set the second recordset as a snapshot as that was the only way I could find that would let me use FindFirst and I really don't want to loop through every record as the table will get pretty big"

A Snapshot Recordset is not updatable. Further, it's generally much slower that using a Dynaset Recordset (dbOpenDynaset).  I suggest in general you not use Snapshots ... another reason being they may not contain the latest data ... depending.

FindFirst definitely works with a Dynaset Recordset.

mx

ps ... "ScuzzyJo"
Funny.
0
ScuzzyJoAuthor Commented:
I found the solution myself.  I had used:

Set myVar = Current.db.OpenRecordset("TableName",dbOpenSnapshot)

instead of:

Set myVar = Current.db.OpenRecordset("TableName",dbOpenDynaset)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.