[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2004-11-28
49
Medium Priority
?
406 Views
Last Modified: 2008-01-09
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
0
Comment
Question by:Dale Massicotte
  • 23
  • 8
  • 7
  • +3
49 Comments
 
LVL 11

Expert Comment

by:Quetzal
ID: 12692110
if you replace the docmd with the following, do you get the same error?
DoCmd.RunCommand acCmdSaveRecord
0
 

Author Comment

by:Dale Massicotte
ID: 12692154
Yes -

The error message is identical
0
 
LVL 11

Expert Comment

by:Quetzal
ID: 12692192
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

Author Comment

by:Dale Massicotte
ID: 12692223
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
 
LVL 11

Expert Comment

by:Quetzal
ID: 12692366
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
 
LVL 58

Expert Comment

by:harfang
ID: 12692866
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
 
LVL 11

Expert Comment

by:Quetzal
ID: 12693050
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
 
LVL 58

Expert Comment

by:harfang
ID: 12694113
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
 

Author Comment

by:Dale Massicotte
ID: 12697083
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
 
LVL 36

Expert Comment

by:SidFishes
ID: 12697285
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
 
LVL 58

Expert Comment

by:harfang
ID: 12697945
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
 
LVL 11

Expert Comment

by:Quetzal
ID: 12698148
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
 

Author Comment

by:Dale Massicotte
ID: 12698153
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
 

Author Comment

by:Dale Massicotte
ID: 12698241
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
 
LVL 36

Expert Comment

by:SidFishes
ID: 12698880
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
 
LVL 11

Expert Comment

by:Quetzal
ID: 12700982
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
 

Author Comment

by:Dale Massicotte
ID: 12701531
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
 

Author Comment

by:Dale Massicotte
ID: 12701537
It is the cnn.open line that gives me the error
0
 
LVL 36

Expert Comment

by:SidFishes
ID: 12702484
what is your connection string (obscure the pwd)
0
 

Author Comment

by:Dale Massicotte
ID: 12707946
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
 
LVL 11

Expert Comment

by:Quetzal
ID: 12708434
You can typically use CurrentProject.Connection
0
 

Author Comment

by:Dale Massicotte
ID: 12708629
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
 

Author Comment

by:Dale Massicotte
ID: 12708691
More specifically

I get the write conflict when I go and close the form and it saves the record
0
 
LVL 36

Expert Comment

by:SidFishes
ID: 12708762
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
 

Author Comment

by:Dale Massicotte
ID: 12709236
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
 

Author Comment

by:Dale Massicotte
ID: 12711873
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
 
LVL 54

Expert Comment

by:nico5038
ID: 12797273
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
 
LVL 38

Expert Comment

by:puppydogbuddy
ID: 12799717
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
 

Author Comment

by:Dale Massicotte
ID: 12800859




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
 
LVL 54

Expert Comment

by:nico5038
ID: 12800964
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
 

Author Comment

by:Dale Massicotte
ID: 12801315
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
 

Author Comment

by:Dale Massicotte
ID: 12801320
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
 
LVL 54

Expert Comment

by:nico5038
ID: 12801387
What are the formsettings of the continuous form, are all (except Allow Filter) switched off ?

Nic;o)
0
 

Author Comment

by:Dale Massicotte
ID: 12801410
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
 
LVL 36

Expert Comment

by:SidFishes
ID: 12801428
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
 

Author Comment

by:Dale Massicotte
ID: 12801448
Awesome job FishMyster!   Okay now what?   Any solution?
0
 
LVL 54

Expert Comment

by:nico5038
ID: 12801508
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
 
LVL 36

Expert Comment

by:SidFishes
ID: 12801519
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
 

Author Comment

by:Dale Massicotte
ID: 12801548
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
 
LVL 54

Expert Comment

by:nico5038
ID: 12801563
You can use:
currentdb.execute ("UPDATE tblX SET UserID='" & currentuser & "'")

Nic;o)
0
 
LVL 54

Expert Comment

by:nico5038
ID: 12801566
Oops, you'll also have to add a WHERE, but I guess you guessed that too :-)

Nic;o)
0
 

Author Comment

by:Dale Massicotte
ID: 12801595
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
 
LVL 54

Assisted Solution

by:nico5038
nico5038 earned 600 total points
ID: 12801652
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
 
LVL 36

Accepted Solution

by:
SidFishes earned 1400 total points
ID: 12801692
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
 

Author Comment

by:Dale Massicotte
ID: 12801843
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
 

Author Comment

by:Dale Massicotte
ID: 12801854
Hold that thought....I just solved my problem by freaking accident....more to come
0
 

Author Comment

by:Dale Massicotte
ID: 12801920
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
 
LVL 54

Expert Comment

by:nico5038
ID: 12801958
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
 

Author Comment

by:Dale Massicotte
ID: 12801974
Sure, again thanks much!
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question