Combobox to filter a y/n field

3,653

Expert Mod 2GB

moishy101,

I would have the RecordSource for the Report be based on a query that uses the values from the Yes/No CheckBox and the ComboBox on the form. Very simple to do in the query design process.

You can establish query parameters that prevent filtering if the ComboBox is empty, also.

Let me know if this helps, or send additional info if required.

Cheers!

Jul 26 '12 #2

I don't want to change the record source of the report, I'm trying to filter using the where clause of DoCmd.OpenReport.

Jul 26 '12 #3

3,653

Expert Mod 2GB

I'm not sure the record source can be changed in a report by itself, because it is technically a static set of data. It is possible to have controls on a form which calculate based on other controls, but I know of no way to change the record source. Perhaps there might be some experts who know how to do it. I would be interested to know if it's possible.....

I guess that is why my recommendation was to have a dynamic query that provided a recordset according to certain conditions and criteria. Then you have the best of both worlds. You never have to mess with the record source on the report, but get the necessary records based on criteria external to the report.

Jul 26 '12 #4

@twinnyfo

It's very simple to change the record source of a report (Me.RecordSource = strSQL, in the on open event).

But as I mentioned that is not how I want it done.

Jul 26 '12 #5

Rabbit

12,516

Expert Mod 8TB

Account for an empty combo box in what way?

Jul 26 '12 #6

If the combo box is empty not to apply that filter (there are other filters).

Jul 27 '12 #7

I tried this but it doesn't seem to filter properly.

Expand|Select|Wrap|Line Numbers

 
    If Not IsNull(cboForSale) Then

        strWhere = strWhere & " And" & " A.ForSale Like " & IIf([Forms]![frmReports]![cboForSale] = "For Sale", True, False)

    End If

Jul 27 '12 #8

On second thought maybe the problem is with the rest of the sub.

Expand|Select|Wrap|Line Numbers

 
    Dim strWhere As String

    strWhere = " A.City Like '" & Nz([Forms]![frmReports]![cboCity], "*") & "'" _

               & " And" & _

               " A.Neighborhood Like '" & Nz([Forms]![frmReports]![cboNeighborhood], "*") & "'"

Would the use of Nz() above show all records (not apply the filter for that field) if the combo boxes are empty?

Jul 27 '12 #9

3,653

Expert Mod 2GB

moishy101,

I'm still trying to understand why it is so critical not to use a query based on the Form you have loaded? In terms of programming, it really is the easiest and best way to do it. Reports aren't really designed to be dynamic, but rather static. The data underneath them can be very dynamic, which is why we design queries that take into account external parameters, such as values in combo boxes on forms.

Is there are particular reason the report absolutely must retain its current record source? Because, based on what you are trying to do, you are changing the record source, without "changing the record source."

An alternate option for this, which, again is form-based and not report-based, is to open the report with its normal record source, then apply a filter using the VBA code from the form. This would do what you want to do, but not how you want to do it.

Additional thoughts?

Jul 27 '12 #10

An alternate option for this, which, again is form-based and not report-based, is to open the report with its normal record source, then apply a filter using the VBA code from the form. This would do what you want to do, but now how you want to do it.

Take a look at the help files on the OpenReport method.

Jul 27 '12 #11

3,653

Expert Mod 2GB

Could you please post the code you have to develop your filter string and the code you use to open your report? Also, are you getting errors or just not the records you want? It would also help to know the recordset behind the form, as this will determine how your filter is going to limit data.

I think this would help me troubleshoot.

Jul 27 '12 #12

twinnyfo

Thanks for your time, I think I got it sorted for now.

Jul 27 '12 #13

by: Dalan | last post by:

The problem to resolve should be pretty easy for someone. I have a main form which contains a combo box to lookup various vendors. Have several reports for the vendors, but it is print all or...

add records to table with combobox ?

by: thomas goodwin | last post by:

I have a form whose record source is a table with fields A and B. The combobox has Field A as its control source. When I type a value into the combobox that is Not in field A, I get a new record...

filter field for report - access97

by: Heather | last post by:

I want to create a report that shows the percent of safety audits complete by each individual shift, based on the safety audit date. The user has to put in the dates they want the report to pull...

problem binding comboBox to dataset

by: Omar | last post by:

When I try to databind my comboBox (specifically field "emplcode") to a filled dataset , the contents of the comboBox displays a bunch of "System.Data.DataRowView". I assume the amount of times...

C# / C Sharp

How to pass a combobox.text field in BOLD type

by: yer darn tootin | last post by:

Hello, I have a method which takes various string values to create an automated email. Anyone know how I can make a particular string value passed appear in bold type in the email? ( below, the...

Visual Basic .NET

subform combobox filter problem

by: bcreighton | last post by:

I have created a bound subform on an unbound masterform linked together with a common field (A store's identification number) using an unbound combobox on the masterform and an invisible field on...

Access 07 ComboBox Filter results category subcategory type SubType

by: gcoaster | last post by:

Hello Everyone! I have a question regarding "cascading combobox(s) list(s)" I would like: ComboBox 2 to show results from ComboBox 1's selection, then ComboBox 4 to show results from...

Restricting combobox to Filtered Form

by: Thelma Roslyn Lubkin | last post by:

I have a form whose rowsource is a single table, i.e. 'Datasystem'. I use a combobox to search for records in that table based on the value of a single field, i.e., 'systemname'. I use a...

Combobox - refresh problem?

by: EManning | last post by:

Using A2003 w/ tables linked to SQL Server. All users have their own copy of the mdb. I have a combobox whose rowsource is a query. This query is based on a table and has a field in it that...

Multiple Combobox Filter with Clear Option

by: aas4mis | last post by:

I haven't had much luck with specific controls, their properties and loops in the past. I thought I would share this tidbit of code, feel free to modify/modularize in any way to suit your needs. This...