Dawn, you're right this is clearly an option. But for the time being I've chosen the other option, that is to store the value in a field instead , 'though I will definitely check your suggestion. I have employed this option in other cases.
I appreciate your help, Theodore --- In [email protected], "Crosier, Dawn" <[EMAIL PROTECTED]> wrote: > > If you are very adamant about not storing the actual query, then you > could create the query on the fly: > > Dim strSQL as String > > strSQL = "Select....." > > As appropriate you can put this in the OnOpen Event of your form. > > Dawn Crosier > Application Specialist > "Education Lasts a Lifetime" > > This message was posted to a newsgroup. Please post replies and > questions to the group so that others can learn as well. > > -----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/
