Solved

Filter Report Based On Multiple Dynamic Combo boxes

Posted on 2009-05-17
11
613 Views
Last Modified: 2013-11-29
Hi there,

I'm creating a form that will allow users to open a multitude of reports. I've created a table (tblReports) that stores ReportID, Title, cmdObject (Report / Query name to open), etc.

I've now creating another table (tblReportFilters) to store all the filters a particular report can have, using these fields:

ReportFilterID, ReportID, txtLabel, txtControlSource, txtRowSource, intColumnCount, txtColumnWidths

I've setup my reports form with the Parent form bound to tblReports.

Subform 1 shows extra info on selected report on Parent form and is also bound to tblReports.

Subform 2 (is nested on Subform 1) as is bound to tblReportFilters.

See Diagram attached.

tblReportFilters is setup to store all the information I need to populate the comobo box that will filter the data on the selected report.

Using the code shown below on the OnCurrent event of Subform 1, I'm dynamically changing the comobo boxes in Subform 2 to show the correct report filters available for any given report.

There is just one problem, whatever the rowsource for the last comobox is, that is the the only rowsource that becomes available for ALL comoboboxes.

Am I setting things up in the wrong way for what I want to do?


Private Sub Form_Current()

Dim rs As Recordset

 

 

    Set rs = Me.frmReports_Filters_subfrm.Form.RecordsetClone

    

    '   Prepare all combo box filters

    

    

    Do While Not rs.EOF

            

        With Me.frmReports_Filters_subfrm.Form

    

            .cboControlSource.RowSource = Nz(rs!txtRowSource)

            .cboControlSource.ColumnCount = Nz(rs!intColumnCount)

            .cboControlSource.ColumnWidths = Nz(rs!txtColumnWidths)

            .cboControlSource.Requery

            

        End With

        

        rs.MoveNext

            

    Loop

    

    

    

 

End Sub

Open in new window

ReportsForm.JPG
ReportsFormDesign.JPG
0
Comment
Question by:CuriousOne1
  • 5
  • 5
11 Comments
 

Accepted Solution

by:
CuriousOne1 earned 0 total points
ID: 24409129
I just found quite a good article on what I think the real problem is here.

It comes down to setting up a Combo Box With A Variable Row Source In A Continuous Form (http://metrix.fcny.org/wiki/display/dev/Continuous+Forms--Combo+Box+with+Variable+Row+Source)

Trying to implement suggestions in article now.

0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 24416471
If you are trying to create "Cascading Comboboxes" in a continuous form here, then this may prove difficult.

Every "workaround" I have seen so far, has drawbacks.

Keep us posted in any event...

;-)

JeffCoachman
0
 
LVL 39

Assisted Solution

by:thenelson
thenelson earned 500 total points
ID: 24419007
Reading over your info, I understand that you created a combo box on a continuous form and you would like each Combo box displayed for each record to have a different row source, column count and column width. Unfortunately, as you found out, you cannot do that because it is only one combo box displaying different data for each record. One combo box can have only one row source, column count and column width.

You can change the row source, column count and column width of the combo box in the on focus event of the combo box. this way, each time the combo box gets focus, the properties change and the dropdown displays the way you want.

However, if you change the row source property, any combo box with an entry that is not in the now source will not display. So to resolve this issue, we put a textbox on top of the combo box that will display the combo box entry. The query for the form creates the entry for this textbox. In the metrix.fcny.org example you found, the author places the textbox next to a shortened combo box. This causes the drop down of the combo box to be offset. When I have done this, I place the textbox on top of the combo box so it just covers up the text portion (everything except the down arrow) of the combo box. In the on focus event of the textbox, change the focus to the combo box. With this, everything will look like a normal combo box.

I could make a demo database for you to look at if you need.

Hope this helps.
Nelson
0
 

Author Comment

by:CuriousOne1
ID: 24427449
Nelson, could you please provide a basic demo database. It would be helpful.
0
 

Author Comment

by:CuriousOne1
ID: 24428858
Okay, I did get it working now. However, to make the DLookup lookup different values for each filter I had to add 3 more fields to the tblReportFilters table (txtFiledName, txtTableName, txtBoundFiledName).

Then I setup the text field behind the combo box with the following formula: :

txtFilter = IIf(IsNull([cboFilter]),"",DLookUp([txtFieldName],[txtTableName],[txtBoundFieldName] & "=" & [cboFilter]))

And using your suggestion Nelson to put the textbox on top of the combo box makes it display as normal.

Thanks guys, on with the next part of this project.
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 39

Expert Comment

by:thenelson
ID: 24435975
>Nelson, could you please provide a basic demo database. It would be helpful. <

Take a look at: http://www.thenelson.name/   Report Form Tricks    Changing Combobox
0
 
LVL 39

Expert Comment

by:thenelson
ID: 24460945
You're welcome.  Glad to help and thank you very much for the points with "A" grade!

Happy Computing!

Nelson
0
 

Author Comment

by:CuriousOne1
ID: 24464567
Thanks for the example. I think it will work fine for cases where there is the combo boxes depend on data from a single table.

And one last problem I had to deal with, as I was dealing with fields of different data types was to slightly change the Dlookup function depending on whether data was numeric or not.


txtFilter =IIf(IsNull([cboFilter]),"",IIf(IsNumeric([cboFilter]),DLookUp([txtFieldName],[txtTableName],[txtBoundFieldName] & "=" & [cboFilter]),DLookUp([txtFieldName],[txtTableName],[txtBoundFieldName] & "=" & "'" & [cboFilter] & "'")))

Open in new window

0
 
LVL 39

Expert Comment

by:thenelson
ID: 24466412
>I think it will work fine for cases where there is the combo boxes depend on data from a single table. <

In one database, I use that technique on a combobox that depends on 4 tables.

But glad you found a solution.
0
 

Author Comment

by:CuriousOne1
ID: 24466425
sorry, I mean that the combo boxes all have the same rowsource data. I need to refer to different rowsource data for each combo box.
0
 
LVL 39

Expert Comment

by:thenelson
ID: 24466462
Yep, done that too.
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now