UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
2 Pages V  1 2 >  (Go to first unread post)
   Reply to this topicStart new topic
> Formatting A Dynamic Header And Detail, Access 2016    
 
   
LagoDavid
post Jul 11 2018, 02:08 PM
Post#1



Posts: 446
Joined: 12-October 03
From: Texas


I have a report based on a cross tab query. The cross tab query is based on two temporary tables. The second of those tables is where I get my Column Headings for the report (tmptblColumnHeads). Currently I have the control source on the column heads set to

=DLookUp("[SampleDate]","[tmptblColumnHeads]","[SortID]=1") (and SortID =2, 3, etc for the other columns). The control names are txtCol1, txtCol2, ...

I do not always have the same number of columns but the report can hold up to 7 columns of data. When there are less than 7 columns, the report errors. I need the column headings (and related detail fields) to be nothing or null if I don't have a value for them. I have a variable in my VBA code, intColumnHeadings, for which I used DCount on the tmptblColumnHeads to get the count of the number of column heads. I would like to use that variable in the "control source" field for the column headings controls (and detail controls) to set the control source to nothing or null if there are not enough columns to fill the report.


I am trying to put this code in the control source for column 7 (txtCol7). (the 7 in this case would be replaced by 1-7 depending upon which column head it is)

CODE
= If intColumnHeads >= 7 then
txtCol7 = DLookUp("[SampleDate]","[tmptblColumnHeads]","[SortID]=7")
ELSE
txtCol7 = NOTHING


Access tells me invalid syntax.
Go to the top of the page
 
RJD
post Jul 11 2018, 03:38 PM
Post#2


UtterAccess VIP
Posts: 9,148
Joined: 25-October 10
From: Gulf South USA


Hi: Yes, the code you show in your post is incorrect as a source for the textbox value. "If" is VBA and you will need an "IIf" in the source. Try something more like this ...

=IIf(intColumnHeads >= 7,DLookUp("[SampleDate]","[tmptblColumnHeads]","[SortID]=7"),"")

or just (I think better) ...

=DLookUp("[SampleDate]","[tmptblColumnHeads]","[SortID]=7"

... and the other numbers for the other headers - the result should be Null if the SortID does not exist.

Of course, we can't see the rest of what you are doing, so this is just a guess (I don't know where intColumnHeads is coming from, nor do I know what else the query and report look like). There is another possible way to do the headers for a dynamic report using a crosstab query as the record source (with an additional query to supply the sequence to header combination). But, as I said, we can't see your setup to see how close you are to that.

HTH
Joe
Go to the top of the page
 
LagoDavid
post Jul 11 2018, 05:26 PM
Post#3



Posts: 446
Joined: 12-October 03
From: Texas


yes, I got that to work for the column heads, and you are understanding what I have. Now I am trying to get the data columns to work.

You helped me with this before using your qryDatesInRange and qrySequence. I am trying to do the same thing in a different way, two temporary tables and then building a cross tab on them. In your method the report cross tab query has a SEQ number column (1-7) that is used as the control source for the details text boxes. In my cross tab query I use the ID number of the row in the tmptblColumnHeads table.

Instead of hard coding the source for the details text boxes as 1 or 2 or 3, etc., II need to have the control source be NULL if there is no data for it. I changed the intColumnHeads to a global variable (gintDataColumns) so it would be available to the report when I open the report. I tried this in the control source for the text boxes in the details section of the report but get an error:

For the first text box I used: IIf([gintDataColumns] > 0, 1, Null)
For the second text box I used: IIf([gintDataColumns] > 1, 2, Null)
For the third text box I used: IIf([gintDataColumns] > 2, 3, Null)

etc

And OH yes, I used DCount on the tmptblColumnHeads to get the value of gintDataColumns.
This post has been edited by LagoDavid: Jul 11 2018, 05:33 PM
Go to the top of the page
 
RJD
post Jul 11 2018, 06:35 PM
Post#4


UtterAccess VIP
Posts: 9,148
Joined: 25-October 10
From: Gulf South USA


Ah, yes. I forgot we did something like this together before.

Could you post the current db? I'll see if I can make some time to take a look at what you have done and what might be done to make it as you want. Just tell me which object names we are working with when you post the db.

I am traveling now, but will try to check in on this ...

HTH
Joe
Go to the top of the page
 
