SetFilter Macro

[johannaellamay]

Hi! I have a continuous form based on a query for employees information. I already created a sort button with the following snippet for its macro:



I did that instead of choosing "Field List" and choosing the query for its data because I couldn't quite figure out how to replace my actual field names to the names I want (e.g. FirstName to First Name).

My sorting works fine. But when I try to use SetFilter, Access asks me to Set Parameter Value. This is my macro for my filter button (w/ a text box for entering filter data):



If you can't see the photo, here's what I wrote on the Where Condition:

="[" & [cboField] & "]Like'" & [txtFilter] & "*'"

What am I doing wrong? Please help.

 

[CJ_London]

I don't use macros but your where syntax seems wrong to me - missing spaces in particular and don't think you need the =

="[" & [cboField] & "]Like'" & [txtFilter] & "*'"

should be

"[" & [cboField] & "] Like '" & [txtFilter] & "*'"

 

[GinaWhipp]

Sample DB posted here...
http://www.access-programmers.co.uk/forums/showthread.php?p=1410980#post1410980

First, do put the space as recommended by CJ_London but do not remove the *=*, it kind of objected to that even though it should not have, so, just leave it there.

As for the error message...
It's asking for the parameter because you left *cboField* null. You are using cboField to select which field to sort by. So, you need to make a selection from that field first then type in your search data in txtFilter and then click the Command Button.

To avoid that error you could set a Default in cboField and that way it will never be null.

 

[johannaellamay]

Sample DB posted here...
http://www.access-programmers.co.uk/forums/showthread.php?p=1410980#post1410980

First, do put the space as recommended by CJ_London but do not remove the *=*, it kind of objected to that even though it should not have, so, just leave it there.

As for the error message...
It's asking for the parameter because you left *cboField* null. You are using cboField to select which field to sort by. So, you need to make a selection from that field first then type in your search data in txtFilter and then click the Command Button.

To avoid that error you could set a Default in cboField and that way it will never be null.

Hi! I got it to work. Though now, I have a new issue. I have no idea how it happened. So I've already gotten the sort and filter to work. I put a command button to do a remove sort/filter but when I try to click it, Access just tells me that it "isn't available now".

Also, as for cboFilter, I put the data source as Field List which came from a query. However, the field names are not exactly how I want them to be displayed. For example, eLastName should be seen by the user as "Last Name". How do I do that?

Another thing, is there a resource that you could share on creating sorts and filters through VBA? I'd like to learn it and I might even surprise myself. I'm just really really desperate.

 

[GinaWhipp]

1. What code are you using to clear the Filer?

2. For cboFilter, you would need to change that at the query level just use an Alias.

3. I don't know of any resource for that. I just use Allen Browne's filter and, depending on what needs to be sorted and how, I will write my own. You could Google it, something like *sorted vba ms access*. I'm sure you'll the 1.2 billon responses.

 

[johannaellamay]

1. What code are you using to clear the Filer?

2. For cboFilter, you would need to change that at the query level just use an Alias.

3. I don't know of any resource for that. I just use Allen Browne's filter and, depending on what needs to be sorted and how, I will write my own. You could Google it, something like *sorted vba ms access*. I'm sure you'll the 1.2 billon responses.

1. I'm using a Macro:

RunMenuCommand
Command RemoveAllSorts

2. Is Alias the same as Caption on the query? Or are you referring to something else?

3. Okay then. Thanks.

 

[GinaWhipp]

1. Oh, never use Macro just use...

Me.Filter = ""
Me.FilerOn = False

2. Okay, I guess so. I always called it Alias.

 

[johannaellamay]

1. Oh, never use Macro just use...

Me.Filter = ""
Me.FilerOn = False

2. Okay, I guess so. I always called it Alias.

That was a prompt response. Thanks! I'll try it right away.

 

[johannaellamay]

Hi Gina,

I am still having issues with my sort and filter. Whenever I write the macros, it works perfectly. But when I try to close and reopen my database, Access just gives me an error and says that the macro does not work.

I tried to read and analyze Allen Browne's example. But it didn't have sorting. Is there any other resource that you would recommend that I read? Basically, I want to be able to sort (Ascending and Descending) and filter my continuous form.

 

[johannaellamay]

Also, I tried using VBA on my form. Then after a while, Access keeps asking me to setparameter on cboField. I even deleted my codes which had cboField in them but it still kept asking me for it. I have no idea why. It's really frustrating.

 

[johannaellamay]

Hi! I was finally able to create my code for sorting my continuous form. However, filtering is a bit of a pain. Here's my code:

PPrivate Sub cmdAscending_Click()
    If IsNull(Me.cboField) Then
        MsgBox "Please choose a field.", vbOKOnly, "No field to sort."
    Else
        Me.OrderBy = Me.cboField
        Me.OrderByOn = True
    End If
End Sub
Private Sub cmdDescending_Click()
    If IsNull(Me.cboField) Then
        MsgBox "Please choose a field.", vbOKOnly, "No field to sort."
    Else
        Me.OrderBy = Me.cboField
        Me.OrderByOn = False
    End If
End Sub
Private Sub cmdFilter_Click()
    If IsNull(Me.cboField) Then
        MsgBox "Please choose a field.", vbOKOnly, "No field to sort."
    Else
        If IsNull(Me.txtFilter) Then
            MsgBox "Please type a text to filter.", vbOKOnly, "No text to filter."
        Else
            Me.Filter = "[" & [cboField] & "] Like '" & [txtFilter] & "*'"
            Me.FilterOn = True
        End If
    End If
End Sub

Private Sub cmdRemoveSortFilter_Click()
    Me.Filter = ""
    Me.FilterOn = False
    
    Me.cboField = ""
    Me.OrderByOn = False
End Sub

I just want to know, how to sort to Descending. Because Me.OrderByOn = True will only trigger Ascending. How do I reverse it?

 

[GinaWhipp]

Got so busy never got back to you, sorry. I Sort using...

DoCmd.RunCommand acCmdSortDescending
DoCmd.RunCommand acCmdSortAscending

I do use the Filter to effect a Sort. I use the On_Click event of a Label.