Warehouse Inventory Project

[ahmad_rmh]

Thanks Kamayo ...

after putting option explicit back again error going on the following.

Cancel = True

 

[Kamayo Ako]

Put Option Explicit back though.

I knew you are expert sir. But possible sir you can check the attachment . If i put back the "Option explicit" the sorting has an error when i remove its working . I need your help on how to avoid that error even with Option Explicit declared.

 

[jdraw]

RE testver2rev1.accdb

I would recommend
-use Option Explicit as second row in all modules
-adjust the date checking/validation

If Not IsDate(Me.FromDate) Or Not IsDate(Me.ToDate) Then
     MsgBox "Start Date and End Date Required", vbCritical + vbOKOnly + vbDefaultButton1, "Missing Data"
     Forms![test].FromDate.SetFocus

-dim Dates as Date

You are misusing Cancel = True. That is an option on some events. Perhaps I am missing your intent.

 

[Kamayo Ako]

RE testver2rev1.accdb

I would recommend
-use Option Explicit as second row in all modules
-adjust the date checking/validation

If Not IsDate(Me.FromDate) Or Not IsDate(Me.ToDate) Then
     MsgBox "Start Date and End Date Required", vbCritical + vbOKOnly + vbDefaultButton1, "Missing Data"
     Forms![test].FromDate.SetFocus

-dim Dates as Date

You are misusing Cancel = True. That is an option on some events. Perhaps I am missing your intent.

THANK YOU SO MUCH SIR

 

[ahmad_rmh]

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?

======get solved by myself===============

 

