Solved

RECORD LOCK MESSAGE

Posted on 1998-06-25
43
540 Views
Last Modified: 2006-11-17
I am using an unbound form and using recordset addnew method. This record(s) added are totally new to the table and there is no possibility that other user may have locked this records. I stiil get the message "could not save, currently locked by other user"
What the heck could be wrong?
0
Comment
Question by:devtha
  • 16
  • 13
  • 12
  • +2
43 Comments
 
LVL 8

Expert Comment

by:wayneb
Comment Utility
Have you repaired and compacted this data base it sounds like it is damaged.
0
 
LVL 12

Expert Comment

by:Trygve
Comment Utility
devtha: Is this the form that worked from your other question ? Maybe you have set some really pessimistic locking that locks the whole table ?
0
 
LVL 6

Author Comment

by:devtha
Comment Utility
The db is not damaged...


TRYGVE this is not same form..
I shall look into pessimistic locking. Please shed more light on it.
thanks
Dave

0
 

Expert Comment

by:rsellis
Comment Utility
Access locks records in pages.  If you have multiple users adding or editing records, you may need to change the key to the table so that the records are better disbursed.
0
 
LVL 12

Expert Comment

by:Trygve
Comment Utility
From the developer CD;


       Using Recordset Locking

       You use exclusive mode to lock an entire database. You use recordset locking, on the other hand, to control how one or
       more individual tables in a database are locked. Recordset locking applies only to table- and dynaset-type Recordset
       objects; it doesn't apply to snapshot- and forward-only-type Recordset objects because these are inherently read-only
       objects.

       Note   The default recordset lock is a write lock, which means that other users can't edit data in the locked records. You
       can also prevent users from reading the data by setting a read lock. You may want to do this if you are making bulk
       changes to a Recordset object and you want to prevent users from reading the data until you have completed your
       update. To set a read lock, specify the dbDenyRead constant in the options argument of the OpenRecordset method.
       For more information, search the Help index for "OpenRecordset method."
0
 
LVL 12

Expert Comment

by:Trygve
Comment Utility
Pessimistic Locking

With pessimistic locking, a page is locked once you begin editing a record on that page with the Edit method. The page remains locked until you save your changes to the record with the Update method or cancel the edit, either with the CancelUpdate method or by moving off the current record. Pessimistic locking is the default type of page locking for Recordset objects.

The main advantage of pessimistic locking is that after you have obtained a lock, you know that you won't encounter any locking conflicts as long as the record is locked. Additionally, pessimistic locking is the only way to guarantee that your application reads the most current data, because one user can't change a record after another user has started to edit it.

The disadvantage of pessimistic locking is that it locks the entire page for the duration of the procedure. Therefore, other users can't change any records on that page until the lock is released.
0
 
LVL 12

Expert Comment

by:Trygve
Comment Utility
Optimistic Locking

With optimistic locking, a page is locked only when you try to save the changes to the record with the Update method. Because the lock is applied only when your application tries to update the record, you minimize the time the lock is in place; this is the main advantage of optimistic locking.

The disadvantage of optimistic locking is that when you are editing a record, you can't be sure that the update will succeed. Your attempt to update the record with your edits fails if another user updates the record first.
0
 
LVL 12

Expert Comment

by:Trygve
Comment Utility
Testing a Record for Locking Status

You may want to check to see if a record is locked without actually locking its page or pages. The following procedure uses the LockEdits property to determine if the current record is locked:

Function RecordLocked(rst As Recordset) As Boolean
   Dim blnLock As Boolean
   On Error GoTo ErrorHandler
   ' Save current value of LockEdits property.
   blnLock = rst.LockEdits
   ' Set pessimistic locking.
   rst.LockEdits = True
   ' Try to edit the record. This generates error 3197 if the
   ' record is locked.
   rst.Edit
   RecordLocked = False
   rst.CancelUpdate
   ' Restore original value of LockEdits property.
   rst.LockEdits = blnLock
   Exit Function
ErrorHandler:
   Select Case Err
      Case 3197
         Resume Next
      Case Else
         RecordLocked = True
         Resume Next
         Exit Function
   End Select
End Function
0
 
LVL 3

Expert Comment

by:chapie
Comment Utility
the error msg "Could not save, currently locked by user" is often mistaken for a data lock error which would state something similar to "current record has changed since editing, would you like to 'save changes to clipboard', 'cancel changes', 'undo update'" or something like that

The error you are refering to is a conflict between modifying objects (a query definition, form design, table design, report, etc.)  I had this problem because I would often times make changes while someone is using a form to input data, and the only times I/we would experience problems is if both myself and the other user entered design mode on the same object.
0
 
LVL 3

Expert Comment

by:chapie
Comment Utility
one thing I forgot to make sure to check (after you find out that record locking is not the problem) if you are sure that you are the only person in the database, then exit out of it, and delete the LDB file with the same name as the MDB.  If this file is not automatically destroyed when you exit, then the MDB still thinks that other users are in the databse, and that is possibly where you are encountering the 'object locking' conflict
0
 
