SellRate should get updated

[jalal]

Hi
I have developed an application for “Stock Verification” for a business establishment. I have used following 5 tables
1. tblProduct 2. tblSupplier 3. tblClient 4. tblTrans 5. tblTransDetails
Following is the field lists of the above tables

A. tblProduct
ProductID PK Autonumber
ProductName text
ProductCode text

B. tblSupplier
SupplierID PK Autonumber
SupplierName Text

C. tblClient
ClientID PK Autonumber
ClientName Text

D. tblTrans
TransID PK Autonumber
SupplierID FK Number
ClientID Fk Number
TransDirection ( +1 for buying, -1 for Selling)
TransDate Date
BillNo text
BillDate Date

E. tblTransDetails
TransDetailsID PK Autonumber
TransID FK number
ProductID FK number
PriceEach currency
TaxRate percent
Quantity Number
SellPrice Currency



On the basis of tblTrans I created a query “qryPurchase” with following columns
SupplierID
TransID
TransDirection (criteria 1)
BillNo
BillDate

I have created a Form from qryPurchase as frmPurchase with above controls

On the basis of tblTransDetails I Have created a query “qryPurchaseDetails” with following columns (qryPurchaseDetails will serve as SubForm of frmPurchase)

TransDetailsID
TransID
ProductID
PriceEach
TaxRate
Quantity
BuyRatePriceEach*TaxRate)
BuyAmount PriceEach*TaxRate*Quantity)
SellRateBuyRate*.11) (0.11 is the profit margin)

I have created the SubForm “frmPurchaseDetails” from qryPurchaseDetails

Likewise I have created a Form “frmSales” and subForm “frmSalesDetails”
On the basis of tblTrans I created a query “qrySales” with following columns

ClientID
TransID
TransDirection (criteria -1)
BillNo
BillDate

On the basis of tblTransDetails I Have created a query “qrySalesDetails” with following columns (qrySalesDetails will serve as SubForm of frmSales)

TransDetailsID
TransID
ProductID
SellPrice
TaxRate
Quantity

The movement some product is purchased the system gives its sellRate as well.
Upto here Everything is working fine.

Since Buying price “PriceEach” of a product changes as a routine from one date to another date
Obviously the “SellRate” also changes. And the new TransID goes on increasing. It is fine.

The Problem:
With the help of DLookup function I managed to get the “SellRate” from “qryPurchaseDetails”
I gave the formula as =Dlookup(“SellRate”,”qryPurchaseDetails”,”ProductI D=” & ProductID) and I was successful to get the SellRate inscribed in the “qrySalesDetails”.
But the movement the buying price “Priceeach” changes, the above formula gives the previous “SellRate” as it brings the SellRate of the product based on the previous TransID.

While as I want that the system should give the latest sell rate on the basis on new buy rate, which I can’t get with the help of SQL or Query.

It is requested to help me in the problem using VBA procedure or any other method
Thanks
Jalal

[June7]

Don't fully understand the issue as summarized by:

But the movement the buying price “Priceeach” changes, the above formula gives the previous “SellRate” as it brings the SellRate of the product based on the previous TransID.

While as I want that the system should give the latest sell rate on the basis on new buy rate, which I can’t get with the help of SQL or Query.

Why would value be based on previous TransID?

Is the DLookup in a textbox ControlSource?

Is the issue that viewing existing records after price has changed means the price at time of transaction is not shown? How are you saving the price at time of transaction?

[jalal]

Don't fully understand the issue as summarized by:
Why would value be based on previous TransID?

Is the DLookup in a textbox ControlSource?

Is the issue that viewing existing records after price has changed means the price at time of transaction is not shown? How are you saving the price at time of transaction?

Dlookup works correctly

The movement some product is purchased the system gives its sellRate as well.
Upto here Everything is working fine.

Since Buying price “PriceEach” of a product changes as a routine from one date to another date
Obviously the “SellRate” also changes. And the new TransID goes on increasing. It is fine.

With the help of DLookup function I managed to get the “SellRate” from “qryPurchaseDetails”
On “frmSalesDetails” I gave the formula as =Dlookup(“SellRate”,”qryPurchaseDetails”,”ProductI D=” & ProductID) and I was successful to get the same “SellRate” inscribed in the “qrySalesDetails”.
On next transaction the movement the Business Establishment makes fresh purchases on new buying prices i.e. “PriceEach” changes, The system still works fine and the new “SellRate” as per new “PriceEach” is got on “frmPurchaseDetails”. But on “frmSalesDetails” the system does not displays the new “SellRate” instead the old “SellRate” is still shown.

While as I want that the system should give the latest sell rate on the basis on new buy rate, which I can’t get with the help of SQL or Query.

It is requested to help me in the problem using VBA procedure or any other method
Thanks
Jalal

[June7]

The db you provided does not have query "qryPurchaseDetails" nor forms "frmPurchaseDetails" and "frmSalesDetails".

[jalal]

The db you provided does not have query "qryPurchaseDetails" nor forms "frmPurchaseDetails" and "frmSalesDetails".

I am sorry for the inconveneance

The corrected db is attached now

Thanks and regards

Jalal

[June7]

I don't understand why you are using DLookup in frmSalesDetails. The RecordSource for that form is qryTransDetailSell and all the fields needed to calculate the SellRate are available in that query, just as they are in qryPurchaseDetails. The only difference between the two queries is one has tblClient and one has tblCreditor. Could actually include tblClient and tblCreditor in one query and make that one query work for both forms by setting Filter property of the form to: TransDirection = -1 (or 1).

SELECT tblTransDetail.*, tblProduct.Product, CLng(Nz(Round([Quantity]*[PriceEachEx],2),0)) AS AmountEx, CLng(Nz(Round([Quantity]*[PriceEachEx]*[TaxRate]/100,2),0)) AS Tax, [AmountEx]+[Tax] AS AmountInc, tblTrans.TransDate, tblTrans.TransDirection, tblCreditor.MainName, tblClient.MainName, [AmountInc]/[quantity] AS CostEach, [quantity]*[sellprice] AS [Total Amount], tblProduct.BrandID, tblProduct.SeriesID
FROM ((tblClient RIGHT JOIN tblTrans ON tblClient.ClientID = tblTrans.ClientID) RIGHT JOIN (tblProduct RIGHT JOIN tblTransDetail ON tblProduct.ProductID = tblTransDetail.ProductID) ON tblTrans.TransID = tblTransDetail.TransID) LEFT JOIN tblCreditor ON tblTrans.CreditorID = tblCreditor.CreditorID;

[jalal]

Thanks Problem solved