LagoDavid
post Jul 11 2018, 07:34 PM
Post#5



Posts: 446
Joined: 12-October 03
From: Texas


I don't know how to do that. It is a huge database with well over 100 linked tables and another 20 or so local tables. I will see if I can figure out how to get the relevant tables and queries and reports and forms, but it will take me a while.

With the cross tab query you provided a few weeks ago, the control source of the details text boxes is 1, 2, 3, etc. Those are the names of the column headings in the cross tab query that the report is based upon. With my crosstab query, it is the same, the cross tab column headings are 1, 2, 3, etc. they originate from a temporary table instead of from your qryDatesInRange and qryDateSequence. The report is designed for seven columns and if the number of actual columns is less than 7, the report will error because the text boxes are hard coded with 1,2, 3, 4, 5, 6, 7.

As an example I used = IIf([intDataColumns] > 6, 7, Null) as the control source for the text box in column 7, and Access says it does not recognize intDataColumns as a valid field name or expression. The intDataColumns is a variable that is created with the Form Load Event and gets the result of DCount on the number of records in the tmptblColumnHeads. That is what lets me know how many columns are in the cross tab and the tmptblColumnHeads holds the text values for the names of each column head (in this case they are dates).


I also attempted to load the details section data sources of the various text boxes with code in the form load event. I used:
CODE
Private Sub Report_Load()
   intDataColumns = DCount("*", "tmptblColumnHeads")
   Debug.Print "IntDataColumns = " & intDataColumns
   Me.txtCol1 = IIf([gintDataColumns] > 0, 1, Null)
   Me.txtCol2 = IIf([intDataColumns] > 1, 2, Null)
   Me.txtCol3 = IIf([intDataColumns] > 2, 3, Null)
   Me.txtCol4 = IIf([intDataColumns] > 3, 4, Null)
   Me.txtCol5 = IIf([intDataColumns] > 4, 5, Null)
   Me.txtCol6 = IIf([intDataColumns] > 5, 6, Null)
   Me.txtCol7 = IIf([intDataColumns] > 6, 7, Null)
  
End Sub

This just puts the literal number in every row instead of the report interpreting the number as the name of a column. Somehow I have to get a column name property into the text box rather than the literal number.


Go to the top of the page
 
RJD
post Jul 11 2018, 08:17 PM
Post#6


UtterAccess VIP
Posts: 9,148
Joined: 25-October 10
From: Gulf South USA


This doesn't look familiar at all. I can't see your db, of course, but your approach doesn't seem likely to produce the results you want, and not like the approach we worked on before. And the only way I can untangle this for you is to see your db - or at least enough to see how you are set up.

Is there some way you can cut down the db, perhaps simplifying tables and including only essential objects, and post that? I'll need to see at least how you are creating the crosstab and what the report design looks like. Otherwise I seem to be missing the important elements of what you are doing.

If you can come up with a test db to look at, I'll see what I can do. Note that I am traveling right now, so have limited ability to do this, but will try if you post a db.

HTH
Joe
Go to the top of the page
 
LagoDavid
post Jul 12 2018, 12:01 AM
Post#7



Posts: 446
Joined: 12-October 03
From: Texas


Hopefully this gets it.

just open the form frmSetUpReport to start.
Attached File(s)
Attached File  DynamicReport_Rev2.zip ( 165bytes )Number of downloads: 6
 
Go to the top of the page
 
isladogs
post Jul 12 2018, 04:21 AM
Post#8



Posts: 636
Joined: 4-June 18
From: Somerset, UK


You've posted the laccdb lock file so that's no use to anyone here!

If the column headers are fixed, you can specify them in the property sheet to handle the situation where some valuers will be null
For example, you could enter column headers Jan, Feb, Mar ...Dec and all columns will appear even if you have no data for some of the months

If you have dynamic column names, you need a different approach
I have posted several examples online to show ways of doing this.
Here is one such link:Extended File Properties using VBA

Have a look at the code and see if it helps you.
The number of columns can be varied but make sure you allow for the max number possible ...or add additional error handling
Go to the top of the page
 
LagoDavid
post Jul 12 2018, 08:31 AM
Post#9



Posts: 446
Joined: 12-October 03
From: Texas


I apologize for the wrong attachment. I rarely have ever tried to zip a file so I am not sure what I did wrong. I did not even know there was such a thing as a lock file. All of the files in my drive have the .accdb extension so I don't know where laccbd came from. I will try this again. I am just using the Win10 "send to compressed folder" option.