LVL 6

Author Comment

by:devtha
Comment Utility
TRYGVE
       I think you are on the right track. After several thoughts I also came to the same conclusion. I shall work on it.
On the second thought CHAPIE you are correct about the ldb file
some times even if only I am logged on and if I try to see the ldb file on dos prompt by typing TYPE FILENAME.LDB I see more usernames than my name.

Also, about your proposed answer, Yesterday I made sure that I am the only person logged on by exiting all users through the main screen automaticallly as follows
If currenuser <> devtha then
msgbox "   "
docmd quit
end if
I still got the same message.
This is UNUSUAL but is it possible that the system administrator
could put an invisible lock on my application due to politics or rivalry... Just a possibilty to think about. Due you experts think it is possible. Right all along even after using the correct methods of multiuser envi. I am getting this annnoying message.
Thank You. I am increasing the points to 50.
0
 
LVL 6

Author Comment

by:devtha
Comment Utility
Rsellis,
         What are trying to say....?
0
 
LVL 3

Expert Comment

by:chapie
Comment Utility
Rsellis,

if you mean the key as in theprimary index, that has nothing to do with the locking method....the locking is based on the record location, not some bit of data stored in that record...in other words, your primary key could increment by 1000 each seperate record, they will still be locked in a page schematic.

Devtha,

I'm not sure I follow how you made sure everyone else was log'd out, IF you added that bit of code in the startup procedure, then anyone already in the database would not run that proc, since they're past the start up process (I may have misunderstood what you meant)

I the adminstrators put some lock on it (via NT server, or Novelle), then you would get a objects are read-only msg when you start the database, if this has user security on it, and they blocked you the message would say something like you do not have permission to modify this object...one way around that is to copy it and past it as a new name, then you become the owner of that object and can administer it however you choose, until the admin finds it a changes ownership or something like that
0
 
LVL 6

Author Comment

by:devtha
Comment Utility
Trygve,
       The error message I have given in the question posted. Besides that I also see a number 3202.???
I tried the function yu have given , it seems like 7.0 and gives me problem on cancelUpdate.
Is it same as
              sendkeys "{esc}{esc}{esc}"
              cancel = true
              exit ...
I want to know what that message number means and whos is locking it. LAN here uses Novell 4.11
Is there a funcion or command to find who has locked it.
Chapie pretty soon I may have to reject your answer. Your proposed answer does not even relate to my question as you are referring to design mode and more than one user. Both conditions are false in my question.
Devtha.



0
 
LVL 3

Expert Comment

by:chapie
Comment Utility
if you are dealing with applying a filter on a form, then you do not have to be in design mode!! you can change many control and form properties via code (which is what you are doing)

let me pose one question concerning the error message..

"could not save, currently locked by other user"

where is the word 'RECORD' in the error message...it is NOT there, it is only being falsely assumed as being there, check the help file concerning this...what does it say??
0
 
LVL 12

Expert Comment

by:Trygve
Comment Utility
If you want you could mail me the mdb and I will have a look-see. Perhaps it wont help, but it wont hurt ? The code is Access'97 and the cancelupdate could perhaps just be left out
0
 
LVL 6

Author Comment

by:devtha
Comment Utility
Sorry, none of the suggestions worked I shall try to post the code at a later stage.
I can use some help for novell. How do I know if some one has a lock on my app. Can a syst admin overwrite the system.mda permissions.?
POints increased to 70

0
 
LVL 3

Expert Comment

by:chapie
Comment Utility
You admit in your origional question posting that there is no way that any other user could have a lock on a record you are modifying, and that the form is completely unbound

right there should be enough information that states it's not a record locking issue as I have stated all along

One thing that we have not talked about is ... When are you getting this error??  what is happening, are multiple people in the database using it, or do you have multiple instances open on you rmachine?? what exactly is happening !?!

I really don't think that the system/network administration has anything to do with this.  The only thing they could do is lock out the directory so the LDB could not be created therefor no one could access the database at all

the only way I could say you could make sure you are the only on ein the databse is to create a blank databse on your local machine and import all the object (forms, reports, etc), and link the tables to the networked copy...I would be willing to bet if you did that you would never seet his error
0
 
LVL 12

Expert Comment

by:Trygve
Comment Utility
Just a thought...

You should see to it that you and your users have at least the following permissions in the application area. (Where the MDB, MDW and or MDE is located)

Filescan, read, write and Create.

The create permission is neaded because a LDB file is made when the first user opens the MDB, MDE or MDW. If it is not possible to create it, then the base will get locked for other users.
0
 
LVL 6

Author Comment

by:devtha
Comment Utility
Chapie
           I shall try your suggestion.  I am getting error while using the addnew method and then in between setting all the column values from the form and then RS.update.

Trygve,
           I shall try your suggestion first as it is more easier for me. I shall check with the LAN admins.  
Thanks for stcking around experts.

Devtha
0
 
LVL 3

Expert Comment

by:chapie
Comment Utility
I think Devtha that you stated to answer this question in someone else's similar question, so here's it is
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 6

Author Comment

