Sorting filtered list on form with a Command Button

L

LDMueller

I have a form CLIENTLIST. When I open this form it runs the query
qryClientLst. One of the fields in the query named ClientID has the criteria
"Like [Enter Client Number]". When I open the form CLIENTLIST I'm prompted
to "Enter Client Number". When I do the form opens and only the files with
the client number I entered appear. So basically it's a filtered form. All
of this works fine.

I created a command button above the column ClientId on my form so my users
can click on the button and sort Ascending or Descending. Below is my code
for this. My only problem is when I click on the command button, I get the
prompted to [Enter Client Number] again.

If I remove the prompted from the query qryClientLst so it displays all
records when I open the form, everything works fine. But I need it to sort
the filtered list.

Private Sub cmdSort_Click()
If Right(Me.OrderBy, 4) = "Desc" Then
Me.OrderBy = "[ClientID]"
Else
Me.OrderBy = "[ClientID]" & " Desc"
End If
Me.OrderByOn = True
End Sub

Can anyone help me?

Thanks,

LDMueller
 
C

Chris

You are reprompted because the form has to re-run the query to sort it.

One possibility would be to open the form without any filtering and have a
combo box where the user could select from a list of Client Numbers. In the
after update event of the combo box, change the form's query to filter by the
value selected in the combo box. Then, when you execute the sort, the query
will still read the value in the combo box.

I hope this makes sense. If you need help with how to set something like
that up, post back with the SQL from qryClientLst and I will do my best to
help you set something up.

HTH,
Chris
 
L

LDMueller

Hi Maurice,

When I described my problem I tried to simplify it. Above each column in my
form I have sort buttons which toggle between ascending and descending so my
user can click a button above any column and resort it quickly. So putting
it in the query isn't an option.

Thanks!

Maurice said:
wouldn't it be easier to set the sort option in the query as well?
--
Maurice Ausum


LDMueller said:
I have a form CLIENTLIST. When I open this form it runs the query
qryClientLst. One of the fields in the query named ClientID has the criteria
"Like [Enter Client Number]". When I open the form CLIENTLIST I'm prompted
to "Enter Client Number". When I do the form opens and only the files with
the client number I entered appear. So basically it's a filtered form. All
of this works fine.

I created a command button above the column ClientId on my form so my users
can click on the button and sort Ascending or Descending. Below is my code
for this. My only problem is when I click on the command button, I get the
prompted to [Enter Client Number] again.

If I remove the prompted from the query qryClientLst so it displays all
records when I open the form, everything works fine. But I need it to sort
the filtered list.

Private Sub cmdSort_Click()
If Right(Me.OrderBy, 4) = "Desc" Then
Me.OrderBy = "[ClientID]"
Else
Me.OrderBy = "[ClientID]" & " Desc"
End If
Me.OrderByOn = True
End Sub

Can anyone help me?

Thanks,

LDMueller
Click to expand...
Click to expand...
 
L

LDMueller

Well that piece of information explains a lot. Thank you for this.

My SQL code is as follows:

SELECT IDSStat.IDSID, IDSStat.MtrRefsFrom, IDSStat.DktSheetAtty,
IDSStat.[30Day], IDSStat.DateSelect, IDSStat.MtrPosRel, IDSStat.RespAtty,
IDSStat.FilSuIDS, IDSStat.SupIDSFil, IDSStat.ActType, IDSStat.Notes,
Patents.Matter, Patents.RespAtty, Patents.OtherAtty, Patents.SuprAtty
FROM Patents RIGHT JOIN IDSStat ON Patents.Matter = IDSStat.MtrPosRel
WHERE (((IDSStat.MtrRefsFrom) Like [Enter Client Number]))
ORDER BY IDSStat.IDSID, IDSStat.MtrRefsFrom;

Thanks,

LDMueller

Chris said:
You are reprompted because the form has to re-run the query to sort it.

One possibility would be to open the form without any filtering and have a
combo box where the user could select from a list of Client Numbers. In the
after update event of the combo box, change the form's query to filter by the
value selected in the combo box. Then, when you execute the sort, the query
will still read the value in the combo box.

I hope this makes sense. If you need help with how to set something like
that up, post back with the SQL from qryClientLst and I will do my best to
help you set something up.

HTH,
Chris
LDMueller said:
I have a form CLIENTLIST. When I open this form it runs the query
qryClientLst. One of the fields in the query named ClientID has the criteria
"Like [Enter Client Number]". When I open the form CLIENTLIST I'm prompted
to "Enter Client Number". When I do the form opens and only the files with
the client number I entered appear. So basically it's a filtered form. All
of this works fine.

