Search results

Colin, I need the relationship. I need to go to to tblPrdName to find the text name of the numbered period. Just to clarify, my query works and is already JOINed to tblPrdNames. But it's joined on my 'redundant' field thAcPrd (a numeric 6). But thAcPrd is a sub set of thAcYrPrd (202006) so...

Can I join a table on an expression ? I have a field in a table called thAcYrPrd which is a long in the format 202006 where 06 = 6th period of year 2020. I decided to do it this way because it makes selection between a period range in the query straightforward. (Periods do not necessarily...

Got it fixed but can't explain it. I had two fields on the main form which showed totals from the sub form footer. Deleted them and found I could filter the sub form without the Access Automation error. Put them back one at a time, with the same names and data source syntax, and the form is...

Having trouble with un-setting a filter in VBA. It may be to do with master-child relationships, not sure, everything works apart from an Automation Error I'm getting. I have a sub form with a query with no parameters. So if I run the query manually it selects every record from the table...

Ah, sorry, no don't want to do that. I know it's hard making a diagnosis at a distance. I haven't solved this problem yet but I'm posting a related question.

Here's the error message. The sub form with the funnel 'Account' is used in two forms and only one throws the error. It's as though 'thPostingBatchNo' has got coded into the host form somehow but I can't see where. Is there an event that fires when I set the drop-down filter? The filter sets...

DbGuy, the project is just too big with too many interactions to upload. Still haven't figured it out. I rebuilt the sub form from scratch. But I should have remembered that the very same sub form is re-used in a different host form. So having rebuilt it, I have exactly the same behaviour...

Tried the decompile, c&r, still getting the message. So here's what I'm seeing: Open the main form with a record in the normal use way, ie, app is running. Main form has 2 tabs, one is TransLineLedger. No Filter is selected (I can see the funnel with the x and it says No Filter) Datasheet shows...

I've ended up in a situation where my form seems to be 'stuck' with a filter and I can't get rid of it. I've got a form which has a sub form TransLineLedger. The column headings have the drop down arrows to filter on the contents. I also built in one filter on a command button but it seems to...

Yes, I'm saying when I set TreeView.Indentation = 310, font.name = "Courier New", font.size = 9, all levels are exactly 3 characters indented. It turned out I already had 7 levels so I made code to count the levels and adjust the padding to the minimum that was needed to pad out the top level...

Ah, sorry about the file. I wasn't sure what you meant by data, I included the table that populates the tree view. That's what I'm doing. Populate the tree, count the levels to determine the minimum amount of padding, then pad the node text. So all levels are aligned perfectly on the...

Thank you MajP. I can attach the records that populate the tree. I've been having a close look at your code this evening to see where I can get the Level from to set the text width. As I understand it, the queries can't determine that unless they're based on the recursive routine to populate...

Your hierarchy schema is exactly what I've got. Currently I'm repeat counting the lowest levels so that's why it's really slow. I was already hatching a plan to use a temp table to save the record totals. Currently I put the list of decendants for every Group record into a temp table then sum...

I tried the IIF and I also tried a Switch but when I include the fGroupTotal function Access says sub queries are not allowed. I can try again, but ... leaving it alone for a short while ... Would a Union query do it? I've never done one. But I'm thinking Iteration 1 all "B" type nominals...

The query returns a result in any *one* of the last 3 columns. Ignore NetSum, it's not filtered for my criteria. (There are balances for Balance_PL but only where nominalType = "P" and they're not showing in the screenshot) I want to plant whatever the value is into a sub directly from the...

I have a query which returns the values I need, but I would like them returned into one column. By that, I mean, I can get three columns in the dataset, called Balance_Grp, Balance_PL, and Balance_BS. I want to consolidate them into one value, OR, format a string according to whether the...

Two parts to the discussion now: The formatting, I didn't know what to expect, I wondered if maybe there was some way of tabbing over some string or text in the control to line it up. But if it's a case of padding out a fixed-width font, I can do that, I'm already doing that for the branches...

Definitely interested to know how it can be done Colin

Hang on I'll format the SQL 'SELECT "recid" & [nominalid] AS ID, ' "recid" & [nominalparent] AS parentID, ' tblnominalaccounts.nominalName AS nodeText, ' "recid" AS identifier, ' tblnominalaccounts.nominaltype, ' Sum (tbltranslines.tlNetValue) AS NetSum, '...

Thanks, MajP, and again for the TVW module. I need to find a way to do this. I temporarily set the font to a retro Courier which aligns each branch but I still haven't solved the different offsets. Setting the text to a fixed width to accommodate all possibilities isn't a problem. The text...