by:devtha
Comment Utility
You got to be kidding with such an answer.
Actually I forgot that you responded to this question along with other experts.
0
 
LVL 6

Author Comment

by:devtha
Comment Utility
CHAPIE,

This is based upon your proposed answer in Kearly's question where we had a conflict and exchange of unaligned thoughts.
There you suggest to put the fornt-end on each user's machine. Then there is a headache involved in updating each machine for even a minor update. I avoided that only for this reason.

TRYGVE and CHAP
I also would like to mention that, my form that uses ADDNEW method followed by an update has some type of manipulation during the coding for the appearance of the form to be printed.
Does that mean that it is non sharable as the design is changed on run time.

0
 
LVL 3

Expert Comment

by:chapie
Comment Utility
I'm not sure I follow about the 'appearance of the form to be printed'..let me see if I did get it right though

if you make any form changes, be it the color of a control, or a filtering of data, then you will get a conflict when trying to close the form....one way around this, that I just not thought of is using the argument that states something to the affect of 'acSaveNo' or acSaveNone or something like that..this will prevent access from trying to save the object before closing it.

I wouldn't have chosen answer except for the fact that I just thought of that arguement *G* let me know if that works for you
0
 
LVL 12

Expert Comment

by:Trygve
Comment Utility
What happens if you move your copy of the application to your local computer and run it from there (just for testing purposes)? Do you get the same problem ?

From the developer CD on error 3202;

•You tried to save a record on a 2K database page that is currently locked by another user. Wait for the other user to finish working with the record, and then try the operation again.

_______________________

LockEdits Property

See Also | Applies To

Sets or returns a value indicating the type of locking that is in effect while editing.

Syntax

BOOL GetLockEdits (VOID);

VOID SetLockEdits (BOOL bFlag);

Parameters



Type



Example



Description





BOOL



bFlag



TRUE (Default) Pessimistic locking is in effect. The 2K page containing the record you're editing is locked as soon as you use the Edit method.

FALSE Optimistic locking is in effect for editing. The 2K page containing the record is not locked until the Update method is executed.




Remarks

You can use the LockEdits property with updatable CdbRecordset objects.

If a page is locked, no other user can edit records on the same page. If you set LockEdits to True and another user already has the page locked, an error occurs when you use the Edit method. Other users can read data from locked pages.

If you set the LockEdits property to False and later use the Update method while another user has the page locked, an error occurs. To see the changes made to your record by another user, use the Move method with 0 as the argument; however, if you do this, you will lose your changes.

When working with Microsoft Jet-connected ODBC data sources, the LockEdits property is always set to False, or optimistic locking. The Microsoft Jet database engine has no control over the locking mechanisms used in external database servers.

Note   You can preset the value of LockEdits when you first open the CdbRecordset by setting the lLockEdits argument of the OpenRecordset method. Setting the lLockEdits argument to dbPessimistic will set the LockEdits property to True, and setting lLockEdits to any other value will set the LockEdits property to False.
0
 
LVL 3

Expert Comment

by:chapie
Comment Utility
trygve...time and again I have tried explaining that the message being display

"COULD NOT SAVE, CURRENTLY LOCKED BY USER "..." ON MACHINE "...

does not have the word RECORD anywhere, and it is not a record locking issue, anyone that has developed in a multi-user environment knows this, for a fact!!

the correct error message for a record locking issue is

"current record has changed since editing, would you like to 'save changes to clipboard', 'cancel changes', 'undo update'"

or something like that, I don't remember the exact wording, but they are very much different message

I don't mean to hammer down a point to death, but you (TRYGVE) have posted very extensive comments, and I hate seeing all that work wasted
0
 
LVL 12

Expert Comment

by:Trygve
Comment Utility
Hello Chapie !

With the danger of repeating myself; From the developer CD on error 3202 (which is the error devtha is refering to...)

•You tried to save a record on a 2K database page that is currently locked by another user. Wait for the other user to finish working with the record, and then try the operation again.

If this description of the possible cause for the error has nothing to do with record locking, then please let me know. It could be my english reading ability that plays me a trick.

Perhaps you are confusing the problem with error code 3186, 3187 and possibly others; "Couldn't save; currently locked by user '|2' on machine '|1'." ???

devtha: It seems to me that the error does not come from the spesific record being locked, but the 2K page in which the record is located. Also like in the other problem you had; do you run update queries at the same data that your code are "working on" This will cause locking errors for sure.

The offer to look at the mdb stands.
0
 
LVL 3

Expert Comment

by:chapie
Comment Utility
Perhaps you are confusing the problem with error code 3186, 3187 and possibly others; "Couldn't save; currently locked by user '|2' on machine '|1'." ???

there now you're admitting it yourself...if you look at the actual question (the very top of the pae *G*) you will see that the origional error message paraphrases much the same....it's not until later on in this discussion that the error 3202 comes up, and I believe that this may be a coincidental occurrance during a seperate incident or troubleshooting session, where it just happened to sneak in place of the origional message
0
 
LVL 12

Expert Comment

