Editing a existing record

I use a datasheet to list projects.  Clicking on project ID opens form with selected record.  Allow Edits = Yes, Data Entry = No, Record Locks = Edited Record.  When I first saved edited record, changes were saved to first record in table, not correct record.  So added RS.FindFirst "ProjectID = " & me.txtProjectRecordNumber.  Then started getting Runtime error 3188 Could not update; currently locked by another session.  I thought the original open datasheet might be the culprit so I added code to close the datasheet after the form opens - same error.  Next I tried adding a line in the code to change the recordlocks to 0 (Forms("tblProjectControl").RecordLocks=0) which solved the issue with the locked form but this refreshes the form (calls CurrentForm) and causes lots of other problems.  How can I fix?
Dim DB As DAO.Database
    Dim rst As DAO.Recordset
    Set DB = CurrentDb
    Set rst = DB.OpenRecordset("tblProjectControl")
    With rst
        .FindFirst "ProjectID = " & Me.txtProjectNumber
        .Edit
        !RequestDT = Me.txtRequestDT
        !RequestHR = Me.txtRequestTime
        !Insert_User = Me.txtGetUser
        !ImpactnoAction = Me.txtImpactnoAction
        !Justification = Me.txtJustification
        ...
        .update
        .close
        Set rst=nothing
    End With

Open in new window

chobeAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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.

peter57rCommented:
Why are you opening the second form at all?
Since you are editing the matching record in code there appears to be no obvious reason to open the from.

If you really need to open the second form, then run the 'editing' code from your post first and then open the form.
JezWaltersCommented:
This sort of problem is lot easier to diagnose with a database to work on.  Is there any chance you could post a cutdown sample database that exhibits the behaviour you are describing?
chobeAuthor Commented:
To provide a sample database would be impractical because of the number of subforms number of tables, etc.  As to why I need two forms; Each record has some 70+ fields so editing in a datasheet is way to cumbersome.  The first form provides only a few identifying fields such as project ID, name, current project manager, etc., for some 900 records.  That's why I need the two forms - first to select the record and second to display and edit the record.  I have since discovered that if I remove the statement rst.findfirst "ProjectID = " * me.txtProjectNumber, that it not appears to work as expected - saving the changes to the same record as in the form but I'm concerned that things will break down and start saving changes the the first record again.  I appreciate any assist you can think of....
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

chobeAuthor Commented:
O.K. at least now I better understand the issue.  My code opens a NEW recordset in addtion to the forms recordset.  Thus the reason for the locked issues.  Question is how do I update the forms recordset?
peter57rCommented:
If you are going to leave the form open fore the user to do more input then you could write to the form controls instead.  Or as I said before, complete the code  updates to the table first and then open the form.
chobeAuthor Commented:
I finally figured out why I was having the problem.  By creating the new recordset (openRecordset) based on the table tblProjectControl and by opening form with same recordset I was locking the record preventing editing  I changed my code to use the form's recordset (me.recordset) instead.  All works well now.

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
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
Visual Basic Classic

From novice to tech pro — start learning today.