RECORD LOCK MESSAGE
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?
What the heck could be wrong?
wayneb
Have you repaired and compacted this data base it sounds like it is damaged.
devtha: Is this the form that worked from your other question ? Maybe you have set some really pessimistic locking that locks the whole table ?
ASKER
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
TRYGVE this is not same form..
I shall look into pessimistic locking. Please shed more light on it.
thanks
Dave
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.
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."
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."
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.
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.
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.
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.
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
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
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.
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.
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
ASKER
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.
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.
ASKER
Rsellis,
What are trying to say....?
What are trying to say....?
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
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
ASKER
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.
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.
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??
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??
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
ASKER
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
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
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
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
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.
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.
ASKER
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
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
I think Devtha that you stated to answer this question in someone else's similar question, so here's it is
ASKER
You got to be kidding with such an answer.
Actually I forgot that you responded to this question along with other experts.
Actually I forgot that you responded to this question along with other experts.
ASKER
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.
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.
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
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
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.
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.
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
"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
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.
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.
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
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
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.
ASKER
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.
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.
ASKER
'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
';;;;;;;;;;;;;;;;;;;;;;;;;
';;;;;;;;;;;;;;;;;;;;;;;; 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].captio
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].captio
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].Re
forms![e39]![field1006].Re
[id].SetFocus
DoCmd Hourglass False
AddARowExit:
Exit Sub
AddARowErr:
MsgBox "Error: " & Error & " (" & Err & ")"
DoCmd Hourglass False
Resume AddARowExit
End sub
'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
';;;;;;;;;;;;;;;;;;;;;;;;;
';;;;;;;;;;;;;;;;;;;;;;;; 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].captio
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].captio
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].Re
forms![e39]![field1006].Re
[id].SetFocus
DoCmd Hourglass False
AddARowExit:
Exit Sub
AddARowErr:
MsgBox "Error: " & Error & " (" & Err & ")"
DoCmd Hourglass False
Resume AddARowExit
End sub
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
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
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 ?
ASKER
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 ....
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 ....
Forget the sendkeys, forgot about the unbound form.
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
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
ASKER
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.
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
WHERE IN THE CODE IS THE ERROR OCCURRING??
having the code is not enough, we have to know where to start trouble-shooting
ASKER
I am on VACATION will talk to you guys on MONDAY the 20th
ASKER
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
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
ASKER
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks TRYGVE.....