by:Trygve
Comment Utility
I always consider the later information to be the most up-to-date, but perhaps it is time for devtha to post the exact error code and message so that we can consentrate on the problem and not on other each other.
0
 
LVL 6

Author Comment

by:devtha
Comment Utility
Chapie there is no need to be jump into arguments. I guess you cannot accept the fact of someone challeanging your answer. That is what happened between us on other post.

Anyway I saw the message 3202 and error string....
Try.. If it is 2k problem then that can be solved (if I am not wrong) in INI file. I have'nt done this but if you guys know please let me know.
Meanwhile I am going to reproduce the same error again and will post the message.
Secondly I shall try the suggestion you guys have mentioned to put the app on local machine and try it.
Thanks again. Increasing the points to 100.
0
 
LVL 6

Author Comment

by:devtha
Comment Utility
'Here is the long code that I am executing On Exit event of a 'field that brings me to a stage for adding new record(s).
'This is the code when executed gives me the error.

////////////////////////////////////////////////////////////////

On Error GoTo AddARowErr

If IsNull([id]) Then
MsgBox "SSN Required."
[id].SetFocus
Exit Sub
End If


DoCmd Hourglass True

DoCmd Print A_SELECTION, , , A_HIGH, 2, False     '2 copies

        DoEvents

       
        Const MB_ICONQUESTION = 32
        Const MB_ICONEXCLAMATION = 48
        Const YES = 6
        Const YES_NO = 4
       

    Dim DocName As String
    Dim LinkCriteria As String

Dim RESULT
RESULT = DLookup("[R/D]", "HRIS", "[SSN] = forms![e39]![id]")

If RESULT = "00" Then
Exit Sub
End If


REG_RD:

Dim WEEKOFF1$, WEEKOFF2$

Select Case (RESULT)
Case "17"                     'SET 1
WEEKOFF1$ = "SAT"
WEEKOFF2$ = "FRI"
Case "23"                     'SET 2
WEEKOFF1$ = "SUN"
WEEKOFF2$ = "MON"
Case "24"                     'SET 3
WEEKOFF1$ = "SUN"
WEEKOFF2$ = "TUE"
Case "25"                     'SET 4
WEEKOFF1$ = "SUN"
WEEKOFF2$ = "WED"
Case "26"                     'SET 5
WEEKOFF1$ = "SUN"
WEEKOFF2$ = "THU"
Case "27"                     'SET 6
WEEKOFF1$ = "SUN"
WEEKOFF2$ = "FRI"
Case "34"                     'SET 7
WEEKOFF1$ = "MON"
WEEKOFF2$ = "TUE"
Case "45"                     'SET 8
WEEKOFF1$ = "TUE"
WEEKOFF2$ = "WED"
Case "56"                     'SET 9
WEEKOFF1$ = "WED"
WEEKOFF2$ = "THU"
Case "67"                     'SET 10
WEEKOFF1$ = "THU"
WEEKOFF2$ = "FRI"
Case "02"                     'SET 11
WEEKOFF1$ = "NIL"
WEEKOFF2$ = "SUN"
Case "05"                     'SET 12
WEEKOFF1$ = "NIL"
WEEKOFF2$ = "WED"
Case "06"                     'SET 13
WEEKOFF1$ = "NIL"
WEEKOFF2$ = "THU"
Case "07"                     'SET 14
WEEKOFF1$ = "NIL"
WEEKOFF2$ = "FRI"
Case "15"                     'SET 15
WEEKOFF1$ = "SAT"
WEEKOFF2$ = "WED"
Case "04"                     'SET 16
WEEKOFF1$ = "NIL"
WEEKOFF2$ = "TUE"
Case "47"                     'SET 17
WEEKOFF1$ = "TUE"
WEEKOFF2$ = "FRI"
Case "12"                     'SET 19 (RD-12)
WEEKOFF1$ = "SAT"
WEEKOFF2$ = "SUN"
Case "13"                     'SET 20 (RD-13)
WEEKOFF1$ = "SAT"
WEEKOFF2$ = "MON"
Case "14"                     'SET 21 (RD-14)
WEEKOFF1$ = "SAT"
WEEKOFF2$ = "TUE"
Case "16"                     'SET 22 (RD-16)
WEEKOFF1$ = "SAT"
WEEKOFF2$ = "THU"
Case "35"                     'SET 23 (RD-35)
WEEKOFF1$ = "MON"
WEEKOFF2$ = "WED"
Case "36"                     'SET 24 (RD-36)
WEEKOFF1$ = "MON"
WEEKOFF2$ = "THU"
Case "37"                     'SET 25 (RD-37)
WEEKOFF1$ = "MON"
WEEKOFF2$ = "FRI"
Case "46"                     'SET 26 (RD-46)
WEEKOFF1$ = "TUE"
WEEKOFF2$ = "THU"
Case "57"                     'SET 27 (RD-57)
WEEKOFF1$ = "WED"
WEEKOFF2$ = "FRI"

End Select

Dim mydb As Database, myworkspace As WorkSpace, mytable As Recordset
Set myworkspace = dbengine.workspaces(0)