I created a command button above the column ClientId on my form so my users
can click on the button and sort Ascending or Descending. Below is my code
for this. My only problem is when I click on the command button, I get the
prompted to [Enter Client Number] again.

If I remove the prompted from the query qryClientLst so it displays all
records when I open the form, everything works fine. But I need it to sort
the filtered list.

Private Sub cmdSort_Click()
If Right(Me.OrderBy, 4) = "Desc" Then
Me.OrderBy = "[ClientID]"
Else
Me.OrderBy = "[ClientID]" & " Desc"
End If
Me.OrderByOn = True
End Sub

Can anyone help me?

Thanks,

LDMueller
Click to expand...
Click to expand...
 
C

Chris

Remove the Where clause from the query to get:
SELECT IDSStat.IDSID, IDSStat.MtrRefsFrom, IDSStat.DktSheetAtty,
IDSStat.[30Day], IDSStat.DateSelect, IDSStat.MtrPosRel, IDSStat.RespAtty,
IDSStat.FilSuIDS, IDSStat.SupIDSFil, IDSStat.ActType, IDSStat.Notes,
Patents.Matter, Patents.RespAtty, Patents.OtherAtty, Patents.SuprAtty
FROM Patents RIGHT JOIN IDSStat ON Patents.Matter = IDSStat.MtrPosRel
ORDER BY IDSStat.IDSID, IDSStat.MtrRefsFrom;

Place a combo box on the form (I usually put it in the form header). Name it
something like cboClientNumber. Set the rowsource property to "SELECT
DISTINCTIDSStat.MtrRefsFrom FROM IDSStat;"

In the after update event of the combo box, put the following code:
Me.FilterOn = False 'This line is here because if you have already set the
filter, you may
'get an error if you change the filter without
turning it off first
Me.Filter = "WHERE [MtrRefsFrom] = " & Me.cboClientNumber.Value
Me.FilterOn = True

This should work.

Chris
LDMueller said:
Well that piece of information explains a lot. Thank you for this.

My SQL code is as follows:

SELECT IDSStat.IDSID, IDSStat.MtrRefsFrom, IDSStat.DktSheetAtty,
IDSStat.[30Day], IDSStat.DateSelect, IDSStat.MtrPosRel, IDSStat.RespAtty,
IDSStat.FilSuIDS, IDSStat.SupIDSFil, IDSStat.ActType, IDSStat.Notes,
Patents.Matter, Patents.RespAtty, Patents.OtherAtty, Patents.SuprAtty
FROM Patents RIGHT JOIN IDSStat ON Patents.Matter = IDSStat.MtrPosRel
WHERE (((IDSStat.MtrRefsFrom) Like [Enter Client Number]))
ORDER BY IDSStat.IDSID, IDSStat.MtrRefsFrom;

Thanks,

LDMueller

Chris said:
You are reprompted because the form has to re-run the query to sort it.

One possibility would be to open the form without any filtering and have a
combo box where the user could select from a list of Client Numbers. In the
after update event of the combo box, change the form's query to filter by the
value selected in the combo box. Then, when you execute the sort, the query
will still read the value in the combo box.

I hope this makes sense. If you need help with how to set something like
that up, post back with the SQL from qryClientLst and I will do my best to
help you set something up.

HTH,
Chris
LDMueller said:
I have a form CLIENTLIST. When I open this form it runs the query
qryClientLst. One of the fields in the query named ClientID has the criteria
"Like [Enter Client Number]". When I open the form CLIENTLIST I'm prompted
to "Enter Client Number". When I do the form opens and only the files with
the client number I entered appear. So basically it's a filtered form. All
of this works fine.

I created a command button above the column ClientId on my form so my users
can click on the button and sort Ascending or Descending. Below is my code
for this. My only problem is when I click on the command button, I get the
prompted to [Enter Client Number] again.

If I remove the prompted from the query qryClientLst so it displays all
records when I open the form, everything works fine. But I need it to sort
the filtered list.

Private Sub cmdSort_Click()
If Right(Me.OrderBy, 4) = "Desc" Then
Me.OrderBy = "[ClientID]"
Else
Me.OrderBy = "[ClientID]" & " Desc"
End If
Me.OrderByOn = True
End Sub

Can anyone help me?

Thanks,

LDMueller
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Order by on form open 4
Sort by 2 fields 1
VBA sort code DESCENDING 4
open form help 1
Open form issue 1
problem opening form 2
Using a Label to Sort 6
Me.OrderByOn = True - Not working ?? 2

Top