Domain Lookups

[Shep]

(I'm still using Access 97)

I develop almost exclusively using unbound forms/subforms.

My question concerns totals for the columns in a continuous subform. My totals TextBoxes are in the Detail section of the parent form.

I am having trouble writing a way to derive these totals quickly. I avoid Domain lookups like the plague, normally. I tried it in this case (DLookup within a saved query) and, there being 8 columns which I need to total, the time waiting for this data was unacceptable.

Any suggestions how I might calculate these totals and display them somewhere besides a form footer, more quickly?

Thanks people

 

[KenHigg]

Could you do a sum() in the footer?

 

[Shep]

There is no footer on the form

 

[KenHigg]

Mile-Phile just did some custom domain type functions in the vba section a couple days ago...

 

[Shep]

Thanks Ken, I'll take a look

 

[dan-cat]

(I'm still using Access 97)

I develop almost exclusively using unbound forms/subforms.

My question concerns totals for the columns in a continuous subform. My totals TextBoxes are in the Detail section of the parent form.

I am having trouble writing a way to derive these totals quickly. I avoid Domain lookups like the plague, normally. I tried it in this case (DLookup within a saved query) and, there being 8 columns which I need to total, the time waiting for this data was unacceptable.

Any suggestions how I might calculate these totals and display them somewhere besides a form footer, more quickly?

Thanks people

You need to add a text box within your sub-form having a control source of Sum([YourFieldName])

You can then add a text box within your parent form referencing the 'Sum' text box within your sub-form or else.....

You could attempt to create a calculated field within the recordsource of your form.

 

[Pat Hartman]

I develop almost exclusively using unbound forms/subforms

- why ever would you do that? The biggest selling feature of Access is bound forms/reports. If you're intent on writing code, why not use VB?

If your forms were bound, you could just add a footer to the subform and add =Sum(fldA) as the control source to sum fldA.

For your unbound forms, rather than using individual DSum()'s, write a total's query that sums the relevant fields. You'll need to run the query and populate the controls in several places. For example, the AfterUpdate event of the subform and the current event of the main form. There may be others, you should be familiar with all the places you need to put code to get forms to work properly. As you're doing this, remind yourself that the bound form solution doesn't require any code.

 

[Shep]

Actually Pat I bind in code after the form is open. After a few years of this, it's getting pretty routine. The sad reason is that I'm still developing for this terribly designed backend which I inherited. The flaws are many and widespread, meaning that queries are twisted and layered and kicked and beaten to get the results I need. Many of them are very slow.

It's partly aesthetics. I like a clean form. In most cases, I don't want the form's fields to be populated until the user decides to view a record or recordset.

The good news is that I'm also designing and building this monstrosity's replacement, when I have time. And yeah...I find myself leaning ever closer toward doing the frontend in VB, but I'm not proficient in VB. The learning curve doesn't scare me but it annoys me. I'm trying to wrap this thing up by the end of the year and I'm neck deep in an Access frontend already.

Also, I'm still new at this. Yesterday I wrote a couple of totals querys and I'm running them by way of DLookup code in the AfterUpdate event of a combobox to populate the various totals textboxes. How do you "run the query and populate the controls" in another fashion? I'm dead sure that you have a non-DSum/DLookup method for this.

 

[bjackson]

If you must use dlookups and the like have a look at the domain functions
replacements that i posted in the code repository.There is a subsitute for all the domain functions and from what i can gather they replace the functions with queries.You will find a huge performance hit if you replace all your "d" functions with "t" functions included , and i tip my hat to the person who wrote the functions

Regards
Bjackson

 

[dan-cat]

Shep,

As I said before the easiest way to do this is....

a) Place an unbound text called txtSum within your sub-form with the control source =Sum([Thefieldyouwanttosum]). This details the sum of your sub-form

b) Now put another unbound textbox in your parent form with the control source of =[Forms]![YourParentForm]![YourSubform]![txtSum]. This will detail the value of your txtSum field.

There is no need for DLookups or complicated amendments to the recordsource of your form for this type of solution.

 

[PierreR]

Doing it with Date and Time.

I use this example, subtracting Date/Time, summed it in the subform header or footer and transferring it to the main form.

This goes in the Control Source Property of the Text Box called TotalTime in the subform header or footer, which is called frmActivities:

=HoursAndMinutes(Sum([EndDateTime]-[StartDateTime])

This goes in the Control Source Property of the Text Box on the main form:

=Form!frmActivities!TotalTime

 

[PierreR]

No form header/footer.

If there should not be a (sub)form header or footer, you can set the Visible property of the header and footer to No, once you have gone View-Form Header/Footer in the main menu of Access, just to do the calculation. The reference to the text box on the hidden (sub)form header/footer still works.

 

[Shep]

Thanks dan-cat,

Sorry, I didn't overlook your first post. It was late when I last posted and I couldn't stay awake any longer. Now that I'm coherent once again...

It's a complex form with subtotals for each of several subforms, totals for all subforms and grand totals for all sales. I've used hidden Sum textboxes before and referenced them elsewhere, didn't really think about hiding the footers. So your suggestion along with PierreR's should take care of the subform's totals. The system-wide totals of course aren't part of the form's recordsource so I expect some delay in getting those...just casting about for ways to make it reasonably fast. I'll work on it, thanks to all...

 

[dan-cat]

Thanks dan-cat,

Sorry, I didn't overlook your first post. It was late when I last posted and I couldn't stay awake any longer. Now that I'm coherent once again...

It's a complex form with subtotals for each of several subforms, totals for all subforms and grand totals for all sales. I've used hidden Sum textboxes before and referenced them elsewhere, didn't really think about hiding the footers. So your suggestion along with PierreR's should take care of the subform's totals. The system-wide totals of course aren't part of the form's recordsource so I expect some delay in getting those...just casting about for ways to make it reasonably fast. I'll work on it, thanks to all...

No worries Shep, I don't believe you actually need to place the sum field in the sub-form footer for it to work.

In the case of finding other Sum values not related to your form look at opening a temporary recordset with a calculated Sum function contained therein.

Dim db as DAO.Database
Dim rec As DAO.Recordset
Dim strSQL as String
Dim myValue As Double

strSQL = " SUM([FieldtoSum]) As mySumField FROM myTable WHERE User ='Dave'"

Set db = CurrentDb
Set rec = db.OpenRecordset(strSQL)

myValue = rec("mySumField")

Set rec.Close
Set rec = Nothing