Automatically generating order data?

[Dougalsworld]

Hi all,

I'm currently working on a simple inventory system that i keep trying to improve and making it much less simple

In my product table I have a min and max level. What I'm trying to achieve is to set up a form where i can select a Supplier, then hit a button (or another form of trigger) and it comes back with all the products for that supplier where we are low on stock ie

Product - Supplier - On Hand - Min - Max
Prod1 - Supp1 - 2 - 2 - 4
Prod2 - Supp1 - 4 - 2 - 4
Prod3 - Supp1 - 1 - 2 - 4

So if on my form I selected supplier Supp1 it would automatically fill in order detail for Prod1 & Prod 3 for 2 & 3 units respectively.

Now I'm pretty sure i could do this as a report as the formulas are relatively simple, however i want to pull the data on a form so it creates an actual order with order id etc.

Is there a way i can do this?

Any help would be appreciated, cheers

 

[The_Doc_Man]

Underneath the form's button's OnClick code, open a recordset to a New Order table. Open a recordset to the query that shows you what is at or below your Min Stock level. It is OK if the query is keyed to the supplier's ID, name, or other key.

Once for each record in the Min Stock query, generate an order record that includes the order quantity, supplier ID, item ID, etc.

In order to do this, look in the help files for Recordsets and learn how to use them.

Alternative, less advanced: Underlying the OnClick button, run an Append query that adds records to your New Orders table, taking data from the Min Stock query and the selected Supplier ID.

Once you have unfilled entries in the New Order table, you can process them and move them to an Order History table. Or you can just mark them as "Order Sent To Supplier" including a Purchase Order number or whatever.

 

[ChrisGow]

I need to get somethign like this done as well..... I have tried to find a way to automatically do it with no luck....I have gotten to the point where I can get all the information I need into a form through queries....the only problem is that to start my order quantity at 0 i need to set it to 0 and when I get into my form I cant change that value even tho the form is taking the information from a table....Am I jsut a moron and am missing somethign or what....If you have any idea what Im trying to say could you help me out...might even help out dougalsworld assuming he still has this problem