Results 1 to 10 of 10
  1. #1
    Join Date
    May 2006
    Posts
    29

    Unanswered: How to dynamically add Report footer?

    Hi,
    I'm doing a project that I'm able to dynamically create a report, and dynamically create textbox:
    Set rpt = CreateReport

    With rpt
    .RecordSource = strSQL
    .Section("Detail").Height = 500
    .Section("Detail").AlternateBackColor = 15658720
    .Section("Detail").KeepTogether = True
    .Section("Detail").CanShrink = True
    .Section("Detail").CanGrow = True
    End With

    However, I cannot create the report footer in the "With rpt". Do you have any idea how I can do that?

    I actually want to display the total number of records of "Detail section" in the report footer. But I cannot create report footer...

    Thanks!!!!

  2. #2
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Out of curiousity, why do you want to do this dynamically and not just show the report footer in the report and put it in. It would probably be easier to do this and then use a command to set the report footer visible = true or false.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  3. #3
    Join Date
    May 2006
    Posts
    29
    I'm generating dynamic detail section because the number of textboxes will be changed, depending on what the user chooses to see. That's why I used dynamic detail section with dynamic textboxes. Actually, my program deletes and regenerates the detail report every time the user clicks the button. It take a while to regenerate the whole detail report. I'm still thinking a way how to update a report, instead of regenerating it dynamically... Any suggestion?

  4. #4
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Not sure exactly what you're trying to do but I've never had to dynamically generate a report in all the years I've been designing. Unless you need to re-arrange the text boxes on the report (in which I'd actually create separate reports), I guess I'm not really seeing why you need to create the textboxes through code. Could you maybe explain a little more on why you need to do this versus just creating static textboxes? I've just never ever had to do this and don't quite understand why you'd have to but maybe I'm missing something.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  5. #5
    Join Date
    May 2006
    Posts
    29
    The reason I need to have dynamic textboxes is that:
    I have a form with check boxes for users to select what columns they want to see in the report. Since the number of selected checkboxes vary, I use dynamic textboxes on the report.

  6. #6
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Ok. I see now and that makes sense. Have you thought about making a temporary table with fields (such as Column1, Column2, etc...) and then basing a static report off of that table with static fields such as Column1, Column2, etc...(and then maybe another field such as ColumnCount which tells how many fields to make visible/invisible (or create on the report) or something like that - I'd have to think on it though)? Still - I'm wondering if you couldn't do something down that line with creating a temp table (if you aren't doing that already). Just throwing some other options you were wondering about and the way I might think of doing it. Or maybe an embedded sub report shown as a query (and another embedded report with a summarization query off the first query/temp table). The query could be based off of the temp table or have logic in it itself to show/hide fields. Again, throwing a few other options out there.

    But getting to back to your initial question on creating the report footer dynamically...since I haven't created these types of reports dynamically, I guess having a footer and making it visible/invisible doesn't help?

    Give me a few days to do some experimenting and if someone hasn't answered this by then, I'll post what I've come up. It's a good new challenge that I haven't seen in 15+ years of MSAccess programming and sorry I don't have an answer off-hand. Not sure if you have the time to wait for an answer but I'm curious myself.
    Last edited by pkstormy; 08-18-08 at 22:10.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  7. #7
    Join Date
    May 2006
    Posts
    29
    Thanks pkstormy! I actually tried to add to the footer, but then it displays on every page of the report. So what I did was creating a static report, and then put the summary(# of records, etc) in this static report footer.

  8. #8
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    I was thinking that a static report was the only solution but I wasn't sure since I don't really know what you're working with. I was curious about the routine which lets the user select which fields to select to show on the report so I created a small mdb which lets the user select the fields they wanted to show from a form (ie. there are 5 fields in the table and on the form the user picks which fields to show (using checkboxes - they can pick Field1, Field3 or any combination or all of the fields.) The code then creates a temp table with a "Column1", "Column2", etc..(depending on how many fields the user selected and populates the values from the original table.) So for example, if the user picked Field2 and Field4 on the form, a temp table would be created with 2 fields (1 called Column1 and the 2nd field called Column2.) It would then populate Column1 with the values from Field2 in the original table and populate Column2 with the values from Field4 from the original table.

    The report (static) then shows Column1, Column2, etc...depending on how many columns there are (and "Column1", "Column2", etc... is summed in the report footer.) But again, the reports were static with a report footer summing the column fields. For the reports, I went 2 routes - 1 which had 5 reports (1 with 1 column, a 2nd with 2 columns, a 3rd with 3 columns, etc... - where depending on how many fields were selected on the form, after it created the temp table it would show the corresponding report with the right number of columns from the temp table) and then also created another report which had 5 columns and made the fields invisible depending on how many "column" fields there were in the temp table. I didn't want to go through the time though on creating a dynamic report, adding the appropriate fields and I couldn't find code to create a dynamic report footer so all the reports were again, static and based off of the temp table created.

    Not sure if I explained that very well and I hope it makes some kind of sense but it was a fun challenge (nothing fancy but I just wanted to try different methods on doing it.) I'd be curious on seeing how you've done it.
    Last edited by pkstormy; 08-20-08 at 13:38.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  9. #9
    Join Date
    May 2006
    Posts
    29
    Now i'm facing anothe problem. Initially I thought the dynamic report was working. The database is .adp. I'm using Access 2007, but the file format is Access 2000. Then I converted the file to Access 2002/2003 to create the ade file. however, when I tried to create the dynamic report, it gives me this error:

    "The command you specified is not available in an .mde, .accde, or .ade database"

    Do you know how this can be solved?

    If I don't create dynamic report, can I have a static report, with dynamic detail section? But I'm really out of time...

    Thanks!

  10. #10
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Can't help you on this one pchiu (you may want to start a new thread on this though as this might get buried for answers as sometimes some of us just browse new posts to answer.)

    I personally abandoned ADP projects a few years back (as I think Microsoft has also abandoned working on improving ADP.) I found that I could get better results (and write code a lot faster) using mdb (and mde) with unbound forms (I think you'll find many, many unbound mdb form supporters here on this forum.) It was a LOT easier designing mdb's using SQL Server linked tables versus working with ADP.

    Just something to consider. But make this a new thread so hopefully you'll get some faster replies.

    Guessing though, since you converted, I'd guess to say that the command you're using was replaced with a new command as Microsoft sometimes does with newer versions.
    Last edited by pkstormy; 08-20-08 at 14:13.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •