Datasheet filter problem

[darnnnel]

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

[ChipR]

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.

[darnnnel]

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

[NeoPa]

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?

[darnnnel]

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

[NeoPa]

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?

[darnnnel]

Thanks, i have read everything that i could find, and i dont see any limitations. Here is a sample button:

Private Sub cmdFltrDenied_Click()

    Dim sSQL As String
    
    sSQL = "SELECT * FROM qryReferral WHERE qryReferral.Status = 'Denied' ORDER BY qryReferral.ReferralDate;"
    Me![frmReferralSub].Form.RecordSource = sSQL

End Sub

Thanks

[NeoPa]

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.

[darnnnel]

are there any drawback of using filetrs instead of recordsource? I have heard in the past that there are issues with indexing.

Leon

[NeoPa]

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.

[darnnnel]

Thanks for all of your help, i really appreciate it.

Leon

[NeoPa]

No worries Leon. I hope it helped :)