Solved Inventory Count in Access

[theinviter]

Dears;
i need help,
i have created Inventory Database, as i want to track the balance after filed update,
i tried the below code but the issue is that, it does not include the current record as it calculate previous record.

Private Sub Qnty_Out_AfterUpdate()
[Final Balance] = (Nz(DSum("[Qnty IN]", "[Table1 Query1]", "[Linked _ID] =" & Me.Linked_ID), 0) + Nz(DSum("[Balance1]", "[Table1 Query1]", "[Linked _ID] =" & Me.Linked_ID), 0)) - Nz(DSum("[Qnty Out]", "[Table1 Query1]", "[Linked _ID] =" & Me.Linked_ID), 0)

is there any way to do it instead of Dsum or any other idea.
Or how t calculate immediately after filed update.

thanks

 

[arnelgp]

it does not "include" the current record because you have Yet to save it.
if you want to include it:


[Final Balance] = (Nz(DSum("[Qnty IN]", "[Table1 Query1]", "[Linked _ID] =" & Me.Linked_ID), 0) + Nz(DSum("[Balance1]", "[Table1 Query1]", "[Linked _ID] =" & Me.Linked_ID), 0)) - Nz(DSum("[Qnty Out]", "[Table1 Query1]", "[Linked _ID] =" & Me.Linked_ID), 0) - IIf(Me.NewRecord, Val(Me!Qnty_Out & ""), 0)

i am not sure why you have Balance1 there, is this a startup quantity?

 

[theinviter]

it does not "include" the current record because you have Yet to save it.
if you want to include it:


[Final Balance] = (Nz(DSum("[Qnty IN]", "[Table1 Query1]", "[Linked _ID] =" & Me.Linked_ID), 0) + Nz(DSum("[Balance1]", "[Table1 Query1]", "[Linked _ID] =" & Me.Linked_ID), 0)) - Nz(DSum("[Qnty Out]", "[Table1 Query1]", "[Linked _ID] =" & Me.Linked_ID), 0) - IIf(Me.NewRecord, Val(Me!Qnty_Out & ""), 0)

i am not sure why you have Balance1 there, is this a startup quantity?

Balance1 = startup quantity

 

[theinviter]

is there a way t do make ms access to count from last record based on item ID, instead of counting all column.

thanks

 

[jdraw]

StockCount =PreviousStockCount +Purchases - Sales

 

[plog]

[Qnty IN]", "[Table1 Query1]

1. You shouldn't use spaces in field nor table names. Makes coding/querying that more tricky.

2. Is [Table1 Query1] the actual name of it or did you genericize it for us? Is it a table or a query?

3. You shouldn't have [Qnty IN] and [Qnty Out] fields. You should simply have one field for the quantity. Use negative numbers for quantity out and all you have to do then is one DSUM to get your value instead of summing DSums.

 

[theinviter]

1. You shouldn't use spaces in field nor table names. Makes coding/querying that more tricky.

2. Is [Table1 Query1] the actual name of it or did you genericize it for us? Is it a table or a query?

3. You shouldn't have [Qnty IN] and [Qnty Out] fields. You should simply have one field for the quantity. Use negative numbers for quantity out and all you have to do then is one DSUM to get your value instead of summing DSums.

Yes actual name
But how to do it if one field. As I want to get the balance from last record. As user add new value then will be add with last record in new field.

 

[plog]

You shouldn't store the balance with each record. You should calculate the balance when you need it.

You store your credits and debits in one quantity field, then sum all the records' quantity values to get your current balance.

 

[CJ_London]

is there a way t do make ms access to count from last record based on item ID, instead of counting all column.

Access is not excel so short answer is No unless you are calculating and storing the balance and have a means of identifying ‘last’

and calculating and storing balances is fraught with problems around making sure it is maintained correctly when a record is entered in the wrong order, a qty is changed. A record deleted, adjustments to name just a few

 

[jdraw]

Perhaps you should show us your table structure so we are all talking about the same thing.

But how to do it if one field.

 

[theinviter]

Balance1 = startup quantity

 

[theinviter]

please find attached file.

 

[CJ_London]

looking at your data you have no means of identifying the previous record - you can't use your date field because it's not populated half the time and where it is you have multiple records with the same date.

You can't use the ID because you have records where there is no quantity in or out but mysteriously the final balance changes because you have three startup quantities - how many times can a product start?

And say you update the missing dates so record 36 now has a date of 25th July? that means that record 35 is wrong. and if the date entered was 27th July, record 41 is wrong.

If you are going to say - 'doesn't matter, I'm only interested in the latest position', then you have just destroyed your requirement for maintaining a calculated ongoing balance.

 

[theinviter]

OK what is the best option. Can you guide me.

 

[jdraw]

Review Allen Browne AppInventory

 

[theinviter]

But can I l make a drop list for In and out. So the user will first select frop drop list. Then add the value in one field. If selected out then value will be negative but if in then positive.
Is there any way to do this.

 

[CJ_London]

you have a transactiontype table with name of the transaction the user sees and a multiplier field which is set to 1 or -1 depending on the 'direction' of the transaction.

There is a similar thread to this ongoing - but see the example database I provided in post #20

 

[theinviter]

you have a transactiontype table with name of the transaction the user sees and a multiplier field which is set to 1 or -1 depending on the 'direction' of the transaction.

There is a similar thread to this ongoing - but see the example database I provided in post #20

you have a transactiontype table with name of the transaction the user sees and a multiplier field which is set to 1 or -1 depending on the 'direction' of the transaction.

There is a similar thread to this ongoing - but see the example database I provided in post #20

Can you share the link of the example dstabase

 

[CJ_London]

sorry - forgot to copy/paste it

Warehouse Inventory Project

Dear Experts, I have made a QOH value in inventory adjustment form (dlookup function) to see how much QOH for inventory adjustment. But the issue is that QOH is dynamically changing as the QOH gets changed. How to make static as the record saved?

www.access-programmers.co.uk

 

[arnelgp]

you can experiment on this free access databases:
Microsoft Access | Free Source Code Projects and Tutorials (sourcecodester.com)