Warehouse Inventory Project

[ahmad_rmh]

What is the best way to add time stamp in a form property default value or in a table default value for the record saving purpose.

Some uses in tables and some uses in forms. Which way is best.

 

[Minty]

Use a Default Value of Now().

If you put in at a table level it will automatically be set wherever you enter data. You can display it on the form, but lock the control, that way no one can edit it to mess things up.

 

[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.

 

[Pat Hartman]

It's mean that I have to add date and time stamp on each record on the time of saving record.

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.

 

[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.

 

[Pat Hartman]

I'm hoping we're not talking about a system that keeps a running sum. That would certainly preclude entering transactions out of order.

There is a difference between doing manual entries for inventory movement and having other processes create them. Such as a picking process, a shipping process, a receiving process. I got the impression that these processes would not exist but that the inventory movement transactions would be entered manually. That means you might be entering yesterday's transactions today so although you would use Date() as the default date for the transaction, you might need to override it and use yesterday's date. In any case, most tables should have an updateDT field that is populated in the Form's BeforeUpdate event and this should include time. You might also want a CreateDT field if it is important to know when a record was initially added and also possibly and UpdateBy field if you don't want to make a detailed logging process but just keep track of who made the last change and when.

 

[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=======

 

[Pat Hartman]

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

Good for you. CJ is off the hook.

 

[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...