Solved

Filter Report Based On Multiple Dynamic Combo boxes

Posted on 2009-05-17
11
615 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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 

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

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

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

Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
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…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

776 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