Form to function as a pending queue (1 Viewer)

Jupie23

Registered User.
Local time
Yesterday, 19:29
Joined
Nov 9, 2017
Messages
81
I'm building a database for work. I've got a good start, but I'm having trouble with part of it. This is what they want:
  1. Import a spreadsheet of accounts with missing documents (done)
  2. User will view the record and see if they have received the documents
    1. If yes, complete from database
    2. If no, fill out some fields (name/address/what is needed, etc.), create and send a letter to customer.
      1. User will mark the record as pended for 15/30/45/60 days, depending on situation, and add notes. Date/userID auto fill.
    3. Have a "Pending Queue" where anything that is due to be looked at again will appear
    4. Repeat - look for documents. If not received, send another letter and pend again
    5. Complete from database and remove from pending queue once docs received.
The main table (tblL2L) contains the account info, customer name, address, etc. There is a second table (tblAction) that has date, user, comment, and pend time. This is a subform on the main form. It adds a new record to tblAction each time they fill out the subform, but there should only be one active pend at a time. How would I get the query to only pull the most recent one?

Also - is it even possible to have 4 different pend times (15/30/45/60) and have them all show up in the same place (query/form) when their time is up?

Any ideas on how best to approach this scenario? Any help would be appreciated! Thank you!
 
Last edited:

June7

AWF VIP
Local time
Yesterday, 16:29
Joined
Mar 9, 2014
Messages
3,362
Filtering for most recent record would likely involve some VBA.

4 different pend times may mean 4 records. What do you mean by 'same place' - a report?
 

Jupie23

Registered User.
Local time
Yesterday, 19:29
Joined
Nov 9, 2017
Messages
81
4 different pend times may mean 4 records. What do you mean by 'same place' - a report?

One button would open a form to show new records they haven't looked at yet, and one button would open a form that shows all records where the pending time is over, be it 15 days or 45 days. I would like to have just one place for them to click to see anything they need to work again.

Example: Today, 9/24 - I have an account that is missing documents. I fill out the fields to create a letter telling them to send me the document, print and send. Then I add a comment and select that I want to pend it for 15 days. On 10/9, I click the "pending queue" button to open the form, and that same account should show up in there now (but not before). Again I will check to see if I have received what I asked for. If not, send another letter, and pend it again for 30 days. Then it will disappear from that "pending queue" and reappear in 30 days, so that when I see it in there I know it's time to look at it again.
 
Last edited:

June7

AWF VIP
Local time
Yesterday, 16:29
Joined
Mar 9, 2014
Messages
3,362
Most anything can be done with enough code. For a 'search' form, review http://allenbrowne.com/ser-62.html

You can either add a new record for each pend action (if you care about history) or modify data of existing record.
 

Jupie23

Registered User.
Local time
Yesterday, 19:29
Joined
Nov 9, 2017
Messages
81
Most anything can be done with enough code. For a 'search' form, review http://allenbrowne.com/ser-62.html

You can either add a new record for each pend action (if you care about history) or modify data of existing record.

It is currently adding a new record for each pend action. I would like to keep the history. Could I somehow use a checkbox to check the most recent one and uncheck the previous one, to be used in the query?
 

Isaac

Lifelong Learner
Local time
Yesterday, 17:29
Joined
Mar 14, 2017
Messages
2,733
It almost seems like it might be helpful to add a column or two to the main table. Just off the top of my head, maybe a column for the current pend days state - 15, 45, etc, and maybe a column PendedUntilDate, which is populated if the item is still in any pend status, and is wiped out if the issue is resolved. This could be critiqued as, well, technically all of that is able to be figured out based on your current setup, but .... if the change makes it easier to mentally digest (for you and others who will inevitably have to understand the db), it could be a positive thing. And it might make current (and future needed) queries a bit simpler to create.
 

Jupie23

