Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Jerusalem, Israel
    Posts
    708
    Thanks
    0
    Thanked 1 Time in 1 Post

    Dynamic crosstab report AGAIN (XP/2K)

    I was thinking possibly of a different approach to the perennial problem of dynamic crosstab reports.
    IF you create a form in datasheet view then it would be the equivalent of a crosstab query with the additional advantage of the control of a form. You cannot however create the from based on the crosstab query and save it since the rows on the form are saved when you save the form as text boxes and they would not change as the data in the crosstab changes.
    BUT, if you could write a routine that would automatically create a datasheet view form based on the crosstab every time, then save the form and manipulate the printing of the form that might work. Then each time you would erase the created form and recreate it.
    Is that a logical approach to the issue? Has it been tried before?
    Thanks for insights into the approach before I start coding.

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Dynamic crosstab report AGAIN (XP/2K)

    In theory that approach should work - but the devil is in the details I suspect. It has implications for security, since the user must be able to create a new form, and you will want to be sure that each user has their own front-end. Otherwise two users could clobber each other. In addition, Access 2000/XP won't let you change a form design if sombody else is in the database. But, with those caveats, have at it - we would be curious to know the end result.
    Wendell

  3. #3
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Jerusalem, Israel
    Posts
    708
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Dynamic crosstab report AGAIN (XP/2K)

    I 'will try it tonight hopefully and keep you up to date, and get everyone's help as usual.
    Thanks

  4. #4
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Jerusalem, Israel
    Posts
    708
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Dynamic crosstab report AGAIN (XP/2K)

    I have tried the following and it seems to work:


    Private Sub cmdacCmdNewObjectForm_Click()

    Dim frm As Form
    Dim ctlLabel As Control, ctlText As Control
    Dim rst As Recordset
    Dim intNumFields As Integer
    Dim strName As String
    Dim i As Integer

    ' Create new form with and recordsource.
    Set frm = CreateForm
    frm.RecordSource = "qersteel"

    Set rst = New Recordset
    Set rst.ActiveConnection = CurrentProject.Connection
    rst.CursorType = adOpenKeyset
    rst.LockType = adLockReadOnly
    rst.Open ("qersteel")
    intNumFields = rst.Fields.Count

    For i = 0 To intNumFields - 1
    strName = rst.Fields(i).Name

    ' Create unbound default-size text box in detail section.
    Set ctlText = CreateControl(frm.Name, acTextBox, , , strName)
    ' Create child label control for text box.
    Set ctlLabel = CreateControl(frm.Name, acLabel, , _
    ctlText.Name, strName)
    Next i

    ' Restore form.
    DoCmd.Restore
    DoCmd.OpenForm frm.Name, acFormDS

    Since I am doing this a datasheet I do not need to worry about spacing the controls.
    I now could use some help, in printing it out in landscape as a datasheet.
    Any other ideas or issues would be greatly appreciated. Obvioulsy there is no error checking built in yet.
    This could be set up with a form to choose which crosstab query you choose to use.

  5. #5
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Jerusalem, Israel
    Posts
    708
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Dynamic crosstab report AGAIN (XP/2K)

    Interms of security and multiple users what I am working on is very small for a single user. Later ?I can try to figure out the other aspects.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Dynamic crosstab report AGAIN (XP/2K)

    You might find the following commands useful:

    RunCommand acCmdPageSetup
    RunCommand acCmdPrintPreview
    RunCommand acCmdPrint

    Access 2002 VBA hase new options for setting the printer etc., but they are not available in Access 2000.

  7. #7
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Jerusalem, Israel
    Posts
    708
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Dynamic crosstab report AGAIN (XP/2K)

    Should I try dealing with PrintDev or that is overkill.
    Any comments on the basic approach?
    Thanks

  8. #8
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Jerusalem, Israel
    Posts
    708
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Dynamic crosstab report AGAIN (XP/2K)

    BTW there is a typo in the help topic. The example is the way you sent it, but it is missing an R. In the constant list it is spelled:"acPRORLandscape".

  9. #9
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Jerusalem, Israel
    Posts
    708
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Dynamic crosstab report AGAIN (XP/2K)

    If I set the orientation with printer.orientation where does it take effect. I.e. If I put that line then docmd.Printout would that be enough to make sure that it comes out Landscape. USing Runcmd accmdpagesetup , I do not think ou can then tell Access what setup you want?

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Dynamic crosstab report AGAIN (XP/2K)

    Yes, there is a typo is in my reply. At least, my version of Access (2002 SP-2) recognizes acPRORLandscape but not acPROLandscape. The example in the help is wrong, but the list of constants is correct.

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Dynamic crosstab report AGAIN (XP/2K)

    Why don't you experiment. I really don't know more about this than you do.

  12. #12
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Jerusalem, Israel
    Posts
    708
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Dynamic crosstab report AGAIN (XP/2K)

    I will let you know what happens

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Dynamic crosstab report AGAIN (XP/2K)

    frm.Printer.Orientation = acPROLandscape is probably sufficient to set landscape orientation.
    You'll just have to try and see if the approach you chose works for you.

    Added
    The above example is straight from the Access help file, but it is incorrect. It should be acPRORLandscape.

  14. #14
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Jerusalem, Israel
    Posts
    708
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Dynamic crosstab report AGAIN (XP/2K)

    I tried both options of the following code:

    DoCmd.OpenForm "temp", acFormDS
    Printer.Orientation = acPRORLandscape
    'DoCmd.PrintOut , , , acDraft

    I also tried it with form.orientation=acPRORLandscape

    Neither did anything.
    I see I amy need DevMode.
    Getz etc. wrote a class to wrap it in. I think I will use that

    BTW we all think that you know everything!!

  15. #15
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Dynamic crosstab report AGAIN (XP/2K)

    >> BTW we all think that you know everything!!

    That is rubbish. There is a lot in Access, let alone other Office apps, that I don't know anything about.

    Anyway, if you use Printer.Orientation without specifying the form, you refer to the Printer property of the Application object. You should refer to the Printer property of the form:

    Forms("temp").Printer.Orientation = acPRORLandscape

Page 1 of 2 12 LastLast

Posting Permissions

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