reoccurring orders (1 Viewer)

[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.

 

[Pat Hartman]

review my suggestion in your other thread.

There are multiple solutions for initiating the fulfillment action. Another solution is to have your opening form run the process the first time you open the database for the week. Using this method, you would keep a table and log the date the process runs so you only do it once per week. If you want to handle this separately for homes vs businesses, you can run two procedures. One on monday for the home delivery and one on tuesday for businesses.

 

[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.

 

[Pat Hartman]

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.

 

[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.

 

[Pat Hartman]

Access and Excel are different tools and you would not use the same techniques with each. With Access, you would never create a process that has to be run separately for each customer. As we have already mentioned, you would not be inserting future fulfillment records. You create only the current one. You create the delivery record for THIS period. That way if the schedule changes or quantity changes, you don't have to change any future records.

In order to write a program to do something, you MUST be able to write it first in a natural language. I've been developing applications since probably before you were born and even I am not above writing pseudo-code to outline a process before I write the code. Start by doing that. Define the tables you need and then think about how you get from a to b. Even if you end up having to pay someone to do this for you, YOU still need to be able to define the process step by step in sufficient detail for a programmer who is not a subject matter expert to write the code for you. We will help you to refine your process.