Solved

Filter Report Based On Multiple Dynamic Combo boxes

Posted on 2009-05-17
11
620 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

 

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
 
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

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

Question has a verified solution.

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

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
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…

691 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