Need Help With OnOrder Table

[1eye1vision]

Hi all, haven't been on in a while but really need help with a part of my DB, i have a table on which orders are placed, once the user prints the order form the table is cleared. However what i would like to do is duplicate this order into another table with an Order Ref Number,and then using a form, once the stock is received allow the user to update what is in stock by placing a tick box next to the items this will then update the record with what was ordered and then remove that item from the 'OnOrder' Table leaving only what's left to arrive on the table.
I understand that this could be complicated but really need help on this as my DB is nearly finished and this is the only part left to conquer.

Cheers in advance

 

[neileg]

It would be better to leave all the records in one table and have a status field to show what is on order and what has been received. Moving data from one table to another is not good design. If you really must do this, you need an append query to add the record to your second table followed by a delete query to remove it from the first.

 

[1eye1vision]

OnOrder Table

Hi Neileg,
I understand it's not good practice to move data from one table to another, however i think you misunderstood, the data isn't actually being moved, What i want is to write to 2 tables at the same time. 1 table would then be cleared at the time of exiting from the form and the other would hold the order until such a time that it is delivered.
Then by selecting a check box next to each item this would be removed depending on what had been delivered, as we all know not all items are delivered at the same time.

Regards
1E1V

 

[neileg]

So table 1 is a temporary table? Since you can't actually write to two tables at once:

you need an append query to add the record to your second table followed by a delete query to remove it from the first.

 

[1eye1vision]

2 Tables

I thought by opening 2 recordsets you could write to 2 tables, the table one isn't a temporary table it exists for the purpose of ordering stock, as said before once the order has been placed then then table is cleared. Don't know if this is the easiest way but that's how it's done at present.


Regards
1E1V

 

[1eye1vision]

Can Anyone offer any help on this problem.
I'm going stir crazy with this and don't know how much more i can do!!

Regards 1E1V

 

[elbweb]

my understanding of what you want is this:

you have a table of orders, you want to be able to input one to the database, print out an invoice or something similar

then you want something to track when this order was recieved, a checkbox that very simply says that this order was recieved

after that you want to be able to call back all orders that were never recieved (never got the check box)

is this right on what you want?

 

[1eye1vision]

Sort of,
I have a table already on which the orders are created, however when the order is printed this table is then cleared to allow for new orders.
What i would like is a second table, on this the order would be created with a order ref no.
Then using a separate form maybe called 'Items on Order' this would show all orders and because each order could have multiple items, each order would show what was ordered, next to each item there would be a check box so i can then tick each item as its received.
One thing i forgot to mention was that once the item is checked the quantity that has arrived would then need to update what is in stock.
Sounds complicated even to me!

Cheers
Sorry for the delay in response problem with network

 

[elbweb]

what im basically saying is why do there need to be more then one table?

everything points to there being one table, you create a record, when you do that, it makes the order ID for you, and then you print that report out, and then you later come back and update the record with the fact that it was recieved, and how much was recieved.

 

[neileg]

Few points:
1) The standard design approach for orders is to have two tables, one is the header data (supplier details, delivery address, date,order number etc) and the second holds the detailed lines for the order with the item details and quantities.
2) I don't understand why you are insisting on creating the order in one table and then adding it to another.
3) If you must do this move, why don't you like my advice on using two queries? You seem to want to do this in VBA which is just adding complexity for no value.
4) The checkbox approach to goods receipting won't cope with part deliveries. What happens if you order ten units and you get 5 delivered today and 5 delivered next week?