Creating a report from a form

[sabre1]

I am trying to create a report from a form where a user can decide which fields they would like to show in a report. The form would contain check boxes and list boxes so the user can also filter on fields and for instance only show the active accounts instead of all accounts. Can someone please point me in the right direction of how i can do this.


Thanks.

[ajetrumpet]

you know, you might need to create the query through code with this kind of complexity. something like:

button on_click()

on error resume next

currentdb.querydefs.delete "myquery"

dim qry as dao.querydef
dim activeaccts as boolean
dim sql as string
dim temp as string
dim i as integer

temp = ""

if me.activeaccountsCbox = -1 then
   activeaccts = true
end if

   for i = 0 to me.ListBoxFields.listcount - 1
      if me.listboxfields.column(0, i).selected then
         temp = temp & me.listboxfields.column(0,i) & ","
      end if
   next i
              temp = iif(temp = "", "", left(temp, len(temp) - 1))

sql = "SELECT "
sql = sql & iif(temp = "", "* ", temp) & " "
sql = sql & "FROM table "
sql = sql & iif(activeaccts = true, "WHERE [activeaccounts] = -1", "")
sql = trim(sql)

currentdb.createquerydef("myquery", sql)

refreshdatabasewindow

docmd.openreport "myreport", acViewPreview

that returns all records if no fields are selected. If the report is based on that query, it can be dynamic with code similar to that.

[NTC]

generically speaking a Report object has a record source - and the controls on the Report are fixed. One typically is managing the record set within the record source - - - not the Controls on the Report.

You can toggle on/off visibility of controls on the Report. But you can't really make new controls appear. So in terms "decide which fields they would like to show in the report"...you would toggle on/off visibility of controls based on the form selection via the visibible property - - and trigger it in the OnFormat Event of the section that the controls appear.

Hope this helps.