Solved

Filter Report Based On Multiple Dynamic Combo boxes

Posted on 2009-05-17
11
614 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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

895 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

14 Experts available now in Live!

Get 1:1 Help Now