myworkspace.BeginTrans

Set mydb = myworkspace.databases(0)
Set mytable = mydb.OpenRecordset("callin", db_open_dynaset)



';;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
';;;;;;;;;;;;;;;;;;;;;;;;    Transaction begins here
';;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;


    Dim n As Integer
    n = (forms![e39]![field954]) / 8
    Dim h As Integer
   
        Dim enddate As Variant
        Dim Skip_restdays As Variant



mytable.AddNew

mytable.[fmla hrs sl] = 0
mytable.[Sl] = 0
mytable.[al] = 0
mytable.[lWOP] = 0
mytable.[COP] = 0

mytable.[id] = forms![e39]![id]
mytable.[from date] = Date
mytable.[p/l] = DLookup("[P/L]", "HRIS", "(SSN)=FORMS![e39]![ID]")
mytable.[r/d] = DLookup("[R/D]", "HRIS", "(SSN)=FORMS![e39]![ID]")
mytable.[leave type] = forms![e39]![leave type]
mytable.[date of absence] = Date
mytable.[field954] = forms![e39]![field954]
mytable.[HRS REQ] = forms![e39]![HRS REQ]
mytable.[from hour] = DLookup("[BEGIN TOUR]", "HRIS", "(SSN)=FORMS![e39]![ID]")
mytable.[pp] = DLookup("[pay period]", "leave year", "(date)=FORMS![e39]![from date]")
mytable.[day of pp] = DLookup("[day of pp]", "leave year", "(date)=FORMS![e39]![from date]")
mytable.[to date] = forms![e39]![to date]

If n < 1 Then
    mytable.[to hour] = Null
Else
    mytable.[to hour] = DLookup("[end tour]", "HRIS", "[SSN] = forms![e39]![id]")
End If

mytable.[time of call] = forms![e39]![clock].caption
mytable.[reason for absence] = forms![e39]![reason for absence]
mytable.[caller] = forms![e39]![caller]
mytable.[phone] = forms![e39]![phone]
mytable.[cppdyr] = DLookup("[ppdyr]", "leave year", "[date] = date()")

    If (forms![e39]![leave type] = "SAL" Or forms![e39]![leave type] = "AL" Or forms![e39]![leave type] = "ALCP" Or forms![e39]![leave type] = "USAL" Or forms![e39]![leave type] = "EAL" Or forms![e39]![leave type] = "UALCP" Or forms![e39]![leave type] = "fAL" Or forms![e39]![leave type] = "fEAL" Or forms![e39]![leave type] = "fSAL" Or forms![e39]![leave type] = "fUSAL" Or forms![e39]![leave type] = "PM-AL" Or forms![e39]![leave type] = "UEAL" Or forms![e39]![leave type] = "UAL" Or forms![e39]![leave type] = "FUAL" Or forms![e39]![leave type] = "FUEAL") Then
      mytable.[al] = mytable.[HRS REQ]
    ElseIf (forms![e39]![leave type] = "SL" Or forms![e39]![leave type] = "USL" Or forms![e39]![leave type] = "SLDC" Or forms![e39]![leave type] = "SLCP" Or forms![e39]![leave type] = "USLDC" Or forms![e39]![leave type] = "USLCP" Or forms![e39]![leave type] = "fSL" Or forms![e39]![leave type] = "fUSL" Or forms![e39]![leave type] = "fSLDC" Or forms![e39]![leave type] = "fUSLDC") Then
      mytable.[Sl] = mytable.[HRS REQ]
    ElseIf (forms![e39]![leave type] = "LWOP" Or forms![e39]![leave type] = "SWOP" Or forms![e39]![leave type] = "LWCP" Or forms![e39]![leave type] = "SUSP" Or forms![e39]![leave type] = "ULWOP" Or forms![e39]![leave type] = "USWOP" Or forms![e39]![leave type] = "ULWCP" Or forms![e39]![leave type] = "fLWOP" Or forms![e39]![leave type] = "fSWOP" Or forms![e39]![leave type] = "fULWOP" Or forms![e39]![leave type] = "fUSWOP" Or forms![e39]![leave type] = "UAWOL" Or forms![e39]![leave type] = "CL" Or forms![e39]![leave type] = "UCL") Then
        mytable.[lWOP] = mytable.[HRS REQ]
    ElseIf (forms![e39]![leave type] = "COP" Or forms![e39]![leave type] = "UCOP") Then
        mytable.[COP] = mytable.[HRS REQ]
    End If
