Theodore- Do not store the value. Use the function I recommended earlier. If the function is basing its calculation on rows existing in B, a Refresh should recalculate the value when you add or change a row in B - do the Refresh in AfterUpdate of B.
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: Friday, January 20, 2006 12:55 AM To: [email protected] Subject: [ms_access] Re: Continuous form problem John- I partially solved the problem by creating an "OfferDesignUnitPrice" field in tblOfferDesigns table and then amended the "Me!txtOfferDesignUnitPrice = varY" statement to "Me.OfferDesignUnitPrice = varY". This way the it's the field that stores the value. (Textbox txtOfferDesignUnitPrice is now bound to the "Me.OfferDesignUnitPrice" field.) But as often it's the case there is one more little problem to solve. You see the continuous form in question is in fact a subform (call it A) which in turn is linked to another subform (call it B) that lies in the main form. What I do is create a record in subform A and then get to subform B to create subsequent records for every record in subform A. What I like is that as subform B is updated (new records are created and/or old ones are amended) the elements in subform A to be updated too. This means that code as the code above should be run and any calculated controls should be updated as soon as subform B is updated. And then then record selected in subform A should be the last selected. What will be the best practice for this? I also have to mention here that subform A is linked to the main form by the means of a field. Theodore --- In [email protected], "tyki9799" <[EMAIL PROTECTED]> wrote: > > 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/
