Theodore-
First, delete your Sub and put this in a Standard module (one you can see in
the list in the database window):
Public Function OfferDesignUnitPrice(lngDesignID As Long, dblTaxRate As
Double) As Currency
Dim varX, varY
varX = DSum("[ItemQuantity]*[ItemUnitPrice]*(1-[ItemDiscount])",
"tblOfferDesignDetails", "[OfferDesignID]=" & lngDesignID)
varY = CCur(Nz(varX * (1 + dblTaxRate)))
OfferDesignUnitPrice = varY
End Sub
Now change your query to:
SELECT tblOfferDesigns.*, tblOffers.TaxRate,
OfferDesignUnitPrice([OfferDesignID], [TaxRate]) As OfferUnitPrice
FROM tblOffers INNER JOIN tblOfferDesigns ON tblOffers.OfferID =
tblOfferDesigns.OfferID;
John Viescas, author
"Building Microsoft Access Applications"
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
-----Original Message-----
From: [email protected] [mailto:[EMAIL PROTECTED] On Behalf
Of tyki9799
Sent: Thursday, January 19, 2006 8:08 PM
To: [email protected]
Subject: [ms_access] Re: Continuous form problem
John, thanks for the response.
The form's record source is the following:
SELECT tblOfferDesigns.*, tblOffers.TaxRate
FROM tblOffers INNER JOIN tblOfferDesigns ON tblOffers.OfferID =
tblOfferDesigns.OfferID;
The unound textbox in question is txtOfferDesignUnitPrice.
The sub that makes the calculation is this:
Private Sub OfferDesignUnitPrice()
Dim varX, varY
varX = DSum("[ItemQuantity]*[ItemUnitPrice]*(1-[ItemDiscount])",
"tblOfferDesignDetails", "[OfferDesignID]=" & Me.[OfferDesignID])
varY = CCur(Nz(varX * (1 + Me.TaxRate)))
Me!txtOfferDesignUnitPrice = varY
End Sub
Then "OfferDesignUnitPrice" (i.e. the sub) is entered in the form's On
Current event procedure.
Thanks again,
Theodore
--- In [email protected], "John Viescas" <[EMAIL PROTECTED]> wrote:
>
> Theodore-
>
> If you can put a function call in the Record Source query that
includes in
> the parameters at least one field in the recordset, you should be
able to
> fix the problem. What is the Control Source? What is the code that
> calculates the value? Are you requerying or refreshing from any
event in
> the form?
>
> John Viescas, author
> "Building Microsoft Access Applications"
> "Microsoft Office Access 2003 Inside Out"
> "Running Microsoft Access 2000"
> "SQL Queries for Mere Mortals"
> http://www.viescas.com/
>
>
> -----Original Message-----
> From: [email protected] [mailto:[EMAIL PROTECTED]
On Behalf
> Of tyki9799
> Sent: Wednesday, January 18, 2006 5:47 AM
> To: [email protected]
> Subject: [ms_access] Continuous form problem
>
> Hi to all!
>
> I have a continuous form with a calculated textbox. The calculation
> comes by some code in the form’s module. The trouble is that this
> textbox shows the same result for all form’s records. The
correct one
> is the one in the current record. I know this is common problem with
> continuous forms. What should I do so that the correct calculation
> shows for every record?
>
> Thanks in advance
>
> Theodore
>
>
>
>
>
>
>
> Yahoo! Groups Links
>
Yahoo! Groups Links
Yahoo! Groups Links
<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/ms_access/
<*> To unsubscribe from this group, send an email to:
[EMAIL PROTECTED]
<*> Your use of Yahoo! Groups is subject to:
http://docs.yahoo.com/info/terms/
