473,883 Members | 1,595 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Filter a report based on same filter on a form

Hi,

I have a form based on a table. When I filter the form I want to run a
report based on the same table with the same filter as the form. No problem
until I want to filter a combo box where the text value is on a different
table. The me.filter is then a text instead of the id-number from the lookup
table. This causes the report to prompt for the parameter.

How do I get by this problem? Do I need to create a temporary table? I
rather not. or is there another solution without using filter?
Appreciate any help
Richard
Nov 12 '05 #1
3 6620
Sounds like the combo's bound column is zero width?

You should still be able to use the value from the invisible column in the
WhereCondition for your OpenReport. Just use the Value of the combo.

It is also possible to use its Column() property if necessary. (That's
zero-based.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Richard" <rw**********@c hello.se> wrote in message
news:dMQpb.883$ %W3.1336@amstwi st00...

I have a form based on a table. When I filter the form I want to run a
report based on the same table with the same filter as the form. No problem until I want to filter a combo box where the text value is on a different
table. The me.filter is then a text instead of the id-number from the lookup table. This causes the report to prompt for the parameter.

How do I get by this problem? Do I need to create a temporary table? I
rather not. or is there another solution without using filter?

Nov 12 '05 #2
Yes, that is true. But what I want to do is to use the filter the user sets
in the form, to the report.
For instance. If the user only wants to list clients from the State CA he
filters the form and the property me.filter sets to ((tblClients.St ate =
"CA")). That filter is then used to the report. Everything is working fine
until the user filters that combo. The values are taken from a lookup table
and the me.filter is set to: ((lookup_cboRem arks.remark = "Valid member")).
The lookup table is called tlkpRemarks and when I try to run the report it
asks for the parameter value of lookup_cboRemar ks.remark.

The combo's bound column is zero width like you predicted and the second
column is all the remarks. I don't really want to alter the filter but if I
need to it gets messy because the filter string can get long and
complicated.

I am using Access 2002.
Appreciate any further help
Richard Wernerhjelm
"Allen Browne" <al*********@Se eSig.invalid> skrev i meddelandet
news:3f******** **************@ freenews.iinet. net.au...
Sounds like the combo's bound column is zero width?

You should still be able to use the value from the invisible column in the
WhereCondition for your OpenReport. Just use the Value of the combo.

It is also possible to use its Column() property if necessary. (That's
zero-based.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Richard" <rw**********@c hello.se> wrote in message
news:dMQpb.883$ %W3.1336@amstwi st00...

I have a form based on a table. When I filter the form I want to run a
report based on the same table with the same filter as the form. No

problem
until I want to filter a combo box where the text value is on a different table. The me.filter is then a text instead of the id-number from the

lookup
table. This causes the report to prompt for the parameter.

How do I get by this problem? Do I need to create a temporary table? I
rather not. or is there another solution without using filter?


Nov 12 '05 #3
So your request is that the report can interpret a filter containing:
lookup_cboRemar ks.remark = "some text"

I'm not sure how the filter is being set: filter by form perhaps? Would it
be practical to include the combo's lookup table in the RecordSource of the
form (using an outer join query), so the filter could become:
SomeTable.remar k = "some text"
The report would then be able to filter that correctly (assuming it is also
based on a query that contains the lookup table).

If that can't be done, it may be easier to change the way the filter string
is being generated so that it contains the value from the bound column. The
report would interpret that correctly. Typically I include unbound boxes in
the form header for the fields that user is likely to need to filter on, and
build the filter string programmaticall y. This has the advantage that it
works with mde files and runtimes where the filter-by-form is not available.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Richard" <rw**********@c hello.se> wrote in message
news:ec3qb.988$ %W3.1576@amstwi st00...
Yes, that is true. But what I want to do is to use the filter the user sets in the form, to the report.
For instance. If the user only wants to list clients from the State CA he
filters the form and the property me.filter sets to ((tblClients.St ate =
"CA")). That filter is then used to the report. Everything is working fine
until the user filters that combo. The values are taken from a lookup table and the me.filter is set to: ((lookup_cboRem arks.remark = "Valid member")). The lookup table is called tlkpRemarks and when I try to run the report it
asks for the parameter value of lookup_cboRemar ks.remark.

The combo's bound column is zero width like you predicted and the second
column is all the remarks. I don't really want to alter the filter but if I need to it gets messy because the filter string can get long and
complicated.

I am using Access 2002.
Appreciate any further help
Richard Wernerhjelm
"Allen Browne" <al*********@Se eSig.invalid> skrev i meddelandet
news:3f******** **************@ freenews.iinet. net.au...
Sounds like the combo's bound column is zero width?

You should still be able to use the value from the invisible column in the
WhereCondition for your OpenReport. Just use the Value of the combo.

It is also possible to use its Column() property if necessary. (That's
zero-based.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Richard" <rw**********@c hello.se> wrote in message
news:dMQpb.883$ %W3.1336@amstwi st00...

I have a form based on a table. When I filter the form I want to run a
report based on the same table with the same filter as the form. No

problem
until I want to filter a combo box where the text value is on a

different table. The me.filter is then a text instead of the id-number from the

lookup
table. This causes the report to prompt for the parameter.

How do I get by this problem? Do I need to create a temporary table? I
rather not. or is there another solution without using filter?

Nov 12 '05 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
6477
by: CSDunn | last post by:
Hello, I have a problem with field filtering between an Access 2000 Project form (the application is called CELDT), and the report that shows the results of the filter. Both the form and the report are based on the same View addressed in the Record Source of both as 'dbo.CLMain_vw'. The View resides in a SQL Server 2000 database. There are two different problems I am having with filtering: The first involves filtering in the form by date...
4
5144
by: Nhmiller | last post by:
This is directly from Access' Help: "About designing a query When you open a query in Design view, or open a form, report, or datasheet and show the Advanced Filter/Sort window (Advanced Filter/Sort window: A window in which you can create a filter from scratch. You enter criteria expressions in the filter design grid to restrict the records in the open form or datasheet to a subset of records that meet the criteria.), you see the design...
1
16408
by: lorirobn | last post by:
Hi, I have a report that works just fine. Now I would like to add the capability to choose selection criteria to limit what is displayed. I created several reports that do this, but they used VBA to create SQL statements, then apply them. Now that I have been using Access/VBA for awhile, I am wondering if there is a simpler way to do this than coding these long sql statements. My report has a query as its record source. Can I just...
1
3032
by: mpmason14 | last post by:
how can i create the title for my report based on the filtered criteria in a form? the form opens the report and i want the report to have the title of what was filtered. the user can filter among many things (usually only one, though) on the form, and i would like to get whatever was filtered on the report as the title i have the basics down, like having an unbound text box and having = in there to show what is being filtered, but i want...
0
1900
by: Josetta | last post by:
This is for informational purposes...I had a problem and I thought it might help others in a similar situation. I hope someone, someday, finds this idea useful. I've garnered so much knowledge from this forum over the years, it's nice to present a solution that I couldn't find here. I have a form that my users wanted to filter, using the right-click Filter By or Filter Excluding, etc. I have a check box which my reports look up,...
3
2273
by: emgallagher | last post by:
I have a form which lists studies. People can filter the form based on details about the study, such as the study type. Currently users filter via the right click method. I would like to be able to have a report that shows just the filtered records. The code that I tried which didn't work:
5
2179
by: Whasigga | last post by:
Hi guys I've got a report that I have created. It is based off of a query that I made. I use it to show different combinations of data, but its the same type of information... It is called from 5 buttons on a form. each of the buttons opens the report to show something different, most are just the time frame of the data, whether it be 1 day, 1 week, 1 month, 1 year... Each one uses the doCmd.OpenReport method, and then passes a Where...
5
4026
by: DAHMB | last post by:
Hi all, Using Access 2007 I have a report called Sunday School Attendance based on a Query called qryAttendance the query is as follows: SELECT tblSundaySchoolAttendance.StudentID, tblSundaySchoolAttendance.AttendanceDate, . FROM tblSundaySchoolAttendance INNER JOIN ON tblSundaySchoolAttendance.StudentID=.StudentID;
3
2682
by: franc sutherland | last post by:
Hello, I have a report which I filter using the me.filter command in the OnOpen event. Me.Filter = "OrderID=" & Forms!variable_form_name! Me.FilterOn = True I want to be able to open that report, filtered, from different forms. How do I carry the name of the current form into the
0
9792
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
11148
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
1
10857
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
10419
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
9577
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
5801
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
5994
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4618
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
4223
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.