Dynamic Chart in Access ?

[dphan]

Hi all, I m wondering if it is possible to dynamically generate a chart in a report, using some criteria-s selected on a form.

Basically i have a couple of tables with the following information.

Table1
Employee Department1
Employee Department2
....
...
...

Table2
Employee Skill1
Employee Skill2
..
..
..

Table3
Employee Location1
...
...

This informatino will be displayed on a form, which the user can choose one or more of . Then based on the selection, I will have to query for the correct department, skill, location... then generate a access chart to graphically represent this. Is this possible ? Im' still new to Access, so any input will be appreciated. Thank you.

 

[FancyPrairie]

One way to do it is to set the recordsource of your graph to a query. Then, when the user selects the criteria he/she wants, create the SQL statement, via code, and then delete the query and recreate it using the new SQL statement. All done prior to opening the report. Creating the SQL statement and deleting/creating query would look something like this:

    Dim dbs As DAO.Database
    Dim qdf As DAO.QueryDef

    Dim strSQL as String

    On Error Goto ErrHandler

    strSQL = "Select * From ..."

    set dbs = CurrentDb

    dbs.QueryDefs.Delete "NameofYourQuery"                      'Delete Query
    Set qdf = dbs.CreateQueryDef("NameofYourQuery", strSQL)
    DoEvents

ExitProcedure:

    Exit Function

ErrHandler:

    If (Err.Number = 3265) Then         'IFT, tryed to delete a query that did not exist.
        
        Resume Next

    Else

        Msgbox Err.Description
        Resume ExitProcedure

    End If