Results 1 to 11 of 11
  1. #1
    Join Date
    Aug 2002
    Location
    Charlotte NC
    Posts
    665

    Unanswered: Calling Public Functions

    I know this will probably be an easy question to answer but im new to functions.

    This is my function:

    Public Function Save_Graph(recordSource As String)

    Dim Rpt As Report

    DoCmd.OpenReport "Report:_Save_Graph", acViewDesign
    Set Rpt = Reports("Report:_Save_Graph")
    Rpt!Graph.RowSourceType = "table/query"
    Rpt!Graph.RowSource = recordSource

    Set Rpt = Nothing
    DoCmd.OpenReport "Report:_Save_Graph", acViewPreview

    End Function

    and below is the code i use to call it

    Call Save_Graph("TRANSFORM sum(FFEs) SELECT years FROM graph_ffe GROUP BY years PIVOT Time;")


    When I try to use this i get the error:

    Compile Error: Invalid use of property.

    What exactly is going on here?

    Regards
    Jim

  2. #2
    Join Date
    Jul 2002
    Location
    Romania
    Posts
    122
    For a start I'd change the name of the Time field to something else than a reserved word. You could use fldTime...
    Then I'd try to make a habit out of it...

    Good luck

    Dan

  3. #3
    Join Date
    Aug 2002
    Location
    Charlotte NC
    Posts
    665
    Originally posted by DannyY
    For a start I'd change the name of the Time field to something else than a reserved word. You could use fldTime...
    Then I'd try to make a habit out of it...

    Good luck

    Dan

    Thats not the answer, that peice of code works perfectly fine on my form
    Jim

  4. #4
    Join Date
    Sep 2002
    Location
    South Wales
    Posts
    580

    Unhappy Please clarify

    Call Save_Graph("TRANSFORM sum(FFEs) SELECT years FROM graph_ffe GROUP BY years PIVOT Time;")

    FFEs: Is this a fieldname in a table - which exact table name?

    Years: Is this a fieldname in the same table - which exact table name?

    Time: Is this a fieldname in the same table - which exact table name?

    graph_ffe - This is the name of the TABLE right???
    Windows Server 2003-8 / Terminal Services / SQL 2000 / Access 2003 / Office 2003-7 / Exchange 2003-7 / Blackberry Enterprise Server / AutoCAD / Lambert And Butler / Red Bull

  5. #5
    Join Date
    Aug 2002
    Location
    Charlotte NC
    Posts
    665

    Re: Please clarify

    Originally posted by garethdart
    Call Save_Graph("TRANSFORM sum(FFEs) SELECT years FROM graph_ffe GROUP BY years PIVOT Time;")

    FFEs: Is this a fieldname in a table - which exact table name?

    Years: Is this a fieldname in the same table - which exact table name?

    Time: Is this a fieldname in the same table - which exact table name?

    graph_ffe - This is the name of the TABLE right???
    unfortunatly i had to be a bit more dinamic in my application. graph_ffe is a querydef, basicaly a query created in vba. ffe is the measure in the query, years is a time dimention that is static and time is actualy a variable in the querydef creation that could be weeks, months, or Quarters.
    this same rowsource works great on the form, but im having some major issuses trying to get this to work on the report.......all the help files say that it will work...but u know how good the help files are.

    hope this makes it clearer
    Jim

  6. #6
    Join Date
    Sep 2002
    Location
    South Wales
    Posts
    580

    VBA Query

    Can we see that please?

  7. #7
    Join Date
    Aug 2002
    Location
    Charlotte NC
    Posts
    665

    Re: VBA Query

    Originally posted by garethdart
    Can we see that please?
    hmm it appears that i tried to post a mesg that was over 1000 characters and killed the server....sorry about that. I hae attached a txt document that has the code that runs behind my graph form
    I dont know how much good it will do but there it is
    Jim
    Attached Files Attached Files

  8. #8
    Join Date
    Jul 2002
    Location
    Romania
    Posts
    122

    Re: VBA Query

    Tried this and it worked fine...

    Sub testgraph(strSQL)
    Dim rpt As Report
    DoCmd.OpenReport "Report1", acViewDesign
    Set rpt = Reports!Report1
    rpt!Graph43.RowSourceType = "Table/Query"
    rpt!Graph43.RowSource = strSQL
    DoCmd.Close acReport, "Report1", acSaveYes
    DoCmd.OpenReport "report1", acViewPreview
    Set rpt = Nothing
    End Sub

    Tested it in debug window with this SQL:
    call testgraph("TRANSFORM Sum([MemberID]) AS [SumOfMemberID] SELECT [ID] FROM [Attendance] GROUP BY [ID] PIVOT [AID];")

    But I still suggest that you refrain from using reserved words as field/object names. Same with spaces and special characters...

    Good luck,

    Dan

  9. #9
    Join Date
    Aug 2002
    Location
    Charlotte NC
    Posts
    665

    Re: VBA Query

    Originally posted by DannyY
    Tried this and it worked fine...

    Sub testgraph(strSQL)
    Dim rpt As Report
    DoCmd.OpenReport "Report1", acViewDesign
    Set rpt = Reports!Report1
    rpt!Graph43.RowSourceType = "Table/Query"
    rpt!Graph43.RowSource = strSQL
    DoCmd.Close acReport, "Report1", acSaveYes
    DoCmd.OpenReport "report1", acViewPreview
    Set rpt = Nothing
    End Sub

    Tested it in debug window with this SQL:
    call testgraph("TRANSFORM Sum([MemberID]) AS [SumOfMemberID] SELECT [ID] FROM [Attendance] GROUP BY [ID] PIVOT [AID];")

    But I still suggest that you refrain from using reserved words as field/object names. Same with spaces and special characters...

    Good luck,

    Dan
    You dont have that declared as a function...does that mean that i dont want that in a modual, do i need to put this code in the form?
    Regards
    Jim

  10. #10
    Join Date
    Jul 2002
    Location
    Romania
    Posts
    122

    Re: VBA Query

    Originally posted by JDionne
    You dont have that declared as a function...does that mean that i dont want that in a modual, do i need to put this code in the form?
    Regards
    Jim
    It does not return a value, so why would I declare it as function?
    Anyway, function or sub, it does the same thing, so you can just change 'Sub' to 'Function'.

    It can be located either in the form (but make sure you call it from within the form) or in a module.

    I tested it from a module.

    Good luck,

    Dan

  11. #11
    Join Date
    Aug 2002
    Location
    Charlotte NC
    Posts
    665

    Re: VBA Query

    Originally posted by DannyY
    It does not return a value, so why would I declare it as function?
    Anyway, function or sub, it does the same thing, so you can just change 'Sub' to 'Function'.

    It can be located either in the form (but make sure you call it from within the form) or in a module.

    I tested it from a module.

    Good luck,

    Dan
    OK that did work....but i can only export my graphs to a snapshot file...no rtf or xls. Dose anyone know why this is, if there is a work around, and exactly how usefull snapshots realy are?
    Jim

Posting Permissions

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