Used MSysObjects for table names and fields, but need to filter report

[mvital]

I have two combo boxes. One contains the table names in my database and the other contains the field names for each table. The combo boxes are synchronized so that you get a list of the field names depending on your table selection.

What I want to do next is in Report Print Preview is allow the user to select one or more fields to display on the report. He/she can have a report displaying all the fields or just a few.

I am just getting into developing and can't put the all together just yet. Any hints/tips would be great!

 

[IanAble]

Applying filters to reports doesn't work. Create queries/views for the report and any subreports and use your selection form to alter query/view SQL property at run-time. Then open the report.

The following allows you to manipulate simple SELECT queries

...
    SQL=[Object SQL Property]
    SQLParts = Replace(SQL, ";", "")
    SQLParts = Split(SQLParts, vbCrLf)
    For Each SQLPart In SQLParts
        Select Case intPart
            Case 0
                strSelect = SQLPart
            Case 1
                strFrom = SQLPart
            Case Else
                If InStr(SQLPart, "ORDER BY") > 0 Then
                    strOrderBy = SQLPart
                ElseIf InStr(SQLPart, "WHERE") > 0 Then
                    strWhere = SQLPart
                End If
        End Select
        intPart = intPart + 1
    Next SQLPart
...
Manipulate SELECT, FROM, WHERE, and ORDER BY strings
...
[Object SQL Property]=strSelect & vbCrLf & strFrom & vbCrLf & strWhere & vbCrLf & strOrderBy

The same trick could be adapted for other SQL statements.

 

[mvital]

Thanks for responding IanAble. I have several filters in the report that work here is some of the code I'm using.

' Build criteria string from lstAnalyte listbox
For Each varItem In Me.lstAnalyte.ItemsSelected
strAnalyte = strAnalyte & ",'" & Me.lstAnalyte.ItemData(varItem) _
& "'"
Next varItem
If Len(strAnalyte) = 0 Then
strAnalyte = "Like '*'"
Else
strAnalyte = Right(strAnalyte, Len(strAnalyte) - 1)
strAnalyte = "IN(" & strAnalyte & ")"
End If

In this case depending on the analyte the user chooses the report is filtered by it and it only shows those results for that analyte in the print preview of the report. After showing it to the client, he wanted to be able to select which fields to display in the report as well.

That's when I used the MSysObjects table to get the field names for the table. Now I just can't get the report to just chose the appropriate fields based on the user selection. Oh yeah, it would have to be a simple multi-select box. Aaaagggh...I am just too new of a developer to put the code together.

thanks!

 

[MichaelRed]

to really build a flexible 'reporting' mechanisim of the type you seem to be after, you probably need to actually build a (new) report in code each time you run the process. There are examples of dynamicallt building reports in code in these fora. Using "advanced" search with the keyword(s) [CreateReport | CreateForm], you should find a few. The keywords operate in an essientially identical manner, I just don't remember which is used in teh actual examples.

The example(s) here are relatively simplistic. and only build something similar to what the Ms. Wiz would provide for a columnar form. On the otherhand, most of the difficult concepts are well illustrated.



MichaelRed

 

[TheAceMan1]

How are ya mvital . . .

Using a form as the filter perhaps this:

How to Filter a Report Using a Form's Filter

See Ya! . . . . . .