John- I can clearly see your reasoning here but I decided to create the field in tblOffers. I also created the code to do the job and it works alright. I will check Dawn's suggestion too. I try to make the application as light as possible by querying as few bits of data as it is possible.
Sure I'm kind of stubborn here. But you see I can secure that the new field created in tblOffers stores always the right value. I locked the textbox that is bound to the field in Offers form so no one can change the value. He can only see it. The only way to update it is through the OfferDesignSelection form which always gets the right value by design (it is where offer items are selected and respective calculations are being made). Or through the tblOffers table itself but this isn't an option to users. They can only get to forms and reports. Here I have a piece of code that I'd like to change: stLinkCriteria = "[Customer]=" & "'" & Me![txtCustomer] & "'" How can I change it so that instead of Me![txtCustomer] (i.e. the value in a form's control) have the value of a field in the form's data source, i.e. Me.Customer. I tried different options but I always seem to get it wrong as I get error messages. Is it the same when the field's data type is text or it's a number? Theodore --- In [email protected], "John Viescas" <[EMAIL PROTECTED]> wrote: > > Theodore- > > No. Storing a calculated value in a table is a very bad idea. You would > have to write a ton of complex code to make sure the value stays in sync. > For this one case, save the SQL as a query. > > 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 25, 2006 3:14 AM > To: [email protected] > Subject: [ms_access] Re: Continuous form problem > > John, I'm really-really sorry for your headache! I understand it's > quite complicated and I strugle to tame the beast! > > I have a rule: I never store a form's query as a standalone one. I > always store it with the form's data source. This way I avoid both > deleting the querry or amend it by mistake. > > The other way I thought so I would finally get to a solution is to > create an OfferGrandTotal field in tblOffers table. Then I would store > the value of the Main form's txtGrandTotal calculated textbox to the > afore-mentioned field when the form gets closed. > > That is, being in MainForm, > > tblOffers.OfferGrandTotal = Me!txtGrandTotal > > WHERE Me.OfferID = tblOffers.OfferID > > Any clue of a way to do so? > > I mention again that txtGrandTotal textbox is a calculated control, > not bound to a field. > > I hope that this eases matters down. > > Thanks once more and sorry of your hurting head! > > Theodore > > --- In [email protected], "John Viescas" <[EMAIL PROTECTED]> wrote: > > > > Yes, my head hurts. > > > > Use the form's query in the DSum: > > > > DSum("[OfferUnitPrice]*[DesignQuantity]", "<name of query here>", > "OfferID > > = " & <offer ID you want>) > > > > 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: Monday, January 23, 2006 11:18 PM > > To: [email protected] > > Subject: [ms_access] Re: Continuous form problem > > > > John, the problem is that the value of OfferUnitPrice isn't stored in > > tblOfferDesigns table. It would be ok if so. It's only calculated by > > the use of the public function you suggested to me, i.e. > > > > 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 > > > > This function is then passed in the form's query, i.e. > > > > SELECT tblOfferDesigns.*, tblOffers.TaxRate, > > OfferDesignUnitPrice([OfferDesignID], [TaxRate]) As OfferUnitPrice > > FROM tblOffers INNER JOIN tblOfferDesigns ON tblOffers.OfferID = > > tblOfferDesigns.OfferID; > > > > where OfferUnitPrice is bound to a textbox. Another textbox carries > > the function OfferUnitPrice*DesignQuantity and finally a textbox in > > the form's footer does the Sum(OfferUnitPrice*DesignQuantity) for the > > selected Offer. > > > > (DesignQuantity is a field in tblOfferDesigns) > > > > Kind of complicated I suppose! > > > > You see, there are three tables involved so I can't figure out a Dsum > > function that will do the job. The first table is > > tblOfferDesignDetails where are stored the individual items (their > > quantities and price) that make up the various designs. The second > > table is tblOfferDesigns that carries the various designs offered > > (their quantities). You see every design is made of various items is > > various quantities. Then every design may be required in various > > quantities. An offer normally carries a number of various designs. The > > third table is tblOffers which carries the TaxRate. > > > > So it's like that: > > Sum{[Sum[ItemQuantity*ItemUnitPrice*(1-[ItemDiscount])from > > tblOfferDesignDetails]] * [DesignQuantity from tblOfferDesigns]} > > and then all this multiplied by [(1 + TaxRate) from tblOffers]. > > > > I tried to make it as clear as I would here!!! > > > > I think your getting a headache and that's my fault! > > > > I'm really grateful, Theodore > > > > --- In [email protected], "John Viescas" <[EMAIL PROTECTED]> wrote: > > > > > > Theodore- > > > > > > Probably no need for a custom function. Use DSum: > > > > > > DSum("[OfferUnitPrice]*[OfferDesignQuantity]", "<table name here>", > > > "OfferID = " & <expression to get offer ID to filter>) > > > > > > To do the requery: > > > > > > Forms!OtherForm!OtherSubform.Form!ComboBox.Requery > > > > > > 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: Monday, January 23, 2006 7:49 AM > > > To: [email protected] > > > Subject: [ms_access] Re: Continuous form problem > > > > > > Cheers John. > > > > > > The requery statement worked alright. > > > > > > The function (code) is needed (although the textbox expression is > > > fine) as I wish to to get this value in another form and I don't like > > > to include in this form's record source the tables required to > > > calculate this value. The only common field that is included in the > > > form's data source is OfferID and this is needed as a filter in the > > > function. > > > > > > And again:(!) How would I requery a combobox that lies in a subform > > > from another form (not the subform's parent form)? > > > > > > Your help is really great! > > > > > > Theodore > > > > > > --- In [email protected], "John Viescas" <[EMAIL PROTECTED]> > > > wrote: > > > > > > > > Hmmmm. > > > > > > > > Try: > > > > > > > > Me.Parent.SubformA.Requery > > > > > > > > Why write a function when the expression in the text box works just > > > fine? > > > > > > > > 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: Saturday, January 21, 2006 8:41 PM > > > > To: [email protected] > > > > Subject: [ms_access] Re: Continuous form problem > > > > > > > > John- > > > > > > > > I entered > > > > > > > > Me.Parent.Refresh > > > > > > > > on the After Update event in Subform B's module but what I got is a > > > > trembling subform A! I seems that it tries to update for ever it's > > > > records and the other calculated elements in it and Access gets > stuck > > > > displaying the hour glass so I have to close it throught the Windows > > > > Task Manager! > > > > > > > > I fixed the Sub as you adviced creating a textbox with > > > > "=Sum([OfferUnitPrice]*[OfferDesignQuantity])" as it's data source. > > > > How would I get the same result through a query in a Standard Module > > > > as the one you suggested to me the other time? > > > > > > > > Thanks, > > > > > > > > Theodore > > > > > > > > --- In [email protected], "John Viescas" <[EMAIL PROTECTED]> wrote: > > > > > > > > > > Theodore- > > > > > > > > > > Try: > > > > > > > > > > Me.Parent.Refresh > > > > > > > > > > Put the Sum in the footer section of Subform A (in Continuous > view). > > > > > > > > > > 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 11:25 PM > > > > > To: [email protected] > > > > > Subject: [ms_access] Re: Continuous form problem > > > > > > > > > > John thanks for the advice. It worked out fine except for > subform A > > > > > getting updated (I followed your instructions). > > > > > > > > > > I entered this code on the After Update event in Subform B's > > module: > > > > > > > > > > Forms!MainForm.SubformA.Refresh > > > > > > > > > > But then get the following error message: > > > > > > > > > > Run-time error '438': > > > > > Object doesn't support this property or method > > > > > > > > > > And one further question: > > > > > > > > > > In subform A's data source there is an OfferDesignQuantity > field and > > > > > there's a Textbox bound to this field in subform A. > > > > > I also have another unbound textbox that does carries the > > > > > multiplication OfferUnitPrice*OfferDesignQuantity. So far so good > > > > > (except for updating everything as soon as subform B gets > updated). > > > > > > > > > > I wish to have in the Main form a textbox that would show the > > total of > > > > > the Offer i.e. the Sum(OfferUnitPrice*OfferDesignQuantity). > > > > > How would I do this? > > > > > > > > > > Theodore > > > > > > > > > > --- In [email protected], "John Viescas" <[EMAIL PROTECTED]> > wrote: > > > > > > > > > > > > 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 > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > Yahoo! Groups Links > > > > > > > > > > > > > > > > > > > > > > > > > > > > Yahoo! Groups Links > > > > > > > > > > > > > > > > > > > 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/
