ADP only please (Access Project) Error 2465 "Application-defined or Object-defined Error"

For the life of me I cannot figure it out

In my form the code in the OnCurrent Event creates Error 2465 "Application-defined or Object-defined Error"

Me!UserID = CurrentUser
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70            <<<<< Bad Line Of Code




I need the form to save immediately upon opening so that the CurrentUser is saved in the record and others can see that the record is being edited.

What is befuddling is that I have a command button on the form with the same code.  When pressed, the record is saved and the error is not generated.

What gives?  Maybe there is another way to save the record through code that won't produce the error.

Thanks,
Daler
Dale MassicottePresidentAsked:
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.

QuetzalCommented:
if you replace the docmd with the following, do you get the same error?
DoCmd.RunCommand acCmdSaveRecord
0
Dale MassicottePresidentAuthor Commented:
Yes -

The error message is identical
0
QuetzalCommented:
How many times does the On Current event proc get executed when the form is opened?  (Can set breakpoint and count, disable the offending code for this test)
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Dale MassicottePresidentAuthor Commented:
I set the breakpoint at the end and removed the "offending code".  I assume it executes only once, how can I tell or count?
0
QuetzalCommented:
Set the breakpoint and save the form.  Open the form.   As you hit the breakpoint, simply do a continue.  Count how many times you hit the bp until the form is fully open.
0
harfangCommented:
I believe this is bad practice anywas. I would never have anticipated the automatic save on current... :)
If you need a user trace, do that in a different table:
tblUsersLockContracts (for the table tblContracts)
UserID - one-to-one link to tblUsers
ContractID - number of current contract, one-to-one link on tblContracts

Private Sub Form_Current()

    Dim varUserLock as Variant

    With CurrentDb
        ' unlock any previous contract (bug recoup...)
        .Execute "DELETE * FROM tblUsersLockContracts" _
            & " WHERE UserID = GetCurrentUserID()"
        ' lock current
        .Execute "INSERT INTO tblUsersLockContracts" _
            & " VALUES( GetCurrentUserID(), " & Me.ContractID & ")"

        If .RecordsAffected Then
            ' user has access to the record...
        Else
            ' someone else?
            varUserLock = DLookup("UserID", "tblUsersLockContracts", "ContractID = " & Me.ContractID)
            If IsNull(varUserLock) Then
                MsgBox "couldn't lock record... (bug?)"
            Else
                MsgBox "User number " _
                    & lngUserLock _
                    & " has locked the record..."
        End If
    End With

End Sub

Private Sub Form_Close()
    ' release contracts...
    CurrentDb.Execute "DELETE * FROM tblUsersLockContracts" _
        & " WHERE UserID = GetCurrentUserID()"
End Sub

In this code, I assume that UserID is a number, of course. If you are actuall using the built-in CurrentUser(), it should work as well (replace GetCurrentUserID with CurrentUser).

Bottom-line: do not use your main table for record locking, do not save or edit records in the On_Current event...

cheers :)
0
QuetzalCommented:
IMHO, you're for a whole lot more coding if you pursue a separate lock table (because you'll now need to use this table as a queue to use it correctly).

However, harfang raises a good point about what you have set out to do.  I personally don't like to update records unless the data have actually changed.  There are other ways to handle this situation that could be better, but I don't see what you want to do as "wrong".

But the question you asked about was why the offending line is problematic.  
0
harfangCommented:
Yes, Quetzal, this was not the original question. I did test the bit of code in DAO and ADODB and it works. I can't test ADP here, hence this suggestion of a workaround.

There must be something undesirable with the default approaches (lock record or handle write conflict with optimistic locking), or Daler would not set things up that way.

Cheers:)
0
Dale MassicottePresidentAuthor Commented:
Yes, Indeed---ACCESS PROJECTS revert to OPTIMISTIC LOCKING and I am attempting to create a workaround until I can explore SQL-SERVER locking.  I just need to get my ACCESS project up and running ASAP, but at the same time create a little VBA "workaround".
0
SidFishesCommented:
why not just run a stored procedure when the form loads...

 Dim cnn As ADODB.Connection
        Set cnn = New ADODB.Connection
       
        strSQL = "EXEC dbo.stpUpdateUser " & me!UserId & ", '" & me!SomeOthercriteria & "'"
        cnn.ConnectionString = pubstrConnect 'your connection string
        cnn.Open
        cnn.Execute strSQL
        cnn.Close

