What is wrong with this code??

[Desperate]

What I am trying to do is material allocation. There are structures.Each structure needs different types of material to be fabricated. This info is in a table as below.

Structure ID--Material Code--Required Qty--Status
ST1 -------------MAT2---------- 3---- ---AVAILABLE
ST1 -------------MAT3-----------3-------AVAILABLE
ST2--------------MAT1-----------5------ NOT AVAILABLE
ST3--------------MAT3-----------2------- NOT AVAILABLE

And I have a query holds the stock qty

Material Code----Stock Qty
MAT1------------ 4
MAT2------------ 2
MAT3------------4

Below code should check each row in Structure table, if quantity is less than or equal to stock qty, Status should be AVAILABLE and it should deduct the required quantity from stock quantity to calculate next row correctly. But is not working. It calculates First items correctly till they get NOT AVAILABLE then it says for all items NOT AVAILABLE even they are AVAILABLE. I guess recordset may stuck somewhere?

Private Sub RunAllocation_Click()

Dim db As DAO.Database
Dim STOCK As DAO.Recordset
Dim WP As DAO.Recordset
Dim VirtualStock As Double

Set db = CurrentDb()
Set STOCK = db.OpenRecordset("InventoryTotalQ") 'STOCK QUERY
Set WP = db.OpenRecordset("WorkPack") 'STRUCTURE TABLE

VirtualStock = STOCK!Stock_Qty 'VIRTUAL STOCK TO DEDUCT REQUIRED FROM STOCK QUANTITY

If STOCK.RecordCount = 0 Then Exit Sub

STOCK.MoveFirst

Do Until STOCK.EOF

If WP.RecordCount = 0 Then Exit Sub

WP.MoveFirst

Do Until WP.EOF

If STOCK!Part_No = WP!Part_No Then 'IF MATERIAL EXIST IN STOCK START TO CHECK QUANTITY

If VirtualStock >= WP!Required Then 'IF STOCK QTY IS BIGGER THAN REQUIERD QTY.

WP.Edit
WP![Status] = "AVAILABLE"
WP.UPDATE

VirtualStock = VirtualStock - WP!Required 'REDUCE REQUIRED QTY. FROM STOCK TO CALCULATE NEXT ROW AS PER CORRECT VALUES

Else

WP.Edit
WP![Status] = "NOT AVAILABLE"
WP.UPDATE

End If

End If

WP.MoveNext

Loop

STOCK.MoveNext

Loop

STOCK.Close
WP.Close
Set STOCK = Nothing
Set WP = Nothing
Set db = Nothing


End Sub

 

[Poppa Smurf]

You may need to cycle through your WP and then your Stock.

Do Until WP.EOF
STOCK.MoveFirst

Do Until STOCK.EOF

If STOCK!Part_No = WP!Part_No

etc, etc, etc,

For each record in WP you will need to move back to the first record in your Stock so that the new totals will be used.

 

[Desperate]

Well I tried removing WP.MoveFirst as well but when I remove it strangely nothing is working.
But you are right. For sure this is related to cycling. Something I am doing wrong there.

 

[Poppa Smurf]

Post the relevant table, query and code etc. and I will have a look at your question.

 

[vbaInet]

You could simply use a subquery or a DLookup() function for this.

Subquery:

Status: IIf([Required Qty] <= (SELECT StockQty FROM Materials WHERE [Material Code] = [Structure ID]), "Available", "Not Available")

Put that into your query (i.e. the query that has all fields from Structures).

 

[Desperate]

But I am not trying to find only availability. After it checks the quantity, stock should be reduced visually to recalculate next same items availability in a correct way.

 

[vbaInet]

I just gave you the idea. Create an alias field for that select query. Refer to the alias field and perform your deduction inside the IIF().

 

[Desperate]

Poppa Smurf I prepared small database for you. Please open StructureT, select 12DUMMY-1 from structure_NO click add to work pack. After this select 130-TP-001 click to add 5 times and click run allocation. You will see its not allocating correctly. You can watch required qty versus stock qty. from small table on the right.
Its access 2007.

 