I reviewed the link and perhaps I am just not skilled enough to follow how a file attribute will help me. I think I need something like column name attribute in the report form. You said you have posted several examples of dynamic column names online and I will search for those, but if you have another example link please reply with it.

The column heads are dynamic and I am having no problem getting them to print in the page header section of the report. It is the details section I am struggling with and "dynamic details". See the report "rptParametersWithDateColumnHeadings3" in the attachment.



Attached File(s)
Attached File  DynamicReport_Rev2__2_.zip ( 561.31K )Number of downloads: 3
 
Go to the top of the page
 
isladogs
post Jul 12 2018, 09:16 AM
Post#10



Posts: 636
Joined: 4-June 18
From: Somerset, UK


The example was just to show you code suitable for a dynamic crosstab report where the headings can change
It was just the first one I had to hand the contents were irrelevant

I use similar code in most of my reports based on crosstab queries so another example won't add much.
Please have a look at my report code

I've opened your attachment but can't get the report to run from the form - is it the second one in the list
Also give me some options that will allow me to view the report from the form

Looking at it in design view, using DLookup in the column headings is the wrong approach
The headers should come direct from the crosstab query or from fixed column headers as appropriate
Go to the top of the page
 
LagoDavid
post Jul 12 2018, 10:09 AM
Post#11



Posts: 446
Joined: 12-October 03
From: Texas


Are you sure your link was correct? I did not see any report code in that link. I just saw a list of file attributes than can be accessed from various file types.

I apologize, I did not do a good job of providing enough information with the database. My form will not run the report yet. I did not get that far with the form coding yet. The form builds the cross tab query but does not yet run the report. You have to open the report I mentioned (rptParametersWithDateColumnHeadings3) manually at this time.

Choices On the form to build the cross tab query:
1. You can ignore the list box with form choices since the form does not yet run any reports.

2. Choose any of the sites but for example choose #2 SITE WEST
3. Choose any sample group but for example choose WW: WP DESALTER BRINES
4. Set the start date at 3/1/18 and the end date at today (this will provide a cross tab with four date column heads)
5. Click on the CREATE PDF command button (sorry I should have inactivated the query command button as it is not yet programmed)
6. Manually open rptParametersWithDateColumnHeadings3


Again, I apologize for not providing enough information at the outset.
Go to the top of the page
 
RJD
post Jul 12 2018, 11:00 AM
Post#12


UtterAccess VIP
Posts: 9,148
Joined: 25-October 10
From: Gulf South USA


Hi David: There was no rptParametersWithDateColumnHeadings3 so I worked with rptParametersWithDateColumnHeadingsNew. You had several problems implementing my approach to a dynamic crosstab report.

First, the comment from above from isladogs ...

QUOTE
Looking at it in design view, using DLookup in the column headings is the wrong approach
The headers should come direct from the crosstab query or from fixed column headers as appropriate

This is not correct in the approach we are working on, since the report is dynamic on the horizontal axis. Using DLookup is an integral part of my dynamic report procedure to look up the dynamic headers (headers created in the process with the sequence query, from which you get the header/sequence link).

Here are the issues with what you have ...

1. You forgot to add the In(1,2,3,4,5,6,7) to the Pivot line in the crosstab. This forces all seven columns, even if some are Null. See the revised crosstab query. And then you can ...
2. Set the detail columns as [1] [2] [3] [4] [5] [6] [7]. This since all the columns are accounted for, even if some are Null in content.
Then as for your On Load procedure ...
3. Remove the On Load procedure. It is not needed in this approach.
4. Ungroup the controls in the detail section to remove the layout. This layout makes the controls harder to work with and serves no apparent purpose in this report.

All this is done in my revision to your posted db, attached here. See if this addresses your issues.

HTH
Joe
Attached File(s)
Attached File  DynamicReport_Rev3.zip ( 490.53K )Number of downloads: 3
 
Go to the top of the page
 
isladogs
post Jul 12 2018, 11:37 AM
Post#13



Posts: 636
Joined: 4-June 18
From: Somerset, UK


QUOTE
Are you sure your link was correct? I did not see any report code in that link. I just saw a list of file attributes than can be accessed from various file types.


