Filter for Cmd Button

  • Thread starter Thread starter Charles D Clayton Jr
  • Start date Start date
C

Charles D Clayton Jr

I have a dbs in A2K that I need to make easier for the user to use.
To that end I have created command buttons for sorting. I have the
code for an Ascending and descending, by selection and exclude
selection and removing all filters. However, what I cannot figure out
is the "Filter for" option. When I right click in a datasheet (which
is the way the subform is set up) I get an option to "filter for" and
then I type whatever I needed. It is nice because you can use
wildcards. The code that I am using is this for my sorting buttons is
this (the example is sorting A to Z)

Private Sub cboSortAZ_Click()
On Error GoTo cboSortAZ_Error
Dim cntl As Control
Set cntl = Screen.PreviousControl
cntl.SetFocus
DoCmd.RunCommand acCmdSortAscending
Exit Sub
cboSortAZ_Error:
If Err.Number = 2046 Then
'Not available at this time
MsgBox "Put cursor in field to sort by!"
Else
MsgBox Err.Number & ": " & Err.Description
End If
End Sub

I have looked through the help file and the closest I could find was
the acCmdApplyFilterSort but I could never get it to work (if, in
fact, it is what I need). I have multiple columns that the user could
pick to do a search in so I do not quite know how to trap that
information easily. Before I could use the PreviousControl but know I
do not know.

Does anybody have any suggestions?

Thanks,

Charles D Clayton Jr
 
-----Original Message-----
I have a dbs in A2K that I need to make easier for the user to use.
To that end I have created command buttons for sorting. I have the
code for an Ascending and descending, by selection and exclude
selection and removing all filters. However, what I cannot figure out
is the "Filter for" option. When I right click in a datasheet (which
is the way the subform is set up) I get an option to "filter for" and
then I type whatever I needed. It is nice because you can use
wildcards. The code that I am using is this for my sorting buttons is
this (the example is sorting A to Z)

Private Sub cboSortAZ_Click()
On Error GoTo cboSortAZ_Error
Dim cntl As Control
Set cntl = Screen.PreviousControl
cntl.SetFocus
DoCmd.RunCommand acCmdSortAscending
Exit Sub
cboSortAZ_Error:
If Err.Number = 2046 Then
'Not available at this time
MsgBox "Put cursor in field to sort by!"
Else
MsgBox Err.Number & ": " & Err.Description
End If
End Sub

I have looked through the help file and the closest I could find was
the acCmdApplyFilterSort but I could never get it to work (if, in
fact, it is what I need). I have multiple columns that the user could
pick to do a search in so I do not quite know how to trap that
information easily. Before I could use the PreviousControl but know I
do not know.

Does anybody have any suggestions?

Thanks,

Charles D Clayton Jr
.
Click to expand...
Hi Charles,
you may find that it is easier to use a separate form. In
this separate form use a combination comboboxes and
listboxes to establish the multi filter criteria. Then use
code to create an sql that you then set as the
recordsource for the form when the filter form is closed.

for example (air code):
main form named, frmMain
frmMain record source is a saved query named qryMain

filter form named, frmMainFilter

on frmMainFilter 2 comboboxes
cboClientID - 2 columns id, Client Fullname
cboRegionID - 2 columns id, Region

on main form have command button with onclick
docmd.openform FormName:="frmMainFilter",
WindowMode:=acDialog

when completed setting criteria on frmMainFilter have
command button OK

****** code start*****

private sub cmdOK_OnClick()
dim strWhere as string
dim strSQL as string

if not isnull(cboClientID) then
if len(strWhere)>0 then
strWhere=strWhere & " AND "
end if
strWhere=strWhere & "((ClientID)=" & cboClientID & ")"
end if

if not isnull(cboRegionID) then
if len(strWhere)>0 then
strWhere=strWhere & " AND "
end if
strWhere=strWhere & "((RegionID)=" & cboRegionID & ")"
end if

if len(strWhere)>0 then
strSQL="Select * from qryMain where (" & strWhere & ");"
forms("frmMain").recordsource=strSQL
end if

docmd.close

end sub

****** code end ******

Luck
Jonathan
 
Thanks for your reply. I thought something like that might be the
case but I wanted to make sure that I was not overlooking anything.
It took me a while searching through help to find everything that I
did and I might have missed the right answer.

Blessings,

Charles D Clayton Jr
 
Back
Top