473,320 Members | 2,048 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,320 software developers and data experts.

Datasheet filter problem

Hi,
I have a form which has a subform in datasheet view. In access 2007, there is an arrow by each column which allows me to set a filter. Ussually when i click the arrow, i get a choice of records to filter by as shown below:


However, when i want filter 2 columns the choices disapear on the second column after i set the filter on the first column, as shown below:


I know this is supposed to work, not sure what i am doing wrong. Any help would be greately appreciated.

Leon
Oct 26 '09 #1
11 7852
ChipR
1,287 Expert 1GB
I can duplicate this with a subform in data sheet view. You can add a text or number filter to another column, but it will not give you a list of filtered choices. It looks to me like a limitation of the program.
Oct 26 '09 #2
Hi, thanks for the quick reply. The wierd thing is that it works in one subform datasheet but not another. Makes me think that it is a setting, just not sure which one. Any ideas?

Leon
Oct 26 '09 #3
NeoPa
32,556 Expert Mod 16PB
So, is it the fact that it's the second column or that it's a particular column that stops it giving the filter options?
Oct 26 '09 #4
Seems to be any column. I also noticed the following. I have buttons on the form that requery the subform based on specific criteria. when i open the form and all of the records are shown the quick filter works fine on all of the colums, even more than one at a time. once i use a command button to requery the subform, i can only quick fiilter one column at a time using the prefilled list. Strange?

Leon
Oct 26 '09 #5
NeoPa
32,556 Expert Mod 16PB
As Chip says, it's probably a limitation of Access.

Having said that it wouldn't hurt to see the code that causes this behaviour (the code that filters the form behind your buttons) in case it throws up any ideas.

Have you checked the Help system for filtering and any comments about limitations, such as quick filtering being limited when there is already a filter applied?
Oct 26 '09 #6
Thanks, i have read everything that i could find, and i dont see any limitations. Here is a sample button:
Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdFltrDenied_Click()
  2.  
  3.     Dim sSQL As String
  4.  
  5.     sSQL = "SELECT * FROM qryReferral WHERE qryReferral.Status = 'Denied' ORDER BY qryReferral.ReferralDate;"
  6.     Me![frmReferralSub].Form.RecordSource = sSQL
  7.  
  8. End Sub
Thanks
Oct 26 '09 #7
NeoPa
32,556 Expert Mod 16PB
Two points :
  1. I would suggest that if you're applying a filter you should consider setting the .Filter & .FilterOn properties instead of resetting the .RecordSource.
  2. Quick Filters rely on knowing what values are in the whole recordset. You could try some code to move to the end and back when you apply the filter. Maybe the list is simply not there because it has yet to read through to the end.
Oct 26 '09 #8
are there any drawback of using filetrs instead of recordsource? I have heard in the past that there are issues with indexing.

Leon
Oct 26 '09 #9
NeoPa
32,556 Expert Mod 16PB
Not that I'm aware of. There would be the fact that the SQL is never optimised as it would be in a QueryDef, but that applies equally to both methods. I can think of various issues with adjusting the RecordSource directly however.
Oct 26 '09 #10
Thanks for all of your help, i really appreciate it.

Leon
Oct 27 '09 #11
NeoPa
32,556 Expert Mod 16PB
No worries Leon. I hope it helped :)
Oct 27 '09 #12

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

Similar topics

5
by: phobos | last post by:
I have a complex query built up out of lots of little queries; I run the final one and look at the output. This query takes a little while to run - like maybe a couple of minutes. Lots of...
0
by: Malcolm Cook | last post by:
I've discovered: Using "File > Print Preview" in a form's datasheet view with a server Filter crashes access after previewing... ....that is, unless ServerFilterByForm has been turned off...
2
by: phhatwork | last post by:
Hi all, I'm trying to replace the default shortcutmenus that you get when you right-click a form in datasheet view. By default you get different menus for when you click a column (you can hide and...
1
by: George Kandaz | last post by:
Steve, I posted a question to your last post almost a week ago, but never heard from you again. Please read my last message, as follows. Thanks. ...
1
by: Thelma Lubkin | last post by:
I have a form that allows a user to choose a specific field value. The form calls a second, datasheet, form using a different field in the selected record as a filter . This second form has a...
4
by: steph | last post by:
Hi, A question regarding Access 2002: I've got a form which should be displayed in Form View and in Datasheet View. But I want to use a different record source for each view. That means, when...
3
by: Bface | last post by:
Hello, I tried looking through some of the postings to see if anyone asked these questions before but I didn’t find any. I have 3 questions and I hope someone can help me out. I will write post...
1
by: fiazbfs | last post by:
I would like to know the Keyboard Shortcuts for "Apply Filter", "Filter by Form", "Filter by Selection" in datasheet / form view. It is very annoying to just be able to use the mouse for this...
4
WyvsEyeView
by: WyvsEyeView | last post by:
I am doing the very standard thing of filtering the contents of one combo box based on another combo box. I've done it many times, but always on a main form. Now I'm trying to do it on a datasheet...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
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: 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...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
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....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

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.