Yes I'm absolutely sure.
There are 4 reports but only one is relevant to this
The image in the post is the dynamic crosstab report which is called rptAttributesFileType_Crosstab. This is run using the Attributes List By File Type button

The code is in the Report_Open event:

CODE
Private Sub Report_Open(Cancel As Integer)

On Error GoTo Err_Handler
    
    '===================================================
    'Dynamic report designed to be used with Crosstab Queries
    
    'This code is especially "tuned" for crosstab queries.
    'As I like to have control over the layout, thus I have the lay-out designed first with "coded" controls.
    'Then the dynamic filling becomes very easy. The raw text I use to help with this is:
    
    'Making the column header and detail data flexible is possible,
    'but needs some VBA code in the OpenReport event.
    
    'To start doing this you need to place the fields "coded" in the report.
    'The column headings should be called "lblCol1", "lblCol2", "lblCol3", etc.
    'The "detail" fields should be called "Col1", "Col2", "Col3", etc.
    
    'The report query has two row header columns and a Total column,
    'therefore the first field is effectively column 4 (count starts at 0 so I used intI=3)
    'but this could differ for you.
    
    'The report has 36 columns so can handle up to 36 file types
    'If number of file types > 36, error 2465 occurs & the report exits.
    
    '=====================================================
    
    Dim intI As Integer
    Dim Rs As DAO.Recordset
    
    strSQL = "TRANSFORM First(tblAttributesFileType.AttValue) AS FirstOfAttValue" & _
        " SELECT tblAttributesFileType.ID, tblAttributesFileType.Attribute" & _
        " FROM tblAttributesFileType" & _
        " GROUP BY tblAttributesFileType.ID, tblAttributesFileType.Attribute" & _
        " ORDER BY tblAttributesFileType.ID" & _
        " PIVOT UCase(tblAttributesFileType.FileExtension);"
        
    'Debug.Print strSQL
        
    Set Rs = CurrentDb.OpenRecordset(strSQL)

    'Populate headers - start at column 2
    For intI = 2 To Rs.Fields.Count - 1
       Me("Lbl" & intI - 1).Caption = Rs.Fields(intI).Name
       Me("Lbl" & intI - 1).Visible = True
          
    Next intI

    'Populate checkbox controls
    For intI = 2 To Rs.Fields.Count - 1
        Me("chk" & intI - 1).ControlSource = Rs.Fields(intI).Name
        Me("chk" & intI - 1).Visible = True
    Next intI
    
    
    'Populate totals
    'each filled value = -1 (true) so do -SUM to get total
    For intI = 2 To Rs.Fields.Count - 1
        Me("Tot" & intI - 1).ControlSource = "=-SUM([" & Rs.Fields(intI).Name & "])"
        Me("Tot" & intI - 1).Visible = True
    Next intI
        
Exit_Handler:
    Exit Sub

Err_Handler:
    If Err = 2465 Then 'too many data columns for report (max = 36)
        N = DCount("FileExtension", "tblAttributesFileType", "Attribute='Name'")
        If N > 36 Then
            FormattedMsgBox "Attribute summary data has been saved for " & N & " file types         " & _
            "@The summary report can only show results for the first 36 file types         " & vbNewLine & vbNewLine & _
            "Delete unwanted data from the table 'tblAttributesFileType' before running this report        @", vbCritical, "Too much data!!"
            Cancel = True
            Exit Sub
        End If
    Else
        strProc = "Report_Open"
        MsgBox "Error " & Err.Number & " in " & strProc & " procedure: " & Err.Description
        Resume Exit_Handler
    End If
End Sub


However you need to study that in the context of the report to understand what the code is doing

As RJD has answered whilst I was responding, it may be that you now have an answer anyway
Go to the top of the page
 
RJD
post Jul 12 2018, 11:50 AM
Post#14


UtterAccess VIP
Posts: 9,148
Joined: 25-October 10
From: Gulf South USA


Hi isladogs: The approach I developed a while ago, and has been used here helping several members, does not involve any VBA (or manual intervention or exports to Excel, as some procedures suggest). It just adds a single sequencing query, then generalizes the crosstab with columns 1, 2, 3, etc. The sequencing query is used in the JOIN of the crosstab to link the original PIVOT field to the sequence number and to provide the header text for the report columns. No other adjustments are needed to the report and unused columns are simply Null automatically.