[Poppa Smurf]

Here is an updated version of your database
1. The Run Alloc allocation code has been changed.
This code will need to be modified so that the required fields in your InventoryT table is updated with the required data when a new record is added to this table.
Inventory.AddNew
Inventory("Part_no") = STOCK!Part_No
Inventory("Quantity_Out") = WP!Required
Inventory.Update
2. The query InventoryTotalQ was changed.

 

[Desperate]

Thanks a lot Poppa. I am checking now. But actually nothing should be changed in InventoryT through this screen.Because this screen is only to show user the availability of the materials to build structures. InventoryT is getting updated through other screens as per the materials receipt and issuing. I don't have a chance to send you all database due to limitation as it has huge size.But I will try to get an idea from what you did.

 

[Poppa Smurf]

Desperate

What is the purpose of the form StructureT? I have assumed that if an part is available, then the InventoryT will need to be updated with a entry to show that required quantity for the part has been used by the form StructureT.

 

[Desperate]

Sorry for the lack of explanation.InventoryT is a table which holds material transactions.It shows what have been received to warehouse and what have been issued from the warehouse. The purpose of StructureT is to show engineer, which structures can be fabricated with the remaining materials in the warehouse. It has nothing to do with inventoryT but taking the stock_qty (stock_qty=received_qty-quantity_out) from its query.

 

[Poppa Smurf]

InventoryT is a table which holds material transactions.It shows what have been received to warehouse and what have been issued from the warehouse. The purpose of StructureT is to show engineer, which structures can be fabricated with the remaining materials in the warehouse. It has nothing to do with inventoryT but taking the stock_qty (stock_qty=received_qty-quantity_out) from its query.

So when the StructureT form opens and the Structure Number is added to Work Pack a snapshot of the material available for the part numbers of the Structure number is displayed in BomF.

Now your requirement is to update the Status field of the part number of the structure number with Available or Not Available depending on the quantity in the warehouse. If the part is available then deduct the required quantity from the Stock Quantity in the BomF. The table InventoryT can not be used to update BomF with the new Stock Quantity after a part is allocated.

 

[Desperate]

Yes exactly correct. The InventoryT can't be updated. That is why i created virtual stock (virtualstock = STOCK!Stock_Qty) And statement virtualstock = virtualstock-WP!Required is to deduct qty from stock quantity virtualy. Lets say you have Material1 Required 3 pcs. for the first structure.You have 5 pcs. in the stock. It will say available for the first structure.If there is same material again for another structure on the next row, stock qty should be deducted to 2 to show the user NON-availability (only to show no deduction). But I guess, the mistake is there.

 

[Poppa Smurf]

Here is an updated version.

I have modified my previous version with the following changes.
1. Created a table tbl_temp_inventory - this will hold a snapshot of the current inventory.
2. New queries qry_append_temp_inventory to append the inventory snapshot to the table tbl_temp_inventory. Qry_temp_inventory_available - used to provide the available quantity for qry_bomq. Qry_bomq - recordsource for form BomF.
3. On form StructureT added code to the Load event so that the table tbl_temp_inventory is deleted and is populated with a snapshot ot the Inventory. Modified code in the Run Allocation module for the new temp table.

The Inventory data remains unchanged when the Run Allocation button is clicked.

 

[Desperate]

Poppa, I just did the same on my computer with your brilliant idea! Thanks a lot for your support. I really appreciate. What I tried to do before is something like to create a dynamic recordset which I couldn't manage. But creating a temporary table also works fine.Thanks!

For the users who reads this thread; this method can be used to analyze for material allocation to any purpose like cooking recepies with the foods in your hand, building a car etc. etc..Examples can be increased based on the imagination

 

[Poppa Smurf]

I glad that the updated version provided a solution to your problem. I used new queries so that I did not interfer with your existing queries that are used elsewhere in your database.