Using Recordset to edit a table

[jazee007]

Hi there,

I must point out first that I am a novice in VBA code.

My problem is that I need help to write the VBA code. My problem is as follows:

1) I have a table named "Barcode_of_goods"
The field that I will use in the table are:
i) Barcode_goods (Text Field)
ii) Minimum ReOrder Quantity (Number)
iii) OrderingStatus (Text)

2) I Have a query named "Actual Stock Report"
The field I will use in the query are:
i) In Hand
ii) Barcode_of_goods

3) I have a form named: Customers Quotation View and Edi + a
sub form named "Sales Subform1"
The field I will use is:
i) Barcode_of_goods
ii) Qty_Sales

I have a button in the the form Customers Quotation View and Edi.
On click I need it to do the following:

Run through the record in the Subform one be one, check the field "Barcode_of_goods" with the "Barcode_of_goods" in the query "Actual Stock Report" and see if "In Hand" minus "Qty_Sales" is less than "Minimum ReOrder Quantity" in the table "Barcode_of_Goods" update "OrderingStatus" to "Re-Order" else do nothing.

Please can someone help me to write this.

Thank you.

[Seth Schrock]

It would actually be easier to create an UPDATE query and run that once the transaction is done. First create a regular query and join your Barcode_of_goods table to the Actual Stock Report query and put a WHERE clause that filters for [In Hand] <= [Minimum ReOrder Quantity]. Run the query to make sure the results are what you are looking for and then change the query to an UPDATE query and set your OrderingStatus field to "ReOrder". This method will actually be faster to create and also to execute.

[jazee007]

I have done as you said. It works like a charm. It was so easy that I complicated the situation completely.


Thank you. Great job.

[Actual Stock Report]![In Hand]<=[Barcode_Of_Goods]![Minimum Reorder Quantity]