Regards,
Joe
Go to the top of the page
 
isladogs
post Jul 12 2018, 12:10 PM
Post#15



Posts: 636
Joined: 4-June 18
From: Somerset, UK


Thanks Joe

So basically a ranking query but used to order the columns?
Do you have a sample I can look at?

And have you looked at the example I provided in that link?
I've used my approach for many years & it has worked well for many reports some of which can vary from less than 10 to over 70 columns.
In some cases I also adjust the column width and font name / font size to ensure everything fits nicely in the available space

Attached are screenshots of 3 of these including one low res image with colour formatting.
NOTE: The data is not real - from a DEMO database for an imaginary school
This post has been edited by isladogs: Jul 12 2018, 12:23 PM
Attached File(s)
Attached File  ExamResultsCrosstab1.PNG ( 101.06K )Number of downloads: 3
Attached File  AttendaceCrosstabReport.PNG ( 51.75K )Number of downloads: 1
Attached File  Exam6StudentResidualGrid.gif ( 121.23K )Number of downloads: 2
 
Go to the top of the page
 
LagoDavid
post Jul 12 2018, 01:23 PM
Post#16



Posts: 446
Joined: 12-October 03
From: Texas


Yes Joe, thank you so much!
I was in the middle of trying to understand a much more complicated approach I found from Microsoft. I will still try to understand it, but later. It has lots of VBA in the various report events but looks like it will also work. For now, I need to keep it simple.

I have run into a problem though. Your revision worked the first attempt, but when I went in and changed the report parameters it is now giving me an error. The error says that [5] (or [3] or [6] etc) is not recognized as a valid field name or expression. The number that it throws is the first empty column. If there are three columns of data, it throws [4] is not recognized...

Thank you to both of you again.

Isladog, I see you have responded but I have not yet read (studied) your example. I misunderstood the link apparently because I did not see any examples the first time I looked. I will review it.
Go to the top of the page
 
LagoDavid
post Jul 12 2018, 01:41 PM
Post#17



Posts: 446
Joined: 12-October 03
From: Texas


Joe,
the error is occurring because the changes I am making to my cross tab query (to add the In (1,2,3,etc)) are getting deleted when I run the set up form. I have no idea what is going there. I put the IN clause in the SQL statement and then save the query, and then later the IN clause is magically gone. This is happening in both my database and the revision 3 you sent back.
Go to the top of the page
 
RJD
post Jul 12 2018, 02:32 PM
Post#18


UtterAccess VIP
Posts: 9,148
Joined: 25-October 10
From: Gulf South USA


I am not at a computer right now but will look at this later. I suspect you did not change the code that creates the crosstab query. You should look at that first and make sure the In part has been added.

I only looked at the existing query and did not realize you were creating a new query each run.

Joe

from phone
Go to the top of the page
 
LagoDavid
post Jul 12 2018, 03:08 PM
Post#19



Posts: 446
Joined: 12-October 03
From: Texas


Y'all please forgive me for this last comment (and likely for many others). I had forgotten that I am building the cross tab query in VBA and not in the query design window. So I was changing the query in the design window and my code was overwriting it when it ran. I do so many dumb things with this database stuff...

This is my first effort ever to build tables and queries in VBA and I just am not in the habit of looking at code to modify a query.

Thank you both again for your absolutely wonderful help.

And Isladog, my zipped database has Joe's original coding for the report and for the cross tab query. I built the query AllSampleResults. Joe built the qryDatesInRange and the qrySequence and modified the cross tab query and report several weeks ago to make it dynamic. I changed the process as much for a learning experience as anything else. and then until Joe rescued me again, I could not make it work.

Also though, the report was very slow to build, and I hoped it might speed it up to use temporary tables; and I got to practice building tables and queries in VBA. The report is significantly faster now. I don't know if there is some other "penalty" for creating and deleting temporary tables, but the report is much faster now.

Now I need to modify the events so the report is actually executed and then modify the events so different report templates can be used. so you will very likely see more upcoming questions...
Go to the top of the page
 
LagoDavid
post Jul 12 2018, 03:09 PM
Post#20



Posts: 446
Joined: 12-October 03
From: Texas


Our replies just crossed in the air.
You are so right.
Go to the top of the page
 
2 Pages V  1 2 >


Custom Search


RSSSearch   Top   Lo-Fi    13th November 2018 - 12:14 AM