Purchase Order / stock DB

[MrMcFestoe]

Iam starting to make a datbase from ordering and stock, so far i have two tables one with all the stock and another with the suppliers info, i need to make a form which i can do but this has got me,

I need to make a form which will have the suppliers details on the top (done this) then a part which i can select the item's i need to order, i thought of a combo box to select the part but i need a list of what has been selected this is the problem, how do you do that. Also i need to keep a record of the order and details of the order.

Thanks

 

[LogicalmanUS]

MrMcFestoe,

You have a number of solutions here, but it will depend on a number of issues.

Do you need to select the item required and then enter the number of those items, or is the number of automatic?

From what you then say, it looks like you want to make a number ordered items prioe to saving the order, from a single supplier.

Is this database being used from a single location? If so, then the use of a static part order table is feasible, e.g., as each item will have it's own unique ItemID, then you could make the form thus:

The form is created as a single form, with a subform.
In the header of the form are 2 comboBoxes, cmbSuppliers and cmbItems, a text box for number of items and an ADD button.

Upon a supplier selection, the second combobox is populated with those items belonging to that supplier. The user then enters the number of items requires and clicks the ADD button.

Upon the button click, the item ID and number required are entered into the part order table.

The subform is a Continuous form reflecting the contents of the Part Order table. It will be read only (Data entry and add turned off).

On the foot of the main form is another button Order Complete, which, when pressed processes the order in whichever way you require.

Once an order is processed, the Part Order Table empties and the process can start over again.

Just a few thoughts, if it is what you require, then I can assist you in building the itms and logic code.

Logicalman

 

[MrMcFestoe]

Logicalman

Thanks for the reply, it sounds right, will items pick be shown in the subform and is there a limit to how many items appear, can the order then be saved in a table so it can be viewed at any time ?

thanks

 

[MrMcFestoe]

Logicalman

Sorry posted to hasty, i do need to select the number items to be order.

 

[LogicalmanUS]

MrMcFestoe,

The answers to your questions above are:

will items pick be shown in the subform - Yes

is there a limit to how many items appear - No

can the order then be saved in a table so it can be viewed at any time - yes


The first twp are covered in the original reply.
The last question is dependant on what you want to do with the order. My suggestion would be that you have a table (tblProcessedOrders) that has the same columns as the part order table, but with an additional column set to default DATE(). Once an order is processed the part order table is emptied into the tblProcessedOrders table, where you then have a permanent record of the order.

If you really want to get professional regarding the ordering, then you should create a header table for the tblProcessedOrders table, that will have all the common data the ordered items share, e.g. time/date of order, who placed the order, if you have current prices stored, then you can put the total cost of the order, and a comment field. This table also has an autonumber column, and that number takes the place of the date column in the part order table as a Foreign Key.

If you wish to continue the design away from the forum, then please reply with your email address and I can guide you that way.

Logicalman