Warehouse Inventory Project

[ahmad_rmh]

In a form view, its a good idea but sometimes users makes mistakes and you have to edit the record then ...

 

[Minty]

That shouldn't affect when the Data is entered though? Maybe the quantity or the part number, but not the When?
If it's a few days later then they should enter a correction transaction, not go back and fix an old one.

 

[ahmad_rmh]

That shouldn't affect when the Data is entered though? Maybe the quantity or the part number, but not the When?
If it's a few days later then they should enter a correction transaction, not go back and fix an old one.

But if you go on real sense, the same invoice should have to be edited and even i have not seen any software showing this type of correction transaction.

 

[ahmad_rmh]

But I am using Quickbooks, they have made an audit trail report on users, at what time, record entered, edited or deleted by the user.

 

[ahmad_rmh]

Normally, you would not include time as part of the transaction date. The transaction date can default to today which is Date() but the user needs to override that value. Including a time (unless you actually need to track transfers by time which I doubt), just causes issues later.

If you want to use the informationto mark each record with when it was updated, use the Form's BeforeUpdate event.

Me.UpdateDate = Now()

Or, if all you care about is when the record is created, Use Now() as the default value for the CreateDT field.

Thanks Pat, I'll try this and will let you know.

 

[Minty]

You do need the transaction time, if you perform a stock check at say 11.30 AM, then have later transactions in the day, you need the time to correctly work out the current stock.

If they shut down for a day's stock taking or embargo stock checked items for the day then it's not an issue.

 

[ahmad_rmh]

You do need the transaction time, if you perform a stock check at say 11.30 AM, then have later transactions in the day, you need the time to correctly work out the current stock.

If they shut down for a day's stock taking or embargo stock checked items for the day then it's not an issue.

Both scenarios are correct, i will try both of them to improve my skills

 

[CJ_London]

I would add that a timestamp field is useful to have so you know when a record was entered. This would not be the stocktake time, but it can be useful when trying to sort out the order of real events v the order as entered into the db.

Using Quickbooks (or any accounting app) as a basis for developing a stock management process is not a good model to use. The reason is that accounting systems do not require the same detail of transaction timing - a date is usually sufficient - it doesn't care what order transactions occur during a day as it is only really interested in balances at the end of a day (but doesn't stop it having a timestamp field anyway).

However they do have a high level of what I would call 'legality' - once a transaction has been entered it cannot be changed, if it needs correcting you do a correcting entry (bit like your stock adjustment) and often there will be checks and balances preventing a user from pre dating a transaction.

This latter point you will need to take into account - once you have entered your stock in hand value and created any adjustment quantity - you need a check on subsequent transactions that the user cannot enter a date/time before the date/time of when the stock in hand was determined - and that might vary from item to item.

 

[ahmad_rmh]

Hi CJ, how are you...

You are right, the timestamp is most important factor in recording the transactions.

In any organisation, once the stock take finished and closed after the confirmation from puchase department, it is not allowed to adjust at the back date and even some software have checks administrator passwords protected after closing the month or year because of audit issue.

Stock Adjustment ==>
Why the need of stock adjustment?

If the organisation have bulk of transactions to warehouses and sometimes the labours are sending more than than the GTN and the main warehouse is not getting any feedback due to some lack of communication then ultimately you have to adjust the stock as per the difference.

 

[CJ_London]

I'm hoping we're not talking about a system that keeps a running sum.

Think I've convinced ahmad of that.

Such as a picking process, a shipping process, a receiving process.

those aren't allowed for in the app at present, but easy enough to add in if required.

A lot depends on how the app is used - it's one thing for a user in a back office somewhere entering data

Spoiler

(I remember years ago when computers and apps were very basic - the (mainframe) app we used used date/time of entry to determine order - so my office would receive bits of paper throughout the day. These would be held until the following day when they would be sorted into the 'right' order and input one at a time. And was then reconciled with a manual system held in the stock office)

and quite another if the app is used on the warehouse floor for generating transfer dockets and the like as they are required. In which case a user can do the stocktake from their laptop/tablet knowing all the 'paperwork' is up to date.

But mistakes can still happen........

 

[ahmad_rmh]

Kindly suggest for the followng as per the attached samples

a. I have made adjustment form. I don't want to see the Qtyonhand and PhysicalQtyAdj columns after saving the record.

b. These should have to be available at the time of adjustment of new record.

 

[ahmad_rmh]

Kindly suggest for the followng as per the attached samples

a. I have made adjustment form. I don't want to see the Qtyonhand and PhysicalQtyAdj columns after saving the record.

b. These should have to be available at the time of adjustment of new record.

=====Got Solution by myself=======

 

[ahmad_rmh]

Formula correction required:

=IIf(IsNull([AdjustmentSub].[Form]![ItemsFK]),Null,DSum("[Stock_In_Out]","TransactionsExtended","[ItemPK]=" & [AdjustmentSub].[Form]![ItemsFK]))

upto this step it is working, i want to give date reference

=IIf(IsNull([AdjustmentSub].[Form]![ItemsFK]),Null,DSum("[Stock_In_Out]","TransactionsExtended","[ItemPK]=" & [AdjustmentSub].[Form]![ItemsFK] & "And [TranDate]<= #" &[TranDate]))

HeadFormName: Adjustment
SubformName: AdjustmentSub
DataReferenceQuery: TransactionsExtended

it gives error.

suggest please.

 

[ahmad_rmh]

Formula correction required:

=IIf(IsNull([AdjustmentSub].[Form]![ItemsFK]),Null,DSum("[Stock_In_Out]","TransactionsExtended","[ItemPK]=" & [AdjustmentSub].[Form]![ItemsFK]))

upto this step it is working, i want to give date reference

=IIf(IsNull([AdjustmentSub].[Form]![ItemsFK]),Null,DSum("[Stock_In_Out]","TransactionsExtended","[ItemPK]=" & [AdjustmentSub].[Form]![ItemsFK] & "And [TranDate]<= #" &[TranDate]))

HeadFormName: Adjustment
SubformName: AdjustmentSub
DataReferenceQuery: TransactionsExtended

it gives error.

suggest please.

=====got the solution by myself===============

 

[ahmad_rmh]

=====Got Solution by myself=======

Good for you. CJ is off the hook.

thanks Pat...

 

[ahmad_rmh]

kindly correct the formula for the reference of date in sub form.

=IIf(IsNull([AdjustmentSub].[Form]![ItemsFK]),Null,DSum("[Stock_In_Out]","TransactionsExtended","[ItemPK]=" & [AdjustmentSub].[Form]![ItemsFK] & "And [TranDate]<= #" & [Forms]![Adjustment].[Form]![TranDate] & "#"))

HeadFormName: Adjustment
SubformName: AdjustmentSub
DataReferenceQuery: TransactionsExtended

 

[jdraw]

How about
a) post a copy of the database with instructions to show the issue, or
b) try a few variations in syntax and report back eg,, a specific ItemPK until you get what you need/want?

 

[Gasman]

Do you actually need # when referring to form controls and they are actually a date format?

 

[ahmad_rmh]

Its actually a shortdate in the table. May be there is a conrol reference issue with the main form or something else.

 

[ahmad_rmh]

I am giving reference in the conrol source and it is an unbound field in subform.