Search results

Thanks for your responses. The use of the MSysRelationships table solved the problem.

David Thanks for your reply. I had already done this: For Each rln In gdbs.Relations blnSelect = True If InStr(1, rln.Table, "Hol") > 0 Then blnSelect = False End If If Mid(rln.Table, 2, 3) = "Sys" Then blnSelect = False End If If (blnSelect = True) And...

I have written some VBA to create a basic data model in Excel from an Access database. It’s nearly there, but I have found an issue with finding the Foreign keys in tables. Here is the relationship: And here are the indexes in table tblSubHeading: When I use the Relation object the VBA...

CJ - Finally the penny has dropped & I see what you meant. It's all working now - many thanks for your patience & help. Bodders

As requested here is all the code used to rebuild the table and subform. I *think* the problem is somewhere in the subform properties. Also thanks for all the help and advice so far - it's been very useful The complete code exceeds the character limit for posting here, so here is just the proc...

How did you set the total cells - manually ?

This is what I see with the query as the subform SourceObject - a Total row but no totals:

Thanks for the responses. Having tried everything suggested above, here is the current situation. The query which is the SourceObject for the datasheet subform produces a totals row and individual total rows exactly as it should, using AggregateType = 0. The problem is the subform. I can add...

Warnings are definitely on

Changing qdf.Fields(5).Properties("AggregateType") = 0 to qdf.Fields(5).Properties("AggregateType") = acAggregateSum actually produces the wrong answer in the query. It gives the average.

Here's the query opened on its own with subtotals showing: And here is the subform with the above query as its Recordsource (no totals showing): I cannot work out why this should be.

Interestingly the query opened on its own has the Total row with the correct values. Only on the subform - opened separately - it does not. Strange

In other posts I have read using the AggregateType of 0 should produce a Sum total

I have built a procedure to dynamically rebuild a subform and underlying table based on a crosstab query. This is so that it can show various combinations of categories (rows) and monthly totals (columns). I would like to have a total row appearing on the form and to use VBA to achieve this. The...

That was a quick response and a successful one. Changing Field2 to Field did the trick. Many thanks for that Bodders

I have created an accdb (Access 365 64-bit) for Holiday planning and budgeting, and it has a couple of ribbons with some custom images. To make the application more self-contained I have created a table USysRibbonImages to store them within the database itself. This has 3 fields – ControlID...

A follow-up to the above & thanks again for the responses. I looked at the cross-tab query which was the recordsource for the subform, and optimised by using a subquery. The result was that it solved the probem instantly, and reduced the time to run by a factor of 100 :)

Thanks for the responses. I have tried changing the field names to remove "Description" and those starting with a number - this made very little difference. I will try the suggestion from Isladogs to make the crosstab query as the source - it does not matter that it is read only.

I think this might be worth trying. I will let you know if it works. Thanks.

It's a database I have created to plan my holidays. There is a form that shows the details for each holiday and two subforms, one for dates and one for payments. They are dynamically recreated each time the holiday details are updated.