Report Form

[Ielamrani]

Hi,
I usually run reports for other department but my boss wanted me to create a form where users can run their own reports. Does anyone know an easy way to create a form where a user can choose a table and criteria (like running a query) and then he/she will have the ability to export it.
I hope this is clear.

Thanks in advance

Ismail

 

[MajP]

There is probably a lot of strategies to do this. It will be interesting to see different approaches. I do not know of any real easy ones, since you always have to meet the users needs.
I usually make a few standard reports, but give the user a robust means to filter and sort the reports before opening the report.

For filtering I usually use a form with many multiple select list boxes. Each list box represents a field. I then build a "where" expression from the choices. Choices within the list box are "or" and between the different list boxes are "and". This logic works often, and you can make pretty complicated filter criteria this way. I often save where criteria to a global variable "glblStrFilter"

The sorting is a little more complicated. I create a reference table that has the report names, descriptive field names, and actual field names. This feeds a form that allows you to pick the fields that you want to sort on, and then order these fields. This looks like the form wizard where you move fields back and forth between two lists, and move the field names up and down to sort. This builds my global sort order.

now i may open something like

Public Sub subOpenReportFiltered(rptName As String, strWhere As String, strOrderBy)
  On Error GoTo errLbl
  DoCmd.OpenReport rptName, acViewPreview, , strWhere
  With Reports(rptName)
       .OrderBy = strOrderBy
       .OrderByOn = True
  End With
  DoCmd.Maximize
  If MsgBox("Would you like to Open this report in MS WORD?", vbYesNo) = vbYes Then
    subOpenInWord rptName
  End If
  Exit Sub
errLbl:
  MsgBox Err.Number & "  " & Err.Description
  Exit Sub
End Sub

Now you can have unlimited permutations of the same forms.

 

[TheAceMan1]

How are ya Ielamrani . . .

It can be done . . . but your talking alot of work and alot more code, at a minimum . . . which means alot of time!! More time than your boss has figured on (I hope this is not time dependent or has a deadline). I've had to do this same thing in the past, twice. Each time was an everlasting nightmare
(most of you work will be done in the report), and very tedious . . . code wise. But in a nutshell, my plan of attack was the following:
[ol][li]Setup a [blue]base report[/blue] that includes [blue]all fields users may ask for[/blue]. The collection of fields used came from [blue]visiting all users[/blue] an inquiring their needs to this end.[/li]
[li]From a seperate form [blue]users select output fields[/blue] for the report. [purple]Your greatest work will be done here, as thru code you'll be hiding & positioning fields to show in the report, as well what fields to include in the reports query.[/purple][/li]
[li][blue]A seperate form to query the tables[/blue].[/li][/ol]
Seems easy enough, but once you get started, how indepth this task is will come to the forefront!

[purple]Your thoughts? . . .[/purple]

See Ya! . . . . . .

 

[Ielamrani]

MajP and TheAceMan1,
I really appreciate you help, and I guess you both are right, it is a big job. For now I am going to try to create reports for them as needed...
Again thank you both for your help