andyeastes
Registered User.
- Local time
- Today, 09:49
- Joined
- Dec 29, 2010
- Messages
- 31
Hello,
I was not sure if this post should be in the queries or macros section.
Basically I have a few forms that users fill out and then the data from that table is appended to a transactionTBL and then deleted from the working table. From there it goes into inventoryTBL. All of this is done through a macro wherein it runs an append query, then a delete query, and then calls another macro that does the same thing to another table (i.e. checks for conditions and then appends but instead of deleting marks as processed in this case).
What is happening is that in both cases I am having instances where it adds the record multiple times, usually 3 instead of the desired 1. And then if happens in the first one it usually happens in second case as well so the final result is the same record there 6 times. What is strange is that this happens about 30 times a day of 600+ transactions so a relatively low amount but is still a huge problem.
I am not sure if this is a case where since it is a multi user environment that another user is calling the macro and query 1 (append) is happening again from macro(secondcall) before macro(firstcall) has a chance to delete the records or mark them as processed. My understanding of the macros is that they would wait for call1 to finish before beginning call2 by another user?
Another thought was that it is sort of looping the query maybe? I have onerror goto next in one of the macros should that matter?
I realize I could fix this by unique key indexes I think but that would be a patch not a fix to the real issue? Also for case1 the transactionTBL cannot have that anyways.
Any help would be greatly appreciated as I have been struggling with this forever! I would be happy to post code but not even sure what code would be helpful? Thanks in advance.
Andy
I was not sure if this post should be in the queries or macros section.
Basically I have a few forms that users fill out and then the data from that table is appended to a transactionTBL and then deleted from the working table. From there it goes into inventoryTBL. All of this is done through a macro wherein it runs an append query, then a delete query, and then calls another macro that does the same thing to another table (i.e. checks for conditions and then appends but instead of deleting marks as processed in this case).
What is happening is that in both cases I am having instances where it adds the record multiple times, usually 3 instead of the desired 1. And then if happens in the first one it usually happens in second case as well so the final result is the same record there 6 times. What is strange is that this happens about 30 times a day of 600+ transactions so a relatively low amount but is still a huge problem.
I am not sure if this is a case where since it is a multi user environment that another user is calling the macro and query 1 (append) is happening again from macro(secondcall) before macro(firstcall) has a chance to delete the records or mark them as processed. My understanding of the macros is that they would wait for call1 to finish before beginning call2 by another user?
Another thought was that it is sort of looping the query maybe? I have onerror goto next in one of the macros should that matter?
I realize I could fix this by unique key indexes I think but that would be a patch not a fix to the real issue? Also for case1 the transactionTBL cannot have that anyways.
Any help would be greatly appreciated as I have been struggling with this forever! I would be happy to post code but not even sure what code would be helpful? Thanks in advance.
Andy