If forms![e39]![leave type] = "PM-AL" Or forms![e39]![leave type] = "AL" Or forms![e39]![leave type] = "ALCP" Or forms![e39]![leave type] = "CL" Or forms![e39]![leave type] = "COP" Or forms![e39]![leave type] = "EAL" Or forms![e39]![leave type] = "FAL" Or forms![e39]![leave type] = "FEAL" Or forms![e39]![leave type] = "FLWOP" Or forms![e39]![leave type] = "FSAL" Or forms![e39]![leave type] = "FSL" Or forms![e39]![leave type] = "LWCP" Or forms![e39]![leave type] = "LWOP" Or forms![e39]![leave type] = "ML" Or forms![e39]![leave type] = "SAL" Or forms![e39]![leave type] = "SL" Or forms![e39]![leave type] = "SLCP" Or forms![e39]![leave type] = "SLDC" Or forms![e39]![leave type] = "SUSP" Or forms![e39]![leave type] = "SWOP" Or forms![e39]![leave type] = "fSLDC" Or forms![e39]![leave type] = "fSWOP" Then
    mytable.[sched] = -1
End If


If forms![e39]![leave type] = "fAL" Or forms![e39]![leave type] = "fEAL" Or forms![e39]![leave type] = "fLWOP" Or forms![e39]![leave type] = "fSAL" Or forms![e39]![leave type] = "fSL" Or forms![e39]![leave type] = "fSWOP" Or forms![e39]![leave type] = "fULWOP" Or forms![e39]![leave type] = "fUSAL" Or forms![e39]![leave type] = "fUSL" Or forms![e39]![leave type] = "fUSWOP" Or forms![e39]![leave type] = "fUAL" Or forms![e39]![leave type] = "fUEAL" Or forms![e39]![leave type] = "fUSLDC" Or forms![e39]![leave type] = "fSLDC" Then
mytable.[fmla hrs sl] = mytable.[HRS REQ]
    mytable.[fmla] = -1
End If

mytable.[date submitted] = Date
mytable.[tasupv] = CurrentUser()

mytable.Update

n = n - 1

 
  h = 1
  While (n > 0)           'loop starts only if called hrs are > 8
       
       Skip_restdays = datecalc(Date, h, WEEKOFF1$, WEEKOFF2$)
       enddate = Skip_restdays

    If DCount("[Holidate]", "Holidy", "[Holidate] Between #" & Date & "# And #" & enddate & "#") > 0 Then
              enddate = datecalc(enddate, 1, WEEKOFF1$, WEEKOFF2$)
    End If
   
mytable.AddNew

mytable.[fmla hrs sl] = 0
mytable.[Sl] = 0
mytable.[al] = 0
mytable.[lWOP] = 0
mytable.[COP] = 0

mytable.[id] = forms![e39]![id]
mytable.[from date] = enddate
mytable.[p/l] = DLookup("[P/L]", "HRIS", "(SSN)=FORMS![e39]![ID]")
mytable.[r/d] = DLookup("[R/D]", "HRIS", "(SSN)=FORMS![e39]![ID]")
mytable.[leave type] = forms![e39]![leave type]
mytable.[date of absence] = Date
mytable.[field954] = forms![e39]![field954]
mytable.[HRS REQ] = forms![e39]![HRS REQ]
mytable.[from hour] = DLookup("[BEGIN TOUR]", "HRIS", "(SSN)=FORMS![e39]![ID]")
mytable.[pp] = DLookup("[pay period]", "leave year", "cvdate([date]) = " & "#" & CVDate(enddate) & "#")
mytable.[day of pp] = DLookup("[day of pp]", "leave year", "cvdate([date]) = " & "#" & CVDate(enddate) & "#")
mytable.[to hour] = DLookup("[end tour]", "HRIS", "[SSN] = forms![e39]![id]")
mytable.[to date] = forms![e39]![to date]
mytable.[to hour] = forms![e39]![to hour]
mytable.[time of call] = forms![e39]![clock].caption
mytable.[reason for absence] = forms![e39]![reason for absence]
mytable.[caller] = forms![e39]![caller]
mytable.[phone] = forms![e39]![phone]
mytable.[cppdyr] = DLookup("[ppdyr]", "leave year", "cvdate([date]) = " & "#" & CVDate(enddate) & "#")

    If (forms![e39]![leave type] = "SAL" Or forms![e39]![leave type] = "AL" Or forms![e39]![leave type] = "ALCP" Or forms![e39]![leave type] = "USAL" Or forms![e39]![leave type] = "EAL" Or forms![e39]![leave type] = "UALCP" Or forms![e39]![leave type] = "fAL" Or forms![e39]![leave type] = "fEAL" Or forms![e39]![leave type] = "fSAL" Or forms![e39]![leave type] = "fUSAL" Or forms![e39]![leave type] = "PM-AL" Or forms![e39]![leave type] = "UEAL" Or forms![e39]![leave type] = "UAL" Or forms![e39]![leave type] = "FUAL" Or forms![e39]![leave type] = "FUEAL") Then
      mytable.[al] = mytable.[HRS REQ]
    ElseIf (forms![e39]![leave type] = "SL" Or forms![e39]![leave type] = "USL" Or forms![e39]![leave type] = "SLDC" Or forms![e39]![leave type] = "SLCP" Or forms![e39]![leave type] = "USLDC" Or forms![e39]![leave type] = "USLCP" Or forms![e39]![leave type] = "fSL" Or forms![e39]![leave type] = "fUSL" Or forms![e39]![leave type] = "fSLDC") Then
      mytable.[Sl] = mytable.[HRS REQ]
    ElseIf (forms![e39]![leave type] = "LWOP" Or forms![e39]![leave type] = "SWOP" Or forms![e39]![leave type] = "LWCP" Or forms![e39]![leave type] = "SUSP" Or forms![e39]![leave type] = "ULWOP" Or forms![e39]![leave type] = "USWOP" Or forms![e39]![leave type] = "ULWCP" Or forms![e39]![leave type] = "fLWOP" Or forms![e39]![leave type] = "fSWOP" Or forms![e39]![leave type] = "fULWOP" Or forms![e39]![leave type] = "fUSWOP" Or forms![e39]![leave type] = "UAWOL" Or forms![e39]![leave type] = "CL" Or forms![e39]![leave type] = "UCL") Then
        mytable.[lWOP] = mytable.[HRS REQ]
    ElseIf (forms![e39]![leave type] = "COP" Or forms![e39]![leave type] = "UCOP") Then
        mytable.[COP] = mytable.[HRS REQ]
    End If