Registered User.
Local time
Yesterday, 19:29
Joined
Nov 9, 2017
Messages
81
It almost seems like it might be helpful to add a column or two to the main table. Just off the top of my head, maybe a column for the current pend days state - 15, 45, etc, and maybe a column PendedUntilDate, which is populated if the item is still in any pend status, and is wiped out if the issue is resolved. This could be critiqued as, well, technically all of that is able to be figured out based on your current setup, but .... if the change makes it easier to mentally digest (for you and others who will inevitably have to understand the db), it could be a positive thing. And it might make current (and future needed) queries a bit simpler to create.

I like that idea, as I can do enough VBA to get by but struggle a bit when it gets more complicated. This database will hopefully be replaced by a web app once the developers can get to it, so it doesn't necessarily have to be fancy, just need it to work! I will add those columns to the main table and see where I can get. Thanks!
 

Isaac

Lifelong Learner
Local time
Yesterday, 17:29
Joined
Mar 14, 2017
Messages
2,733
Good luck! Hope it goes well.
This database will hopefully be replaced by a web app once the developers can get to it
You just never know how entrenched & valuable your app might be & remain. The old "this Access tool is just a prototype until App Dev can make it in such-and-such platform" is classic.....but how many times do we Access developers hear that, then make something so great that 3 years later, they still haven't 'gotten to it' ? Or they finally delve into it and report back, "I'm not sure what we were planning to do in .Net would be any better than this!"
Which can actually be a nice feeling. Best of luck & let us know if you run into issues.
 

Pat Hartman