[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

====get solved by myself=============

 

[Gasman]

These forums are here to help people.
You have had lots of help here.
Please reciprocate and post the solutions to your issues. That might help someone else in the future, with similar issues.
It is all about give and take. Do not just take.

 

[ahmad_rmh]

These forums are here to help people.
You have had lots of help here.
Please reciprocate and post the solutions to your issues. That might help someone else in the future, with similar issues.
It is all about give and take. Do not just take.

Yeah Sure,,, Why not....

 

[ahmad_rmh]

Solutions:

To find the Quantity on Hand from tables / queries control on MainForm.

MainFormName: Adjustment
SubFormName: AdjustmentSub
QueryName: TransactionsExtended

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


To find the Quantity on Hand from tables / queries control in SubForm and MainForm.


=IIf(IsNull([TransactionPK]),Null,DSum("[Stock_In_Out]","TransactionsExtended","[ItemPK]=" & [ItemsFK] & "And [TranDate]<= #" & Format([Forms]![Adjustment]![TranDate],"mm-dd-yyyy") & "#"))


===Appreciation Required Please======

 

[Gasman]

I will just say Thank You.

 

[ahmad_rmh]

Splitting the transactions table is fine (better normalisation) although it makes queries a bit more complex. There is a saying 'normalise until it hurts, denormalise until it works'

You use the same form but providing you know what type of transaction you will be doing when you open the form (perhaps based on the user profile, perhaps based on a menu the user selects from) you can set some properties such as a label caption or form back colour to indicate what type of form it is - and modify the rowsource of your combo (which I would rename as cboTranType). Personally I would put this combo at the top and hiding or disabling all the other controls. Then showing or enabling the relevant controls when a selection is made.

for your stock adjustment, there are many types but if this is to do with a stocktake adjustment you can include an additional field in tblTransactions (you could put it in tblTransactionsMain but makes things a bit more complicated with no real benefit) called say 'StockCount' with a default of null. This would be hidden in your form unless the user is doing this sort of transaction - you are using a datasheet so you would set columnhidden to true (or columnwidth to 0).

If the user needs to make this adjustment (or perhaps you have a routine for just entering the stocktake count, and not worrying about comparing values), they enter the stock count in this field and in the afterupdate of the control populate the the quantity field with the difference between actual and theoretical - code would be something like

me.quantity=me.stocktake-dsum("quantity","tblTransactions","itemsFK=" & me.itemsFK)

so say the calculated value is 10 units and the stocktake is 12 you would have

12-10=2

The benefit of storing the stockcount value is as the amount of data grows the dsum might take longer, so you calculate from the last stocktake date - simpler is a query otherwise you need to use nested dsums and dlookups.

Alternatively you can have a control on the main form to show the current stock - controlsource would be something like

=DSum("qty","tblTransactions","itemsFK=" & [tblTransactions Subform].[Form]![ItemsFK])

and in the current event of your subform you would need code

parent.nameofcurrentstockcontrol.requery

user would then need to calculate the difference themselves

or you can just include the dsum in the recordsource of the subform - you will need to use dsum, otherwise the query becomes unupdateable. But why do potentially 100's of calculations when you only need the odd one?

Kindly suggest hints as CJ mentioned in the 2nd paragraph,

I am making through Dashboard

Receipt, transfer and adjustment links on dasshboard will open the same form but with conditions of visibility.

Just pass the hints,

 

[Minty]

To keep things simple I would use the open Args property to pass a string that you use to hide/unhide/disable various controls, based on which command button you choose from the dashboard

 

[ahmad_rmh]

how to hold quantity on hand to be updated until record saved but it's changing as quantity difference shows adjustment of stock.

I have attached the screenshots.

 

[Minty]

What is the control source for QOH display box?

 

[ahmad_rmh]

QOH is a dsum function from query TransactionsExtended

=IIf(IsNull([TransactionPK]),Null,DSum("[Stock_In_Out]","TransactionsExtended","[ItemPK]=" & [ItemsFK] & "And [TranDate]<= #" & Format([Forms]![Adjustment]![TranDate],"mm-dd-yyyy") & "#"))

On PhysicalQtyAdj field, after update event for QuantityDifference has been made.

As event triggered the value in QOH shows new updated Quantity as shown in screenshots.

I want the old Quantity to be hold untill saved.

Or Is it possible to make a time interval difference between two transactions, i.e qty before adjustment and qty after adjustment on the same date but with the difference of time.

 

[Minty]

Are you saving the record in the after update event of that control?
I would have thought the Dsum won't adjust until the record is saved?

 

[gemma-the-husky]

I knew you are expert sir. But possible sir you can check the attachment . If i put back the "Option explicit" the sorting has an error when i remove its working . I need your help on how to avoid that error even with Option Explicit declared.

f you don't use Option Explicit then vba creates variables of type variant for any new variable
if you use Option Explicit then vba requires variables to be named and typed explicitly before use. Your error indicates that you have used a variable that is not specifically named and typed, and this can easily cause your program to run incorrectly. That's why it's not safe to remove Option Explicit.

Compile the program debug/compile to find all undeclared variables and other errors.

 

[ahmad_rmh]

I am working on openArgs. I have three conditions Receipt, Transfer and Adjustment. As per the lable visibility the combo should have to show only those warehouses which i have required.

Your precious hints are required please.

 

[Minty]

You can change the rowsource of the combo in VBA based on which open args is passed in?

Dim strCombo as String
SELECT CASE Me.OpenArgs
    CASE "Receipt"
            strCombo = "SELECT Field1, Field2 ,etc FROM MyLookUPTable WHERE Field3 = 'Mycritera' "
    CASE "Transfer"
            strCombo = "SELECT Field1, Field2 ,etc FROM MyLookUPTable WHERE Field3 = 'Mycritera2' "
    CASE "Adjustment"
            strCombo = "SELECT Field1, Field2 ,etc FROM MyLookUPTable WHERE Field3 = 'Mycritera3' "
END SELECT

Me.MyCombo.Rowsource = strCombo

 

[ahmad_rmh]

You can change the rowsource of the combo in VBA based on which open args is passed in?

Dim strCombo as String
SELECT CASE Me.OpenArgs =
    CASE "Receipt"
            strCombo = "SELECT Field1, Field2 ,etc FROM MyLookUPTable WHERE Field3 = 'Mycritera' "
    CASE "Transfer"
            strCombo = "SELECT Field1, Field2 ,etc FROM MyLookUPTable WHERE Field3 = 'Mycritera2' "
    CASE "Adjustment"
            strCombo = "SELECT Field1, Field2 ,etc FROM MyLookUPTable WHERE Field3 = 'Mycritera3' "
END SELECT

Me.MyCombo,Rowsource = strCombo

thanks Minty