Subform data delete?Possible?and so how?

[xtrareal22]

HElP please, hi i have a reorder form which enters data into the reorder table.On the form i hve subform based on a query which shows which item have to be reorderd.What should i do so that after the values from the subform has been inputted into the field and saved when the button is clicked that the values in the subform get deleted??

[ItsMe]

Forms and subforms use recordsets to display data. If you already have the criteria to query the data and display it in a subform, why do you require the User to enter the information into the main form? It sounds as though the act of running an action query to update the info in the subform could do the job of entering the info in the main form.

To answer your question, use an action query or loop through the records using DAO. This same process could possibly enter the necessary data directly into your Reorder table.

[orange]

Requery the data to get the new values. If the items in the subform (query) do not now have to be reordered ( eg. don't meet the reorder criteria) should not be selected for presentation.
Perhaps I have misunderstood, if so, please provide more info.

[June7]

Possibly your data structure needs some modification. It should rarely be required to delete records. A field in products table should indicate 'reorder level' for each product. Then query or code should be executed that calculates the balance on hand for each product (sum of purchases minus sum of sales). If the balance is less than the 'reorder level' (or within certain percentage) the product should show up on a list of items to reorder. Once order is placed, refresh the query and the product should no longer be on the list.

[xtrareal22]

Possibly your data structure needs some modification. It should rarely be required to delete records. A field in products table should indicate 'reorder level' for each product. Then query or code should be executed that calculates the balance on hand for each product (sum of purchases minus sum of sales). If the balance is less than the 'reorder level' (or within certain percentage) the product should show up on a list of items to reorder. Once order is placed, refresh the query and the product should no longer be on the list.

i did all those things.i have a reorder level and list of items to reorder based on a query like you have described.i open my order form to reorder an item but after reordering and saving my query still displays that i have to reorder the item again even after i refresh.what do i do?Thanks

[orange]

When you do a ReOrder, do you change a field somewhere that indicates 'this product was reordered on Date X' or some other indicator.
What exactly is the logic associated with your

a) REOrder process
b) tables after the ReOrder event and before your Requery?

[June7]

Post your data structure and the query statement. If you want to provide db, follow instructions at bottom of my post.

[xtrareal22]

Here it is.

[orange]

Do you have any info re my post #6? I'm asking for the logic you have or intended to have in your code.

[xtrareal22]

i am not sure this is what you want to know but i do not have a field somewhere that indicates 'this product was reordered on Date X' but i have date of reorder in my reorder form basically i have a reorder table and with its own form.so when an item is to be sold the sales form is used.If stock left after a sale is below the reorder level then i have a macro that displays a message box and modal dialog form asking if i want to reorder item now.On yes opens up the reorder form and this form has a sub form displaying a query of items that need reordering.Then on click of my combo boxes,it displays the name of the things to be ordered.What i want is after i have selected the items to be reordered and saved i want the query to not show that the item needs reordering.?

[orange]

I looked at your database. It seems you may need tables for Customer and Supplier, but I don't know the scope of your business.

You may get some ideas from these IBM articles --depends on how detailed/specific/deep you want to go.
http://office.microsoft.com/en-ca/ex...001124602.aspx

http://pic.dhe.ibm.com/infocenter/sc...g_Details.html

I am attaching a very generic Customer-Order-Product model for reference. It does not deal with ReOrder.

[xtrareal22]

i am trying do this whole thing for a cafe.i did have a supplier table before but i felt like i did not needed it because all items are bought from one supermarket which is in close proximity from the cafe.i did not want a customer table because different people come to eat everyday so i did not feel like it is necessary.What do you think?

[orange]

i have a macro that displays a message box and modal dialog form asking if i want to reorder item now.On yes opens up the reorder form and this form has a sub form displaying a query of items that need reordering.Then on click of my combo boxes,it displays the name of the things to be ordered.What i want is after i have selected the items to be reordered and saved i want the query to not show that the item needs reordering.?

If you don't have some way to indicate this item has been reordered, then when you issue your requery --Nothing has changed so the same numbers appear.

Somewhere, related to the item in question, you must indicate a Reorder was done for X number of this product. And your query will have to take the fact that a reorder has occurred into account, in order to remove this product from items requiring reorder.

Now technically, a Reorder request has been made for X items, but since they have not yet been received, they are not counted as In Inventory. Ideally you will need to track the reorder, and receive it,then add the count X to your inventory of that Item.

This gets trickier if the reorder is sent to supplier, but they do not have X to send you -- you now have some quantity on BackOrder.

Good luck.

[orange]

Re your post #12 -- OK so Customer and Supplier are not in scope for your database. That's fine.
I recommend you don't have embedded spaces in your field names.

I don't understand your relationships window

[xtrareal22]

Sales and stock are both linked by the stock ID field.Every item will be assigned a reorder ID that will be used to recorder the same item again.So the reorder table and stock table are both linked by the reorder ID.My New order table is not linked to any of the tables because it is were new items that are not in the stock table are being ordered so there is not any field that links to the the other tables.