reoccurring orders

[miked18]

Hello, I am struggling to find a way to do reoccurring orders. Hoping for some help.. It has been over 10 years since I've messed with ms access, so I remember a bit, but still getting back into the swing of things.

I am trying to make a database for ordering products. When a new customer signs up, they can do a single order, or reoccurring subscription. Subscriptions can be every 1, 2, 3, or 4 weeks. On my order form, it has the normal stuff. customer name, delivery date, and product info. I also have delivery frequency, ie, subscription or single order. I have it so it runs a SQL append query and enters the data fine for a single record. What I need is how can I make it so when I submit the order it duplicates that order every 1,2,3,or 4 weeks (depending of frequency) up to the delivery end date.

my file is too large to upload here.

Delivery days are typically every Tuesday for home delivery, and Wednesdays for businesses. Though those days may vary in the future.
TIA,
Mike

 

[theDBguy]

Hi. Welcome to AWF!

You should be able to manage this if you store all the necessary info in a table. If you don't want to manually generate the reoccurring orders, you may have to use a form timer and keep your database file running 24/7.

 

[The_Doc_Man]

The biggest problem is runaway orders. An automatic scheduler that is not well constrained might flood you now with orders you won't have to fill for weeks, months, or even years. Which means a LOT of processing to find orders that are currently due.

One man's opinion: You would do better to have a procedure that you run daily to check for pending orders, and have it check the "recurrence orders" first to populate the next installment of those orders at that time. Because, you see, it doesn't matter what orders are in the system until you actually CHECK for them. (Sounds like the old "tree falling in the forest when no one is around" conundrum, right?)

 

[ebs17]

DateAdd Function - Microsoft Support

support.microsoft.com

For series of appointments such as orders, I would use a table as the basis, which has the arguments of the DateAdd function as field contents, plus some information about validity, creation date, foreign key to customer, product, etc.
Using a simple table of numbers, you can then use the information number to break this down into individual appointments in a query. A query makes a lot of sense because the order is still somewhat virtual before it is actually executed. Especially with longer periods of time, a plan can be thrown overboard by realities.

With a filter of the dates of the above query to today, you can check and implement your currently pending deliveries (technically append query of these data records in the delivery table). Only then do concrete consequences such as payment obligations begin.

my file is too large to upload here

In other words, you're just looking for ideas. Of course you want to do the implementation yourself and personally - which I think is good.

 

[miked18]

Looks like you actually have two separate schedules to worry about. You have the planning schedule which looks to the future to see what you need to plant NOW and you have the delivery schedule which finds the orders that you need to deliver this week. There is no reason to combine these two functions into a single process even when they use the same row in a table.

The first process has to examine known orders that have a future end date and given the growing time, make a schedule for how much you need to plant at your next planting. Obviously, if you allow new customers to receive immediate deliveries, you have to have built into your grow plan an estimated amount to satisfy "current" and possibly short term needs as well as the future needs.

This second process takes the active orders and figures out, based on the frequency, what has to be delivered this week and creates an invoice and prints a picking/delivery slip. You NEVER actually add future invoices to the table. They get added at the time you are going to deliver something.

The order entry system should have a way to add an end date. Orders can be open ended or have a fixed duration. You use the end date as well as the start date in your planting schedule process.

Anyone for hire on this site My head is spinning. I appreciate all of the feedback from everyone, thank you. So right now I have a google doc doing this. it is getting messy and is so slow to do calculations.... In the doc, I can select say 2week sub for Customer A for an order on Week A. the following week (Week B) it would Place a "0" in the quantity cell and show me the last date they ordered in another cell. <-- just for reference. So, with access, I want to replicate that in a way. I am having trouble figuring out the code to do so. I am assuming I should have an append query enter the order repeatedly for each date in the subscription, so every 2 weeks, in the same tblORDERS. ?? that's problem 1, problem 2 is after I set that up, how to I make changes to that if say in 2 months they want to move to weekly orders or once every 4 weeks? I know it is bad for to ask for actual coding, but I in a rut and not sure how to implement this.