Warehouse Inventory Project

[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

 

[Gasman]

And we have reached 100 posts

 

[ahmad_rmh]

In a month, I have learnt a lot through this forum and still learning.

The people in this forum: some are very cooperative, some are moody, and some have very odd moods. Any how, In short, you could resolve your issues in a shorter period of time through professionals experts suggestions and advices.

 

[ahmad_rmh]

how to make a filter on a form as per select the case OpenArgs, In simple terms I want to see only those records which are related to "Receipt" and so on.

on the other hand, i want to stick in the same stack as under

Select Case Me.OpenArgs
        Case "Receipt"
            With Me
                .lblReceipt.Visible = True
                .lblTransfer.Visible = False
                .lblAdjustment.Visible = False
                .EntityFK.Visible = False
                .EntityFK_Label.Visible = False
                .VendorsFK.Visible = True
                .VendorsFK_Label.Visible = True
                .TranTypeFK.RowSource = "SELECT TranTypePK, TranType from tblTranTypes WHERE TranTypePK<=2"
                .TranTypeFK.ColumnCount = 2
                .TranTypeFK.ColumnWidths = "0;1cm"
                .TranTypeFK.DefaultValue = 1
            End With

kindly suggest

 

[jdraw]

I see references that you have attached a copy of your database, but I do not see any attachments.
Also, in your related post you mentioned that you had it working.
Can you be specific in what exactly is the status?
And attach a sample database (zip format) with only enough data to highlight any specific issue and instructions to show same..

 

[ahmad_rmh]

I have three conditions in OpenArgs Case: Receipt, Transfer and Adjustment.

I want to see only those records as per the OpenArgs Case but it's showing all records of other cases.

The database file is attached herewith for your info.

 

[jdraw]

I am looking at your database. I see no instructions on the Dashboard.

Can you provide step by step instructions to highlight specific issues?
We're at post #105 and I am not familiar with your set up. Can you take a specific record or "something" and guide me to an issue along with your expectations vs the current issue?

 

[MajP]

Your code does not modify the recordsource of the form, only the rowsources of the comboboxes.