0
harfangCommented:
Are you saying that the form's "Record Lock" setting is ignored in ADP? This can very well be the case, of course, it would just be one more compatibility issue between Micro and Soft... :)
0
QuetzalCommented:
There is a very good reason why optimistic locking is the default.  Locking records for long periods of time is generally not a good idea.  Do you really have an issue will concurrent editing?
0
Dale MassicottePresidentAuthor Commented:
Harfang that is correct

SidFishes:

Can you help me with that stored procedure.

I have created a few sp's used as recordsources for some of my forms, but I have never created one as you are describing
0
Dale MassicottePresidentAuthor Commented:
Quetzal  Ocassionally I have an issue with concurrent editing- although rare.  

I am just trying to create a way to alert users when someone else is editing a record
0
SidFishesCommented:
it's pretty simple...i'll guess at some structure...you can modify as required...

the vb code is passing 2 variables to the sp...pulled from form fields but you can modify to use currentuser (or better... fOSUsername API from the Access Web)

"EXEC dbo.stpUpdateUser " & me!UserId & ", '" & me!OrderID & "'"

Create Procedure dbo.stpUpdateUser
@userID INT,
@OrderID varchar(14) /*note this could be an Integer(or any other datatype) but did it this way to show possiblities
AS
UPDATE tblOrders
Set UserID = @UserID
Where
OrderID = @orderID
go

0
QuetzalCommented:
The reason I asked is because your answer is the one I expected.  Under these circumstances, I think that optimistic updating is the best approach.  One simple approach is simply to record the date/time and user who made the last change and make the data visible on the record (or available in some other way).

Here is what MS has to say on the subject:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconOptimisticConcurrency.asp
0
Dale MassicottePresidentAuthor Commented:
I am working with SidFishes because he is helping me create a workaround to the actual question.

Sid I got the Stored Procedure to work perfectly, but NOT in the code executing it

The executing of the code gives me an error:

Code:

Dim cnn As ADODB.Connection
       Set cnn = New ADODB.Connection

       strSQL = "EXEC p_UpdateUser " & Me!UserID & ", '" & Me!WaybillNumber & "'"
       cnn.ConnectionString = pubstrConnect 'your connection string
       cnn.Open
       cnn.Execute strSQL
       cnn.Close

Help we are almost there

0
Dale MassicottePresidentAuthor Commented:
It is the cnn.open line that gives me the error
0
SidFishesCommented:
what is your connection string (obscure the pwd)
0
Dale MassicottePresidentAuthor Commented:
SidFishes:  At the risk of sounding completely stupid...

What is a connection string and where can I find it pertaining to my Access Project?

I don't think I am using one or one is not required.
0
QuetzalCommented:
You can typically use CurrentProject.Connection
0
Dale MassicottePresidentAuthor Commented:
Thanks Quetzal -  That did the trick.

But now, I get a WRITE CONFILICT (Save Record,Copy To Clipboard, Drop Changes)

The fun it continues
0
Dale MassicottePresidentAuthor Commented:
More specifically

I get the write conflict when I go and close the form and it saves the record
0
SidFishesCommented:
a connection string would be something like

"driver={SQL Server};server=myServer\SQLServerName;TrustedConnection=yes;DATABASE=myDATA;"
or
"driver={SQL Server};server=myServer\SQLServerName;user=sa;pwd=blah;DATABASE=myDATA;"

i use a public const

Public Const pubstrConnect = "driver={SQL Server};server=myServer\SQLServerName;TrustedConnection=yes;DATABASE=myDATA;"

the problem with the write conflict will occur using bound forms...you've changed the data with the sp but not refreshed the form...try to add a

me.requery immediately after firing the sp...
0
Dale MassicottePresidentAuthor Commented:
ME.REQUIRY causes the same error as my original problem

DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
and
me.requery

Cause application error 2465
And the Write Conflict Continues Afterwards
0
Dale MassicottePresidentAuthor Commented:
Going back to my original problem I noticed one thing:

CODE:

Me!UserID = CurrentUser
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

If I remark out Me!UserID = CurrentUser, the code runs fine.

The problem is if the form goes into 'edit' mode then I get error 2465.

Nobody understands the reason for this?
0
nico5038Commented:
Hmm, some thoughts:
1) What's the definition for Me!UserID, can it hold text characters?
2) Is the UserID field linked to another tabel?

Nic;o)
0
puppydogbuddyCommented:
I've gotten that message in the past when I tried execute a save record on one form while I had another form opened that used the same recordset.  I can't remember, but I believe I solved the problem, either by doing a Me.Refresh on the active form before the save Or by temporarily closing the other form before doing the save and reopening after executing the save.
0
Dale MassicottePresidentAuthor Commented:




I tried both puppydogbuddy's suggestions with no avail to each

Regarding the code:

Me!UserID = CurrentUser
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

Causes the error

In fact, Nic;o)

if ANY field is modified through code before this line executes:
     DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
i get the same error.

0
nico5038Commented:
Hmm, like posted by  Quetzal I prefer the "DoCmd.RunCommand acCmdSaveRecord" as it's more stable, but you already tried that.
I'm getting the impression that the current record is locked for update when executing this event.
Some uggly work around might be to use:

DoCmd.RunCommand acCmdSaveRecord
Me!UserID = CurrentUser
DoCmd.RunCommand acCmdSaveRecord

On the other hand I do wonder why to record this as it's only interesting to know who did the last update.
Recording this won't notify others that have the record retrieved earlier and I only use a userID and DateTime stamp to record who did the last update.
When retrieving the record this can be stored and before the save you can re-read the record to test or the userID and Timestamp didn't change in the mean time to issue a warning.

Disadvantage of "locking" a row on forehand is the fact that you don't know or the application dumps and thus leaving the lock....

Nic;o)
0
Dale MassicottePresidentAuthor Commented:
Okay maybe I can do this:

First of all understand that what is happening is that I am firstly in a continous form called DISPATCH.  I then double-click on a particular record so that I can open form WAYBILL and edit that record.  On form WAYBILL's 'on current' or 'on open' event I am trying to save a value to a field in the underlying table so that when someone else on another workstation attempts to double-click on the same record, code will execute informing the user that someone else has already opened form WAYBILL and is editing that particular record.

I am sure that there is a better way.  But just for now I am converting my MDB to ADP and I want things to run as closely to where they were before.  Later I can fix them, but for now this is how it needs to be done as I am trying to save some time here.

Perhaps there is a way to write the value to the particular records field on when double-clicking the record in dispatch just prior to opening the WAYBILL form.

Your thoughts?
0
Dale MassicottePresidentAuthor Commented:
Come on guys...we can do this...please!

This is driving my to the point of visiting a hospital to get my head examined.

Thanks
0
nico5038Commented:
What are the formsettings of the continuous form, are all (except Allow Filter) switched off ?

Nic;o)
0
Dale MassicottePresidentAuthor Commented:
Allow Edits is on also-

But it has to be because on the continous form "On Deactivate" I have the code"

DoCmd.RunCommand acCmdSaveRecord

If I shut off Allow Edits on continous form 'DISPATCH' that action cannot be taken.

FYI I commented out that code and still it had no effect on opening the WAYBILL form, so I assume that no conflict is being created there
0
SidFishesCommented:
ok ...i at least i think the reason my solution didn't work...was due to the continuous form (which you didn't mention..tsk tsk ;)

my guess...your subform is bound to data which exists on your parent form, and you update the parent form...the data held on  the subform isn't getting updated so when you close the form...write conflict...

0
Dale MassicottePresidentAuthor Commented:
Awesome job FishMyster!   Okay now what?   Any solution?
0
nico5038Commented:
Continuous forms are a nightmare. When possible I would make it read only and have all updates concentrated on the other form.
Also using a modal popup form can cause trouble as then e.g. the OnActivate of the previous form won't fire.
I'm always trying to keep forms as "separate" as possible and the other solution would be to switch to using unbound forms, but I guess you won't be happy to implement that :-)

Nic;o)
0
SidFishesCommented:
it's about the order of things happen

Working with subforms
When you open a form containing a subform, the subform and its records are loaded before the main form. Thus, the events for the subform and its controls (such as Open, Current, Enter, and GotFocus) occur before the events for the form. The Activate event doesn't occur for subforms, however, so opening a main form triggers an Activate event only for the main form.

Similarly, when you close a form containing a subform, the subform and its records are unloaded after the form. The Deactivate event doesn't occur for subforms, so closing a main form triggers a Deactivate event only for the main form. The events for the controls, form, and subform occur in the following order:

Events for the subform's controls (such as Exit and LostFocus)

Events for the form's controls (including the subform control)

Events for the form (such as Deactivate and Close)

Events for the subform

from
http://www.experts-exchange.com/Databases/MS_Access/Q_21031308.html

so you might try to add the code i gave you earlier to the subform OPen event...first thing...then everything else should load...with the newest data...(maybe)

(btw...i'm with nico...unbound forms are so much easier to deal with..)

0
Dale MassicottePresidentAuthor Commented:
SidFishes - There is no subform associated with the WAYBILL form or the DISPATCH (continuous form)

I was planning on working on an unbound form and update my data that way.  But I want to get my ADP up and running first.

Did either of you agree with my suggestion

>>>Perhaps there is a way to write the value to the particular record field on when double-clicking the record in dispatch just prior to opening the WAYBILL form.<<<

Isn't there a simple SQL statement I can write in my VBA code that will write to the record just prior to opening the WAYBILL form?????

That way the value goes into the Waybill form and I wont have to immediately save it.  That would elimanate that error message upon opening the WAYBILL form.
0
nico5038Commented:
You can use:
currentdb.execute ("UPDATE tblX SET UserID='" & currentuser & "'")

Nic;o)
0
nico5038Commented:
Oops, you'll also have to add a WHERE, but I guess you guessed that too :-)

Nic;o)
0
Dale MassicottePresidentAuthor Commented:
Thank you for the WHERE...here is the code on the DISPATCH form on double click:

Dim BA As Integer
Dim stOpenArgs As String
       
            Beep
            BA = MsgBox("Another user is already editing this Job.  Continue anyway?", vbExclamation + vbYesNo, "WARNING!")
            If (BA = 7) Then
            ' The user pressed No
            Else
            CurrentDb.Execute ("UPDATE Jobs SET UserID='" & CurrentUser & "'")           '  needs where clause
            stOpenArgs = "Forms!DispatchBrowser2000!WaybillNumber"
            DoCmd.OpenForm "WAYBILL", , , , , , stOpenArgs
0
nico5038Commented:
Try:
CurrentDb.Execute ("UPDATE Jobs SET UserID='" & CurrentUser & "' WHERE [WaybillNumber]=" & Forms!DispatchBrowser2000!WaybillNumber)

assuming that the number is numeric, else add surrounding single quotes.
BTW don't forget to reset the UserID after the update to Null and think about how to remove "lost locks" :-)

Nic;o)
0
SidFishesCommented:
ok...know i'm confused...

"Isn't there a simple SQL statement I can write in my VBA code that will write to the record just prior to opening the WAYBILL form?????"

is what the sp in my first answer waaaay up there ^^^ was to do..

.
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
Dale MassicottePresidentAuthor Commented:
Okay I give up.  I am assuming that if I take the time to learn unbound forms, than record-locking becomes a non-issue instantly.  Doctors, do you both concur?
0
Dale MassicottePresidentAuthor Commented:
Hold that thought....I just solved my problem by freaking accident....more to come
0
Dale MassicottePresidentAuthor Commented:
For all those concerned I am posting the code:

In the Continuous form's on-click event--

Dim cnn As ADODB.Connection
Dim strSql As String
       Set cnn = New ADODB.Connection
       strSql = "EXEC p_UpdateUser " & CurrentUser & ", '" & Me!WaybillNumber & "'"
       cnn.ConnectionString = CurrentProject.Connection
       cnn.Open
       cnn.Execute strSql
       cnn.Close
Dim BA As Integer
Dim stOpenArgs As String
        stOpenArgs = "Forms!DispatchBrowser2000!WaybillNumber"
        DoCmd.OpenForm "WAYBILL", , , , , , stOpenArgs

Then the WAYBILL form opens with no need to do a 'DoCmd.RunCommand acCmdSaveRecord'

Thanks,  experts!

Points split to SidFishes and Nic;o)
0
nico5038Commented:
Glad we could help :-)

Nic;o)
BTW can you ask CS to delete the other Q: http://www.experts-exchange.com/Databases/MS_Access/Q_21238550.html

0
Dale MassicottePresidentAuthor Commented:
Sure, again thanks much!
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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.