none
MS-Access Creating a dynamic report

    Question

  • I have another question, is there a way to make reports dynamic? can I make a generic report and  choose a certain contact and only get back their data for a report instead of having all these queries??

    See I have a table with managers and I want reports based on certain criteria but I want to infuse the managers name into the where clause so I only get back the manager selected employees?

    I don't want 20+ queries based on the manager, can I do this and make reports dynamically?


    shawnrye

    Wednesday, July 24, 2013 2:48 PM

Answers

  • I have another question, is there a way to make reports dynamic? can I make a generic report and  choose a certain contact and only get back their data for a report instead of having all these queries??

    The best way is to open the report from a dialogue form which includes a combo box or list box in which to select  the person concerned.  You'll find an example as MultiSelect.zip in my public databases folder at:

    https://skydrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

    This little demo file is more flexible in fact as it uses a multi-select list box, so one or more people can be selected and the report opened filtered to the one or more people selected.  Take a look at the frmEmployeeDlg form, which is the one opened from the first button on the opening form.  In the Click event procedure of the  button to open the report you'll find the code which filters the report to the selected people.

    One thing to note is that the list box's bound column is the hidden EmployeeID column, not the names you see in the list.  The report is therefore filtered on these distinct numeric values.  This is necessary with personal names as these can legitimately be duplicated, and not uncommonly are, even in small populations (I worked with two Maggie Taylors).  People should always be given a distinct numeric ID therefore, to identify them unequivocally.


    Ken Sheridan, Stafford, England

    Wednesday, July 24, 2013 10:38 PM

All replies

  • Where do you store your SQL queries for report

    Thanks and Regards, Prajesh Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Wednesday, July 24, 2013 2:58 PM
  • I have another question, is there a way to make reports dynamic? can I make a generic report and  choose a certain contact and only get back their data for a report instead of having all these queries??

    See I have a table with managers and I want reports based on certain criteria but I want to infuse the managers name into the where clause so I only get back the manager selected employees?

    I don't want 20+ queries based on the manager, can I do this and make reports dynamically?

    shawnrye

    Hi shawnrye,

    There are a couple of ways to do that.

    One is to supply a filtername and a where condition as arguments in the OpenReport command.

    Another way - and that is my favorite - is to pass the complete SQL-string to the OpenReport command through OpenArgs, and assign this string to the RecordSource of the report in the Open or Load event.

    Imb.

    Wednesday, July 24, 2013 6:47 PM
  • How would I do that?

    I am new to ms-access.


    shawnrye

    Wednesday, July 24, 2013 7:06 PM
  • How would I do that?

    I am new to ms-access.


    shawnrye

    Hi shawnrye,

    The easiest way - as a starter - is to go for my first option: read carefully your Help on the OpenReport method. And then, following the example, try it.

    Imb.

    Wednesday, July 24, 2013 8:36 PM
  • I have another question, is there a way to make reports dynamic? can I make a generic report and  choose a certain contact and only get back their data for a report instead of having all these queries??

    The best way is to open the report from a dialogue form which includes a combo box or list box in which to select  the person concerned.  You'll find an example as MultiSelect.zip in my public databases folder at:

    https://skydrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

    This little demo file is more flexible in fact as it uses a multi-select list box, so one or more people can be selected and the report opened filtered to the one or more people selected.  Take a look at the frmEmployeeDlg form, which is the one opened from the first button on the opening form.  In the Click event procedure of the  button to open the report you'll find the code which filters the report to the selected people.

    One thing to note is that the list box's bound column is the hidden EmployeeID column, not the names you see in the list.  The report is therefore filtered on these distinct numeric values.  This is necessary with personal names as these can legitimately be duplicated, and not uncommonly are, even in small populations (I worked with two Maggie Taylors).  People should always be given a distinct numeric ID therefore, to identify them unequivocally.


    Ken Sheridan, Stafford, England

    Wednesday, July 24, 2013 10:38 PM