I have a table that is constantly changing values. It's just a holding table for incoming things until they are dealt with, and then they are deleted, so there's only a few items in it at any given time.
One of the fields is a unique value called WebID, and I would like to create a TempVar whose name = WebID. In other words, if I have a WebID value of 12345, I'd like to create a TempVar whose name is 12345. There are lots of programming reasons where that would make life much easier for me. Does anyone have any ideas on how to accomplish this?
OK, I'll try to be clear. When a record appears in the holding table, I'm creating a process which automatically assigns that record by moving through a list of available processors, call them Joe, Tom, and Sally. So Joe's up first, but he's doing something else and can't handle the new record. A popup comes up on his screen allowing him to accept or reject it. If he accepts, all's right with the world. If he can't take it and rejects it, it goes to Tom. Same thing. If he can't do it, it goes to Sally, and finally, if no one can take it it goes to an administrator for manual assignment. The names are the result of a query which sorts with the least productive person at the top of the list, and one of the output columns is a sequential number. Since available personnel change by the minute, the sequential number also changes. At this moment, Joe=1, Tom=2, and Sally=3, hence the earlier stated order.
I was thinking that I could use a TempVar whose name matches the WebID number, say 12345, to keep track of whether that specific record has been accepted or rejected. Honestly, as I'm thinking it out while writing and explaining this I'm not sure this is the best approach. I already have a checkbox in the holding table called Pending. When a record is first inserted in the table Pending is False. As soon as the first attempt to assign is made it is set to true. I have a column called Order that will record the last person attempted.
So a new record comes in, Joe is up first, and he receives an on-screen message asking whether he can take it. Meantime, the Order field for that record is already set to 1 (Joe). If he rejects, Order field is set to Order+1 (2), and Tom gets the accept/reject message. If he takes it, that deletes the record from the holding table and the assignment is done in the actual record table.
My initial thought was that there could be several records in the process of being assigned and the TempVar would facilitate this but I'm now far less convinced of that. I can just come back and read the holding table looking for Pending=False, and send that to whoever has Order number of 1. I can then read the table for Pending=True, and send a message to the next person (Order + 1).
I'm thinking I can accomplish this as stated, but now that I started this mess I'd still like to know for future reference if a TempVars name can be created using an expression!
Thanks. By forcing me to really verbalize what I was intending I think I'm going to abandon this approach.
Going through the process of verbalizing is a great analytical/design tool. It forces you to describe your thoughts clearly. And in that process what you considered "clearly" takes a bashing when you put it into words. We've all been there. Quite often the process often leads to an answer or decision---I solved it or that won't work...
Someone's signature in one of the forums is a quote attributed to Richard Feynman.
"Don't write it down until you understand it" (or similar)
I find writing it down -especially to show someone else- helps you understand.
I think moke123 has answered your direct question re TempVars, but maybe you're thinking of a different approach--possibly no TempVars??
It's interesting. I used a simple macro with a SetTempVar event. I put =UserName() into the name field and it works fine. TempVars!MyUserName works perfectly. Then I tried ="My"& "Var". Again, TempVars!MyVar works as expected. However, if I try =6+3, TempVars!9 fails, as does just a simple 9 for the TempVar name. Both have the same error message in the Immediate Window. "Type-declaration character does not match declared data type".
I'm finding out that you can not use a number for a TempVar name, but you can use an expression that evaluates to text. I can't even use =Str(9). TempVars!9 just doesn't work. Maybe someone can think of a way of assigning a number as a TempVars name, but even a simple direct assignment fails.
I certainly learned something. I hope others did, too.
Even if I were convinced that a TempVars that equaled the WebID number was the best approach (and I'm not at all convinced), it looks like it's not possible, so I'm going to use another approach. The Pending bit field will now only be true if the record is literally pending...if it has been offered to someone but not accepted yet. If it is rejected the Pending bit will go back to False. I have some refinements to add into it, but I'm pretty convinced I can drag it kicking and screaming across the finish line.
Dim i As Integer
Call Tempvars.Add("1", 0)
Call Tempvars.Add("2", 0)
Call Tempvars.Add("3", 0)
Call Tempvars.Add("4", 0)
For i = 4 To 1 Step -1
Tempvars("" & i) = 51-i
Next
For i = 1 To 4
Debug.Print Tempvars("" & i)
Next
Thank you but it doesn't solve the real problem I'm puzzling over. I really would like to use a simple DLookup expression and use the int result as the name of a TempVar. My objective is to look up that WebID number 12345 and create a TempVars!12345 from it.
I really appreciate your code but I don't see how I can accomplish this. Do you have any suggestions on how that could be implemented?
Personally I cannot see the benefit of having a variable named after the contents of a field?
Normally a variable would hold the contents of a field, but still....
From the debug window.
I don't think your "business issue" will be solved by some customized tempVars. Based on your description of "list of available processors, call them Joe, Tom, and Sally..", I think your root business process is generally resolved with "queuing theory" concepts. I did a quick search on the forum and found only a few of older posts that mentioned the term (no solutions). The_Doc_man,who is a long time forum participant, was one of the respondents and I think he may have more practical and statistical experience to offer some focused advice on how to address "assigning and completing the incoming workload with multiple processors".
I also found this thread (no solution, but similar problem) that may give you some insight.
it may be that part of the problem with assigning a number as a tempvar name is that a tempvar can also be called by its position within the tempvars collection- tempvars(0), tempvars(1), etc.
My problem with this approach is, what happens when the second item comes in and the first hasn't been assigned yet because even the supervisor is busy?
In a "traditional" help desk or work-flow situation, what you do is track the date/time of creation (or detection or however it comes into existence) and give it an ID number, a status code (something morally equivalent to "Pending Assignment"), and leave a blank for ownership.
Then you don't CARE who gets it first. You just have a popup or put a text box somewhere on the main work screen that gets updated once per minute by a query that looks for the count of records in the "Pending Assignment" state.
When one of your workers is available, that person opens up a query of ALL items sorted by age and selected by the "Pending Assignment" status. So your worker can look at problems and pick one, but knows which one has been waiting longest. Your worker clicks a button to say "Select Problem" and starts a sequence that first attempts to lock the record (perhaps with a status flag), then attempts to allocate the problem to his/her ID, then changes the status from "Pending Assignment" to "Assigned" - and then finally unlocks the record in four discrete steps. And here is the kicker: if the attempt to lock doesn't work, you do not proceed with the other steps. Here's how you might do this, with the assumed field name of "ReserveRecord" for the lock flag:
Code:
Dim WrkDB as DAO.Database
...
Set WrkDB = CurrentDB
UpdateString = "UPDATE WorkingList SET ReserveRecord = TRUE " & _
"WHERE RecID = " & CStr(CurrentRecID) & " AND ReserveRecord = FALSE ;"
WrkDb.Execute UpdateString
RecAff = WrkDb.RecordsAffected
...
I'm sure there is a lot more than this, but the idea is that either ONE record will be affected (and you got the record) or NO records will be affected (and you didn't get the record.) Once this happens, you can report to whoever made the attempt that the record was moved to their queue OR that the record was reassigned before the assignment could be completed.
What you are doing here is based on queueing theory, what we sometimes call the "many equal processors" case. Having that TempVar is a problem because it is not visible to all possible agents. More specifically, the TempVar is actually visible to EACH of them - but it is a different COPY of a "virgin" TempVar - because TempVars are NOT shared among sessions. So you will have as many TempVars as there are people attempting to use the database, each with their own separate front end.
Even if you committed the abomination of sharing the actual front end, the TempVars would be in different session workspaces because that kind of variable is NOT shared.
Using the first (closest) thing that IS visible to everyone means that you must use the contents of the BE - which means you must prep the BE to hold the data needed for a time-ordered queue dispatching system with multiple equal agents. And, I would point out, letting the supervisor assign one of these things probably is wrong anyway. If your agent Tom had the chance to take it and skipped on it, perhaps there was a reason - like "Not Tom's Expertise" or something equally simple.
The issue to consider for assignment is that if your agents AREN'T equal in ability, then you have to allow the specialty cases to be handled by those with the specialty ability. Which means this is NOT an "equal problem, equal multiple processor" problem in queueing theory. I shudder to think of the added complexity.
Read over this and decide whether you want to just let agents take what they know and hope that you have sufficient overlap to handle all that comes in. And if that is NOT what you have, then you have to consider (a) training agents with more skills or (b) hiring agents with more skills.
Before you ask, I was at times a Tier II or Tier III responder for the U.S. Navy Reserve's Personnel Management Help Desk. Did that on and off for 28+ years. I know problem queueing systems. Wouldn't want to write one, but I know how they work.
jdraw, thank you very much for all the threads you linked. I read through them all and actually responded to one of them because I thought that a solution I had come up with might help someone else. Old thread, I know, but ya never know...
The thing is, I already have an assignment list. It's too complicated to explain our whole business model and the reasons for the order we're using, but the bottom line is that I created a query whose output is a list in the order I want to assign things to, and there's a sequential number field in that query as well. Beyond that, the actual assignment routine that generates on-screen popups to assignees exists and has been in use for several years, though for manual assignments. I'm just harnessing what I already have and automatically inserting the necessary information into the assignment table, allowing the rest of the routine to continue as it always has.
So I lookup number 1 in that query and send that user an on-screen request. Normally they'd just accept, and that user will drop to the bottom of the list, or off the list entirely depending on what was accepted. At the time it's assigned the holding table I mentioned earlier (Automation) will also hold the integer 1, corresponding to the first processor available. If for some reason #1 can't handle it, it's rejected on-screen (again, already a part of our operating system), and it comes back and is automatically reassigned to number 2. 99% of the time nothing's going to get past #2, but there will be a failsafe that will redirect to an administrator for manual assignment if nobody takes it. I was toying with the idea of a TempVars named via an expression, but I've already moved past that.
Moke, you're probably right. I did find that a TempVars name can be set programmatically as long as it's text and not numeric, so that was informative.
Grandpa Doc_Man, thanks! What I have is a hidden form that opens on login with a 5 second event attached. Every 5 seconds each user's computer searches through a series of things, and if any evaluate to true then the sequence attached to that is followed. It could be a reminder that their lunch break starts in ten minutes, or a notice that something has been assigned to them, or an Instant Message through a custom system, or any of about 20 different things that are looked at. That system already looks at a table and opens a popup form which tells the user something has been assigned, giving them the opportunity to accept or reject it. All of that infrastructure exists. All I'm going to do is append the assignment table with the info needed to accept/reject. The reject system exists as well, so I'm going to modify it to allow the automation process to loop and move to the next person on the list.
I had thought about the expression-set TempVars name, but I'm seeing that it won't be needed.
Mark, if it were possible to assign a TempVars name as a number Access would recognize it because it would be preceded with a TempVars! TempVars!12345 is not the same as 12345, and I don't think it would be an issue for Access to understand. It's a moot point, though, since it seems that it won't accept a number as a name.
You guys are all awesome. Thanks for all your ideas.
No. You are missing the point of queueing that is going on here. YOU don't assign to anyone and let them reject it. By doing that, you are making work for yourself. You might as well have not assigned it in the first place. And what do you do if EVERYBODY looks at the problem and rejects it because they are ALREADY up to their arse in alligators and the swamp is slowly filling up?
As a Tier III responder, I would sometimes get something dropped into my queue because it was my listed specialty (usually on the system for which I was the primary admin). Those, I was not allowed to reject. BUT because I was Tier III and not a lower tier (and because I had a system to run), I was not required to take ANY problem that hadn't reached that specialty tier yet.
From your earlier discussion, there is some question in my mind as to WHY a supervisor would assign a problem UNLESS some specialty was in play. Otherwise, there is no advantage in the business model you described for assigning ANYTHING. The advantage to the "voluntary assumption of duty" technique is that the next available agent can pick up the problem as soon as they are done with another problem. Anything else borders on "robbing Peter to pay Paul."
Of course, English is one of the worst languages to describe problems so we may very well be discussion a misunderstanding. If so, I apologize ahead of time for missing your point. But at the moment, I don't think I did.
