Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Filter Report Based On Multiple Dynamic Combo boxes

Posted on 2009-05-17
11
Medium Priority
?
627 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 2000 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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 

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

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

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

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

636 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