473,327 Members | 1,896 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,327 software developers and data experts.

Report the current filtered records from a Form (in an ADP project)

14
---Report the current filtered records from a Form (in an ADP project)---

Hello All,

I've seen this topic discussed before, but the solution described then doesn't work in my particular case.

My Config: Access 2002 front-end using SQL Server 2000 (MSDE actually) via ADP/ADE Access Data Project.

I have a form (containing about 80 fields) on which I allow the user to apply field/form filters -- this works fine.

The form has a <print report> button which should trigger a report based on that filtered recordset -- this does not work.

I've tried the following VBA code in the button's click event handler, which I've read should work in plain Access, but fails for me because I'm using SQL Server at the back-end.

Expand|Select|Wrap|Line Numbers
  1.         Dim strWhere As String
  2.         strWhere = ""
  3.         If Me.Dirty Then
  4.             Me.Dirty = False
  5.         End If
  6.         If Me.FilterOn Then
  7.             strWhere = Me.Filter
  8.         End If
  9.         DoCmd.OpenReport "myreport", acPreview, , strWhere
  10.  
The above code passes the form's current filter as a parameter to OpenReport, which fails with various SQL syntax errors because Me.Filter contains a SQL "where" clause (without the word "where), but it is in Access/Jet SQL format, not SQLServer's SQL format. It contains quotes instead of apostrophes around strings, uses "=True" for YES/NO fields instead of "=1" or "=-1" for SQLServer's BIT type fields, etc. This is strange because Access knows that it's using a SQLServer backend (it's a .ADP project).

Ideally I'd like to know how to pass the actual recordset to the report (instead of the above attempt at having the report re-query the db), but this doesn't seem possible?

A second-best solution I guess would be a way to retrieve the Me.Filter value in SQLServer's format, or call a function which does that for me.

Perhaps there are completely different approaches/solutions?

ANY help would be VERY MUCH appreciated! Please don't assume any idea is too obvious to suggest -- while I'm a 20-year coding veteran, I'm VERY new to Access and VBA.

A happy bonus to a solution would be to be able to pass the current sort-ordering of the form to the report as well.

Thank you very much,

-radio1
Oct 27 '06 #1
16 3018
MMcCarthy
14,534 Expert Mod 8TB
In a reports on open event you can reset the recordsource

Expand|Select|Wrap|Line Numbers
  1.  
  2. Private Sub Report_Open(Cancel As Integer)
  3.     Me.RecordSource = "Some SQL string"
  4. End Sub 
  5.  
  6.  
Oct 28 '06 #2
MMcCarthy
14,534 Expert Mod 8TB
To get the sql string you could create a global variable of a string and populate it elsewhere.
Oct 28 '06 #3
radio1
14
Thanks very much for the reply, mmccarthy, but (and please forgive me if I'm incorrect!) I don't think your suggestion addresses my core problem.

In my ADP project, I'm trying to Report the data that is current in a Form -- by current I mean including any field/form filtering the user has done.

The code example I've shown tries to determine what the current user filters are via the Me.Filter variable from the Form.

The problem is that the Me.Filter string (essentially a sql WHERE clause without the word "WHERE") is in Access/Jet's sql syntax, but apparently needs to be in SQLServer's syntax to be used by the Report, and I think that would still be the case utilizing your suggested technique.

Sorry for the "think" part of this reply, but I'm not at the right computer to try out your idea today or probably tomorrow, and I wanted to at least acknowledge your reply as soon as I could.

By the way, some nice things are said about you in this other thread I was involved in: http://www.thescripts.com/forum/thread555184.html

Thank you very much again for your input,

-radio1
Oct 28 '06 #4
NeoPa
32,556 Expert Mod 16PB
SQL Server will tend to conform more closely to the ANSI standards of Structured Query Language.
This is configurable in SQL server and the 'standard' type strings, using single-quotes ('), should also work in Access.
I suggest that you use these string terminators in all your SQL, especially if you plan to cross between any two.

Also, and I believe this holds true for Transact-SQL or T-SQL - (SQL Servers native SQL), booleans may be checked simply by referencing them. Unlike other values, they need not be compared to anything to return a logical result. They are already a logical result so don't need anything more. This should help to avoid differences between the two types.

EG.
Expand|Select|Wrap|Line Numbers
  1. WHERE (blnMyBoolean)
  2. instead of
  3. WHERE (blnMyBoolean = True)
Oct 28 '06 #5
MMcCarthy
14,534 Expert Mod 8TB

Thanks very much for the reply, mmccarthy, but (and please forgive me if I'm incorrect!) I don't think your suggestion addresses my core problem.
You're forgiven. <grin> Flattered as I am by some of the appreciate comments I make plenty of mistakes just don't tell any of my clients.

I've learned the hard way just like most programmers and you'll pick up plenty of coding tips and tricks if you stay at this game long enough.

Now back to your question. ADP is not my area of expertise so forgive me if you can't access this.

Have you tried to use ..

Me.RecordsetClone

This should give you all data current on the form.
Oct 28 '06 #6
NeoPa
32,556 Expert Mod 16PB
Ooh!
That's not one I was aware of - and may prove VERY useful.
I'll look into that in more detail on Monday.
Thanks for that M.
Oct 28 '06 #7
radio1
14
Re:
Have you tried to use ..
Me.RecordsetClone
This should give you all data current on the form.
Thanks for the suggestion, mmccarthy. I've been playing with it, hoping to be able to loop through the form's filtered recordset and add the records to a temporary table, and then report from that table (I don't know if this would have worked anyway -- I didn't get that far).
For me, the following TEST-ONLY code in my button click handler displays the # of records in the original recordset, not the user-filtered subset. Any thoughts on how to make this RecordSetClone reflect the filtered records only?
Also, the RS.Filter shows as simply the digit "0".

Expand|Select|Wrap|Line Numbers
  1.     If Me.FilterOn Then
  2.         Dim n As Integer
  3.         Dim RS As Recordset
  4.         Set RS = Me.RecordsetClone
  5.         RS.MoveLast
  6.         n = RS.RecordCount
  7.  
  8.         MsgBox "# of records: " & CStr(n)
  9.         MsgBox RS.Filter
  10.     End If
  11.  
Thanks for any input!
Nov 1 '06 #8
Has anyone figured out this problem?

I am having the same problem with the ADP (SQL Server Backend) and Filters.
Nov 9 '06 #9
radio1
14
Has anyone figured out this problem?

I am having the same problem with the ADP (SQL Server Backend) and Filters.
It's not a GOOD solution, but I have implemented something that will work in my application almost all of the time. I ended up doing a very-crude conversion of the WHERE clause from Jet's syntax to T-SQL's syntax.

This code (mostly) works for the data types I'm using. It should fail if the user enters a filter including the # or " characters (possible), or any of the other strings in the Replace function calls (much less likely). By "fail", I mean that the data in the report will not be the same as on the filtered form.

Expand|Select|Wrap|Line Numbers
  1.     Dim stDocName As String
  2.     Dim strWhere As String
  3.     strWhere = ""
  4.     If Me.Dirty Then
  5.         Me.Dirty = False
  6.     End If
  7.     If Me.FilterOn Then
  8.         strWhere = Me.Filter
  9.         strWhere = Replace(strWhere, "'", "''")         ' handle embedded apostrophes
  10.         strWhere = Replace(strWhere, Chr(34), "'")      ' convert " to '
  11.         strWhere = Replace(strWhere, "=-1)", "=1)")     ' form-filter checkboxes
  12.         strWhere = Replace(strWhere, "=True", "=1")     ' field-filter checkboxes
  13.         strWhere = Replace(strWhere, "=False", "=0")    ' field-filter checkboxes
  14.         strWhere = Replace(strWhere, "#", "'")          ' dates
  15.         strWhere = Replace(strWhere, " ALike ", " Like ")
  16.         MsgBox Me.Filter + ", " + strWhere
  17.     End If
  18.  
  19.     stDocName = "subjects"
  20.     DoCmd.OpenReport stDocName, acPreview, , strWhere
  21.  
If anyone has more complete conversion code PLEASE tell us about it.
ANY comment re this code is very welcome.
There REALLY must be a better solution to this.

Thanks,

-radio1
Nov 9 '06 #10
ozinm
1
Hi all,
just saw this thread while browsing.

Don't you want to use the me.serverfilter property instead of me.filter?
I'm pretty sure the ADP filter by form updates this value (but I'd need to double check to be sure).
Jan 29 '07 #11
NeoPa
32,556 Expert Mod 16PB
While this thread is probably a little old now, any contributions, especially if they improve on the answer(s) are to be welcomed for later browsers. Feel free to check it out and post what you find :)
Jan 29 '07 #12
Expand|Select|Wrap|Line Numbers
  1. Dim strWhereClause As String
  2.         strWhereClause = Me.Filter
  3.  
  4.         'Convert filter syntax to SQL Where Clause syntax
  5.         strWhereClause = Replace(strWhereClause, "Households.", "")
  6.         strWhereClause = Replace(strWhereClause, """", "'")
  7.         strWhereClause = Replace(strWhereClause, "True", "1")
  8.         strWhereClause = Replace(strWhereClause, "#", "'")
  9.         strWhereClause = Replace(strWhereClause, ")", "")
  10.         strWhereClause = Replace(strWhereClause, "(", "")
  11.         strWhereClause = Replace(strWhereClause, "-1", "1")
  12.  
  13.         DoCmd.OpenReport Me.cboHouseholdReports, acViewPreview, , strWhereClause
Nov 14 '07 #13
Jim Doherty
897 Expert 512MB
Hi all,
just saw this thread while browsing.

Don't you want to use the me.serverfilter property instead of me.filter?
I'm pretty sure the ADP filter by form updates this value (but I'd need to double check to be sure).

I use ADP files all the time never have problems with filtration and am subscribing to this but can't deal with it just now have a meeting will monitor it and come back.


Regards

Jim :)
Nov 15 '07 #14
NeoPa
32,556 Expert Mod 16PB
Jim, I wouldn't worry unduly. Check out the date of post #12 ;)

PS. Although, any extra accurate information is always a bonus so go ahead if you'd like.
Nov 16 '07 #15
Jim Doherty
897 Expert 512MB
Jim, I wouldn't worry unduly. Check out the date of post #12 ;)

PS. Although, any extra accurate information is always a bonus so go ahead if you'd like.
Oh my goodness..I didn't check that, thanks Neo I might have entered into a long schpeel...... with the original poster never to be seen again. I'll leave it at that you have saved my fingers there

Jim :)
Nov 16 '07 #16
NeoPa
32,556 Expert Mod 16PB
No probs.
This sort of thread resuscitation should probably be displayed with mummie's bandages as they did in the old films, but alas - probably too complicated and KUB365 has a busy schedule already ;)
Nov 16 '07 #17

Sign in to post your reply or Sign up for a free account.

Similar topics

3
by: Cezar | last post by:
I have a quite simple windows form project. It uses one standard form, a couple of simple controls including the tree control, and ADO.NET. Is there a simple automated way to port, or at least try,...
3
by: melnhed | last post by:
---Report the current filtered records from a Form--- Hello All, I've seen this topic discussed before, but the solution described then doesn't work in my particular case. My Config: ...
1
by: RSH | last post by:
Hi, I have a silly question... I have a Windows Form project (VB.Net) that was created in Visual Studio 2003. The project runs great. Now I have to add the ability to run the same...
2
by: Tom Clavel | last post by:
Scratching my head. I am using the filter property on a combo box .AfterUpdate event to get to a single client record. This is causing a some strange behavior: As I enter a subform, I get a no...
0
by: Ironr4ge | last post by:
Hi everyone, By the rate its going it want be long till I start growing gray hair... but anyway.. to come to the point... I am trying to open the form "Languages" with a diffrent record...
4
by: Ironr4ge | last post by:
Hi everyone, I am trying to open the form "Languages" with a diffrent record source to the "Contacts" form where I conducted the search or filter... . I was wondering whether there was a vba...
1
by: Joyfully | last post by:
I and a new user of MS Access 2003, and I need help with a report created from a form. I created a form that data is inputed weekly. I created a report to display this data in putting only the...
6
by: Nettle | last post by:
Purpose: I am creating a mailing distribution list database. Users should be able to filter/search contacts and add them to distribution lists they have created. My problem? I can't add multiple,...
3
by: fazalmast | last post by:
hi to all Experties. guide me please can we display report datewise in oracle form 10G? actually i want to query date wise because in our company we are printing monthly reports and i made two...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....

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.