Tricky calculation to perform

[leeli67]

So maybe the first 20 orders i put in will be repeated as and when I need them, thank you , I will also look up append query also.

[leeli67]

1 quick one JZ
1 . will i have to make a new order form to incorporate the standing order.

[jzwp11]

No, the standing order is just a reference to a regular order just to get the list of items. You will need to create a form where you would select the specific order number of a customer that you want to use as the standing order, but you do not need a separate order form where you would list the items.

[leeli67]

Morning , JZW , can you think of a way I could display all the weeks orders for 1 customer that is editable ?

[jzwp11]

One way is to have an unbound form where you enter your criteria (customer and date range), you would use that as a filter to open another form to that specific information. The second form would be based on a query. Now, as to it being editable that is possible to a point (it will depend on the types & number of joins you have in the query). Do you know what fields you want to be able to edit?

[leeli67]

it would just be changing "Quantity"of standing orders for the next day, i.e customer A wants 20 not 22 rolls the next day

I also need to display it like this


________Mon tue wed thur fri
product1 5 5 5 10 5
product2 10 10 10 10 10
product3 7 6 5 4 4
etc


its all bunched up but basically products going down and days of the week going across with quantity filling the columns
any ideas

[jzwp11]

What you would do there is to run the append query (you could still use the unbound form to select the customer and the standing order). After the append query runs, you would open your regular order form to the new record and then you would be able to amend the quantities as necessary for that order.

[leeli67]

Wow , I think im trying to run before I can walk , your last few post have got me scratching my head , only because i'm no way the standard that you obviously are. I order access 2007 for dummies the other day it should be here soon. meanwhile I'll carry on filling in orders , I have about 500 lines of standing orders to fill in which will be the first 500 or so orders in the order table, then ill delete the order form ive been using and i'll go back to where you described an order form with lines of orders already filled, still not sure about this append query , but thanks a million for all your help.

Lee

[leeli67]

also getting a book on visual basic for applications

[jzwp11]

There would be no need to delete the form you are using now especially if it is working for you. You can use that form even using the approach I mentioned for the standing order. If you want to post your database with some sample data in it, then we can starting creating the unbound form & append query I mentioned. Unless, of course, you want to give it a shot on your own once you have a chance to read up on things when your books arrive.

[leeli67]

Hi Jzw , That would be good. Even if you could just make sure im not wasting my time with the orders ive been putting in. So far I have entered the orders for a couple of customers Mon - Friday, which you will see in the [tblOrders] . theres a jump in orderID numbers from 5 to 10 , I had to delete some ,if you look at customer Olivers Butchers , that is a typical weeks order for a customer. Would be more than happy to hear your views.

Lee

[jzwp11]

I'll take a look at the database tomorrow.

[jzwp11]

I see that you have a standing order for each day of the week for a particular customer. When do the new orders typically get created? Do you do them all at one time for say the following week?

[leeli67]

from what I have witnessed in the office , when the daily delivery driver returns from the round of customers he sometimes has sheets from the customers that change their order for the next day. So some way of calling up the customer to see the standing order for the next day or an editable weekly view. is what im trying to achieve, but then once edited , obviously saved as an order.

Lee

[jzwp11]

In the attached database, I created a form (frmCreateNewOrderFromStandingOrders) that has the code behind the button to create a new order based on the standing order. I did put some comments in the code to explain what each section is doing.

I also created another form (frmCustomer) where you can define standing orders for your customers rather than doing it directly in the table.