Super Moderator
Staff member
Local time
Yesterday, 20:29
Joined
Feb 19, 2002
Messages
30,090
This database will hopefully be replaced by a web app once the developers can get to it,
Why do people think that web apps are "better"? They have one advantage (ok, maybe 2) and that is that they work over the internet and Access doesn't have a similar facility. Does EVERY app need to work over the internet? The other potential advantage is that although Access apps can support hundreds, even thousands of simultaneous users (depending on how many seat licenses you have for SQL Server) but they are essentially ONE developer apps. There is no good way to have multiple developers working at the same time on an Access app. Alright, alright, three advantages, you can't build games that run on your phone with Access :(

Every Access app that I have built that has actually been replaced by a web app was far superior to the final solution and cost less than 25% of the web app that replaced it. You get a huge bang for your buck with Access in the right hands. In one case, the Access app cost about $100,000 but its web replacement cost 3.5 MILLION dollars, about 1 MILLION in additional hardware costs, AND about $500,000 per year in maintenance because the web developers elected to create a non-normalized schema which caused extensive programming work as things changed. That app was purchased with MY TAX MONEY. And now our great leader (Ned Lamont) who thinks he knows all about the science has decided that Connecticut has to run its own vaccine trials because they think they know better than the FDA. Sorry for the political comment, it was just a continuation of the stupid decisions made by my home state leaders.
 
Last edited:

Jupie23

Registered User.
Local time
Yesterday, 19:29
Joined
Nov 9, 2017
Messages
81
Why do people think that web apps are "better"? They have one advantage (ok, maybe 2) and that is that they work over the internet and Access doesn't have a similar facility. Does EVERY app need to work over the internet? The other potential advantage is that although Access apps can support hundreds, even thousands of simultaneous users (depending on how many seat licenses you have for SQL Server) but they are essentially ONE developer apps. There is no good way to have multiple developers working at the same time on an Access app. Alright, alright, three advantages, you can't build games that run on your phone with Access :(

Every Access app that I have built that has actually been replaced by a web app was far superior to the final solution and cost less than 25% of the web app that replaced it. You get a huge bang for your buck with Access in the right hands. In one case, the Access app cost about $100,000 but its web replacement cost 3.5 MILLION dollars, about 1 MILLION in additional hardware costs, AND about $500,000 per year in maintenance because the web developers elected to create a non-normalized schema which caused extensive programming work as things changed. That app was purchased with MY TAX MONEY. And now our great leader (Ned Lamont) who thinks he knows all about the science has decided that Connecticut has to run its own vaccine trials because they think they know better than the FDA. Sorry for the political comment, it was just a continuation of the stupid decisions made by my home state leaders.

That is a good point! I don't have much knowledge about web apps but assumed they were probably better because it's actual developers creating them vs. me making what they call a "homegrown solution." And actually, the only one I've seen made by them doesn't look all that fancy! How ridiculous of them to replace your working database with an app that costs that much! That must be so frustrating!

Any idea on how I can take the number of days they chose to pend and add it onto a date?
PendTime = 15/30/45/60 days
On afterupdate, I want it to fill in PendedUntilDate with today's date + the number of days in PendTime.

I see there is a DateAdd function, but not sure if that would be the right thing to use for this situation or how.
 

June7

AWF VIP
Local time
Yesterday, 16:29
Joined
Mar 9, 2014
Messages
3,362
Could use DateAdd() function but arithmetic with date/time value defaults to day unit so can simply add: Date() + PendTime.

In VBA to save to table field: Me!PendedUntilDate = Date() + Me.PendTime

Real trick is figuring out what event to put code into. Simpler to save Date() and PendTime then in query just add the two fields.
 

Pat Hartman

Super Moderator
Staff member
Local time
Yesterday, 20:29
Joined
Feb 19, 2002
Messages
30,090
but assumed they were probably better because it's actual developers creating them
Doesn't mean that they are good developers. Just look at all the bad web pages out there that don't function correctly. This was exceptionally painful because the debacle was paid for with MY TAX DOLLARS.
 

Isaac

Lifelong Learner
Local time
Yesterday, 17:29
Joined
Mar 14, 2017
Messages
2,733
That is a good point! I don't have much knowledge about web apps but assumed they were probably better because it's actual developers creating them vs. me making what they call a "homegrown solution." And actually, the only one I've seen made by them doesn't look all that fancy! How ridiculous of them to replace your working database with an app that costs that much! That must be so frustrating!
You bring up some interesting points. This topic is always interesting to me, as it's a continual theme we work through in the real corporate world as developers whose repertoire includes Access. If I am to be totally honest with myself, I'd have to admit that when it comes to a team of .Net (for example) developers planning to "upgrade" my Access db to their platform, quite possibly the top most persuasive "reason" they could provide for why it might end up better is simply the fact that usually, in that scenario, those folks actually ARE my 'superiors'--app-development-knowledge-wise...Which then could be assumed to result in any number of safer and more reliable protocols being in place. The next reason right after that would probably be Security in general.

Having said that, it certainly is not always true. As soon as I typed that, an oddly reversed situation that I experienced at a job a couple years ago came to mind. There was an Access database that I'd inherited. It was, in my estimation, VERY poorly constructed by an amateur or hobby Access developer. The code project hadn't even been compiled--and actually wouldn't compile!!!--But, it usually 'worked' as pertained to its core expected functionality at a basic level. There apparently had been talk for some time (including before I came) of 'upgrading' this db to a SQL Server / .Net platform. I ended up trying to counter what I found as several major misconceptions:
1) That in order to use SQL Server, we also had to use .Net. Or, that if we were going to use SQL Server, we probably "ought" (for some reason) to use .Net
2) That in order to take the db from a pretty crappy app to a really nice, high-performing, intuitive, user-friendly, feature-rich app, we would have to use .Net
3) That we would assume I as the app developer/migrater would want nothing to do with creating the tables in Sql Server--that I'd just let a DBA glance at the Access tables and then make all of their own DDL decisions
Add to all that, my manager actually suggested that maybe I could make the .Net app. Telling him my only .Net experience was vb.net in the context of SSIS, (and he wanted me to make a C#.Net winforms app) didn't faze him at all--It's easier than you think, he told me. SO determined they were that .Net would just inherently be better than Access (ignoring the difference between awful Access dev vs. better Access dev), that they didn't even care if a 0% experienced person used this project as their first try ever at c#.net

Talk about being blindly obsessed with a platform for no particular reason! What a story. I spent about a week splitting the db, adding some intuitive stuff to the interface and making the forms work faster, in addition to splitting the db, creating an auto-version distribution method and migrating the back end to SQL Server, and it started working so well that talk of .Net faded away somewhat....Still, I ended up leaving the company (for pay reasons) prior to such time as that experience could turn into the fiasco it was destined for. Yikes.
 

Pat Hartman

Super Moderator
Staff member
Local time
Yesterday, 20:29
Joined
Feb 19, 2002
Messages
30,090
I blame a lot of that "confusion" on Microsoft's marketing (or lack thereof) of Access. The present it as a "toy" and so the world views it as a "toy". Any tool used by someone incompetent isn't going to produce a great result. Just because you can use a chain saw to cut up a big tree doesn't mean you can make an ice carving. or a totem pole. Even the SQL Server people are so confused about what Access actually is that they think Access is their competitor rather than a complement to SQL Server. And this is the team that is responsible for Jet so in theory, at least some people on their team "know" that Access isn't a database. Jet is the database engine. If anything competes with SQL Server, it would be Jet, NOT Access. With A2007, the Access team took Jet and turned it into ACE which they now control. SQL Server is still responsible for maintaining Jet as long as it still lives.

Access is pretty much the best tool on the market for a certain class of applications. Of course, you need someone competent at the helm. My feeling is use the right tool for the right job. When my husband and I moved into our first house, our tool collection contained a screwdriver and a hammer so all jobs were done with a screwdriver and a hammer. It only took my husband two hours to drill a hole in the side of a cabinet so he could connect the refrigerator's ice maker and water spout to the cold water line. I knew at the time, I just should have gone to the hardware store and bought a d*** drill!!! but it's hard to argue with a man about stuff like this.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 01:29
Joined
Feb 19, 2013
Messages
12,585
One of my stories. About 20 years ago I wrote an app for a client to manage sales commissions of about £20m/year - spread around 600 sales people. It replaced an excel system that took 5 people to manage as they had around 6000 queries a year from sales. Once launched the number of queries dropped to less than 100/year, the commission team was reduced to 2 people and around £2m/year was saved from commission over payments. (Under the excel system, person A was paid in error, but says nothing, person B who should have been paid, complains and gets paid. Person A did not have to pay back the overpayment)

Two years later, senior management were concerned that key systems such as my app were supported by individuals and not large companies (not unreasonable, but I do have keyman insurance). So it was decided to replace it with an offering from a UK supplier of commission management systems at a cost of circa £1m plus £500k for customisation and £200k for annual licenses (all the sales people had to have a license). Once introduced, queries rose to 1500 a year and there were constant additional costs to cater for changes to commission rules, new markets etc.

The irony is I'm still here, but the commission company was taken over by an american company, the UK servers were replaced with ones based in the US resulting in slower performance over the Atlantic cables of the time and then the purchasing company stopped supporting my clients application and insisted they moved to their own - because they did not retain anyone from the UK company who knew how it worked.
 

Isaac

Lifelong Learner
Local time
Yesterday, 17:29
Joined
Mar 14, 2017
Messages
2,733
@CJ_London Wow, great story. The sales people that work for software companies can act pretty persuasively on management/purchasers! LOL
 

CJ_London

Super Moderator
Staff member
Local time
Today, 01:29
Joined
Feb 19, 2013
Messages
12,585
biggest single saving I achieved was £200m/year. Clients corporate apps didn't talk to each other and they knew they were losing money, but didn't know how much so I wrote an access app that dragged data from multiple sources, identified inconsistencies and provided a 'true' position. I did a toting up of how much I have saved companies over the last 20+ years - works out at an annual running rate of $500m/yr if all those savings were still being made. Shame I don't get paid a percentage! I have offered on a number of occasions but no takers sadly.
 

Users who are viewing this thread

Top Bottom