location issues

[Keiath]

Hi Guys

Another problem I need help sorting out.

I have a stock control system that has tabs. On the 1st is a set of data that gets its information for else where, On another tab I have a stock activity sub form that gets its data from a table called stock activity. In the Sub form I have the following fields StockID, Dateofmovement, RefNo, Location, StockIn, StockOut. (This is a relationship via the stock control number in both the stock activity and stock control table, so that data from stock activity only shows data in the relating stock control form)

on the 1st tab I have Total Stock (this is a calulation of stockIn-stockout) that works fine. But I then need to show where that stock is. Warehouse1 or warehouse2

so that on the 1st tab i have the following information Total Stock=60 Warehouse1=20 warehouse2=40

How can I do this?

Please help its the last part of the jigsaw

Thanks

 

[The_Doc_Man]

First, recognize that a tab control doesn't make the form into a sub-form. It is just a way to get more display area without getting more display area - by splitting it.

Therefore, if the information you want is available to the main form, it can be displayed anywhere on the main form including tab control pages. If no other way, you can pull the information from your relational setup. I might do this one of two ways.

If the info is available to the sub-form, let the sub-form show it. This is the right answer if the same stock can be kept in multiple locations.

Otherwise, put a DLookup as the controlsource for a text box and look up your (single) location that way. If you need to do so, write a query that you can exploit as the recordsource for the DLookup.

 

[Keiath]

actually came up with

=Nz(Sum([StockIn]),0)-Nz(Sum([StockOut]),0) for my total stock count and

=-Sum(([Location]="1")*(Nz([StockIn],0)-Nz([StockOut],0))) for warehouse1

=-Sum(([Location]="2")*(Nz([StockIn],0)-Nz([StockOut],0))) for warehouse2

Works perfect

Problem solved