If forms![e39]![leave type] = "PM-AL" Or forms![e39]![leave type] = "AL" Or forms![e39]![leave type] = "ALCP" Or forms![e39]![leave type] = "CL" Or forms![e39]![leave type] = "COP" Or forms![e39]![leave type] = "EAL" Or forms![e39]![leave type] = "FAL" Or forms![e39]![leave type] = "FEAL" Or forms![e39]![leave type] = "FLWOP" Or forms![e39]![leave type] = "FSAL" Or forms![e39]![leave type] = "FSL" Or forms![e39]![leave type] = "LWCP" Or forms![e39]![leave type] = "LWOP" Or forms![e39]![leave type] = "ML" Or forms![e39]![leave type] = "SAL" Or forms![e39]![leave type] = "SL" Or forms![e39]![leave type] = "SLCP" Or forms![e39]![leave type] = "SLDC" Or forms![e39]![leave type] = "SUSP" Or forms![e39]![leave type] = "SWOP" Or forms![e39]![leave type] = "fSLDC" Or forms![e39]![leave type] = "fSWOP" Then
    mytable.[sched] = -1
End If


If forms![e39]![leave type] = "fAL" Or forms![e39]![leave type] = "fEAL" Or forms![e39]![leave type] = "fLWOP" Or forms![e39]![leave type] = "fSAL" Or forms![e39]![leave type] = "fSL" Or forms![e39]![leave type] = "fSWOP" Or forms![e39]![leave type] = "fULWOP" Or forms![e39]![leave type] = "fUSAL" Or forms![e39]![leave type] = "fUSL" Or forms![e39]![leave type] = "fUSWOP" Or forms![e39]![leave type] = "fUAL" Or forms![e39]![leave type] = "fUEAL" Or forms![e39]![leave type] = "fUSLDC" Or forms![e39]![leave type] = "fSLDC" Then
mytable.[fmla hrs sl] = mytable.[HRS REQ]
    mytable.[fmla] = -1
End If

mytable.[date submitted] = Date
mytable.[tasupv] = CurrentUser()
mytable.[r/d] = forms![e39]![r/d]
mytable.[p/l] = forms![e39]![p/l]

    n = n - 1
    h = h + 1
    mytable.Update
    DoCmd GoToRecord A_FORM, "e39", A_Newrec
Wend

mytable.Close
'mydb.Close


 '''''''''''''''    reset the form

DoCmd GoToRecord A_FORM, "e39", A_Newrec

[id] = Null
Me![HRS REQ] = 8
[field954] = 8
[phone] = Null
[caller] = "SELF"
[leave type] = "USL"
[reason for absence] = Null
[from date] = Date
[to date] = Date
[last name] = Null
[first name] = Null
[from hour] = Null
[to hour] = Null
[field193] = Null
[field195] = Null
[r/d] = Null
[p/l] = Null
forms![e39]![field1004].Requery
forms![e39]![field1006].Requery

[id].SetFocus


DoCmd Hourglass False


AddARowExit:
    Exit Sub

AddARowErr:
           MsgBox "Error: " & Error & " (" & Err & ")"
           DoCmd Hourglass False
           Resume AddARowExit



End sub
0
 
LVL 3

Expert Comment

by:chapie
Comment Utility
ykes!! what a mess *G*, may I make one suggested major change, which is to change the If...or statements to a select case, they will perform much faster (possibly) and they will definitely be much easier to read... here's one example


select case (forms![e39]![leave type]
    case "SAL", "AL", "ALCP", "USAL", "EAL", "UALCP", _
            "fAL", "fEAL", "fSAL", "fUSAL", "PM-AL", "UEAL", _
            "UAL", "FUAL", "FUEAL"
        ...code goes here...
    case else
        ...code here, for [leave type] not equal to above values...
end select

the reason I say this will execute faster is because with the if conditions, if the first one is not met, it goes to the OR which causes your code to check the value of [leave type] again...with the select case, it captures the value one time and checks it to each value without having to re-reference the object (leave type) each time

also, another method to optimize this code might be to take the following: (and any portion of your code that updates the recordset according to entries on the form)

mytable.[leave type] = forms![e39]![leave type]
mytable.[date of absence] = Date
mytable.[field954] = forms![e39]![field954]
mytable.[HRS REQ] = forms![e39]![HRS REQ]

and change it to the following (if you followed this you would have to place the field name that the control is updating into the 'tag' property of that control

Dim ctrl as control

for each ctrl in me.controls
    select case ctrl.tag 'checks for the tag property
       case "Field Type", "field954", "HRS REQ"
            mytable(ctrl.tag) = ctrl.value
    end select
next ctrl

beyond the above help...I need to know where the code is erroring out..when the error occurrs where is the highlighted portion of the code??

would it be possible to see this databse (or just the portion that is having problems)...I have a hotmail account setup for this purpose that I rarely ever check, so if you send it, post a comment so I know to check it out and get the attachment (I think it has to be under 1 MB so be sure to repair/compact it first, and strip down all but a few records - I usually suggest dummy records that contain made-up information so that you are not passing any sensitive data...I'm at sirchapie@hotmail.com
0
 
LVL 12

Expert Comment

by:Trygve
Comment Utility
Without exactly knowing which table the form is based on; Perhaps if you use a Sendkeys "+{ENTER}", True to save the current record before the code starts working ?
0
 
LVL 6

Author Comment

by:devtha
Comment Utility
TRYGVE:
       This is my original question.
I am using an unbound form and using recordset addnew method. This record(s) added are totally new to the table and there is no possibility that other user may have locked this records. I stiil get the message "could not save, currently locked by other user"
What the heck could be wrong?
Does the sendkeys come into picture.?
CHAPIE:
        I was afraid of the situation with suggestions. That sidetracks my question. Efficiency is not the problem at this instance the problem is the message 3202 ....

0
 
LVL 12

Expert Comment

by:Trygve
Comment Utility
Forget the sendkeys, forgot about the unbound form.
0
 
LVL 3

Expert Comment

by:chapie
Comment Utility
you're being contradictory...you state the msg is 3202 then tell us the msg states "could not save, locked by another user", which I forget what the numbers ar but I think Trygve stated that msg is 3186, or 3187 (which sounds to me to be about the right numbers)  which is it, and let me reask once again .... where is the error occurring at what point is it being highlighted when choosing 'debug' when the error ocurrs, without knowing this .. well, we have to know this so we can pinpoint the problem

about efficiency not being the problem...well, if you learned how to improve coding the same procedure without having to re-reference forms!blah.blah.[leave type] every single time you want to check all umpty ump 100's of different values of [leave type], then personally I wouldn't complain, seems smarter to follow the free advice of a better way than to shun it and say  'well that's not the problem'... I don't know about anyone else, but that's what I would do
0
 
LVL 6

Author Comment

by:devtha
Comment Utility
I agree, nut that is not the problem we are discussing. So instead of going into discussion I prefer to concentrate on the question. Thanks for Free advice. This is very old code (a beginner coded it) I have not got time to change it and I was concentratiing on changing the form from bound to Unbound.
0
 
LVL 3

Expert Comment

by:chapie
Comment Utility
you've now posted I believe 3 times without answering the key question, so I will post it again

WHERE IN THE CODE IS THE ERROR OCCURRING??

having the code is not enough, we have to know where to start trouble-shooting
0
 
LVL 6

Author Comment

by:devtha
Comment Utility
I am on VACATION will talk to you guys on MONDAY the 20th
0
 
LVL 6

Author Comment

by:devtha
Comment Utility
1)     mytable.[tasupv] = CurrentUser()
2)     mytable.Update
3)     n = n - 1

I tried the code debug and it stopped on the second line and then stayed there for a while and when I used Ctrl+Alt+Del
it was telling the the MS access dbname is not responding.
I had to end the task. This debug ofcourse was done using the retail access version 2.0 and not the runtime version. If I was running the runtime version and entering the data sometimes it stops responding and gets corrupted or sometimes it gives me the message that currently locked could'nt save.....
Devtha
0
 
LVL 6

Author Comment

by:devtha
Comment Utility
Filescan, read, write and Create.
posted on Thursday July 2nd by Trygve.
I suggested to LAN admin (Politically they did not respond)
Now I tried it day before yesterday and it works !!!
I would like to give this points to TRYGVE and so I am rejecting this proposed answer of CHAPIE.

TRY please post any comment as an answer.
0
 
LVL 12

Accepted Solution

by:
Trygve earned 100 total points
Comment Utility
Glad it works !

Here is the previous posting about permissions;

You should see to it that you and your users have at least the following permissions in the application area. (Where the MDB, MDW and or MDE is located)

Filescan, read, write and Create.

The create permission is neaded because a LDB file is made when the first user opens the MDB, MDE or MDW. If it is not possible to create it, then the base will get locked for other users.

Have a ncie day everyone !
Trygve
0
 
LVL 6

Author Comment

by:devtha
Comment Utility
Thanks TRYGVE.....
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

743 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now