ApplyFormFilter - Please Help!

G

Gina Whipp

Hello All,

The below code works, so why am I here??? Well, the Select Case
cboFieldName cause a repainting which I would prefer to avoid. I would like
it sort by whichever field you choose without the repaint, if that is at all
possible.

Thanks in advance,
Gina Whipp

Private Sub ApplyFormFilter(strFieldName As String)
Dim strFilter As String
Dim strDefaultFilter As String
Dim strDelimiter As String

strDelimiter = Chr$(34)
strDefaultFilter = "*"
strFieldName = "[" + strFieldName + "] Like "

Select Case Me!optFilter
Case 1: strFilter = Nz("[AÀÁÂÃÄ]*", strDefaultFilter)
Case 2: strFilter = Nz("B*", strDefaultFilter)
Case 3: strFilter = Nz("[CÇ]*", strDefaultFilter)
Case 4: strFilter = Nz("D*", strDefaultFilter)
Case 5: strFilter = Nz("[EÈÉÊË]*", strDefaultFilter)
Case 6: strFilter = Nz("F*", strDefaultFilter)
Case 7: strFilter = Nz("G*", strDefaultFilter)
Case 8: strFilter = Nz("H*", strDefaultFilter)
Case 9: strFilter = Nz("[IÌÍÎÏ]*", strDefaultFilter)
Case 10: strFilter = Nz("J*", strDefaultFilter)
Case 11: strFilter = Nz("K*", strDefaultFilter)
Case 12: strFilter = Nz("L*", strDefaultFilter)
Case 13: strFilter = Nz("M*", strDefaultFilter)
Case 14: strFilter = Nz("[NÑ]*", strDefaultFilter)
Case 15: strFilter = Nz("[OÒÓÔÕÖ]*", strDefaultFilter)
Case 16: strFilter = Nz("P*", strDefaultFilter)
Case 17: strFilter = Nz("Q*", strDefaultFilter)
Case 18: strFilter = Nz("R*", strDefaultFilter)
Case 19: strFilter = Nz("[SS]*", strDefaultFilter)
Case 20: strFilter = Nz("T*", strDefaultFilter)
Case 21: strFilter = Nz("[UÙÚÛÜ]*", strDefaultFilter)
Case 22: strFilter = Nz("V*", strDefaultFilter)
Case 23: strFilter = Nz("W*", strDefaultFilter)
Case 24: strFilter = Nz("X*", strDefaultFilter)
Case 25: strFilter = Nz("[YÝÿ]*", strDefaultFilter)
Case 26: strFilter = Nz("[ZÆØÅ]*", strDefaultFilter)
Case 27: strFilter = "*"
End Select


strFilter = strFieldName + Chr$(34) + Nz(strFilter, "*") + Chr$(34)
Me.Filter = strFilter
Me.FilterOn = True

'THIS IS THE PART THAT CAUSES THE REPAINT
Select Case cboFieldName
Case "cpFirstName"
Me.OrderBy = "cpFirstName"
Me.OrderByOn = True
Case "cpLastName"
Me.OrderBy = "cpLastName"
Me.OrderByOn = True
Case "DisplayName"
Me.OrderBy = "DisplayName"
Me.OrderByOn = True
End Select

End Sub
 
S

strive4peace

Hi Gina,

you need to requery... but what you can do is save a reference to the
record you are on and then put it back...

'~~~~~~~~~~~~~`
'put this at top of code


'save current record if changes were made
If me.dirty then me.dirty = false

Dim mRecordID As Long
mRecordID = Me.ID_controlname

'and this at the bottom

Me.RecordsetClone.FindFirst "IDfield = " & mRecordID
If Not Me.RecordsetClone.NoMatch Then
Me.Bookmark = Me.RecordsetClone.Bookmark
End If

Warm Regards,
Crystal
*
:) have an awesome day :)
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*



Gina said:
Hello All,

The below code works, so why am I here??? Well, the Select Case
cboFieldName cause a repainting which I would prefer to avoid. I would like
it sort by whichever field you choose without the repaint, if that is at all
possible.

Thanks in advance,
Gina Whipp

Private Sub ApplyFormFilter(strFieldName As String)
Dim strFilter As String
Dim strDefaultFilter As String
Dim strDelimiter As String

strDelimiter = Chr$(34)
strDefaultFilter = "*"
strFieldName = "[" + strFieldName + "] Like "

Select Case Me!optFilter
Case 1: strFilter = Nz("[AÀÁÂÃÄ]*", strDefaultFilter)
Case 2: strFilter = Nz("B*", strDefaultFilter)
Case 3: strFilter = Nz("[CÇ]*", strDefaultFilter)
Case 4: strFilter = Nz("D*", strDefaultFilter)
Case 5: strFilter = Nz("[EÈÉÊË]*", strDefaultFilter)
Case 6: strFilter = Nz("F*", strDefaultFilter)
Case 7: strFilter = Nz("G*", strDefaultFilter)
Case 8: strFilter = Nz("H*", strDefaultFilter)
Case 9: strFilter = Nz("[IÌÍÎÏ]*", strDefaultFilter)
Case 10: strFilter = Nz("J*", strDefaultFilter)
Case 11: strFilter = Nz("K*", strDefaultFilter)
Case 12: strFilter = Nz("L*", strDefaultFilter)
Case 13: strFilter = Nz("M*", strDefaultFilter)
Case 14: strFilter = Nz("[NÑ]*", strDefaultFilter)
Case 15: strFilter = Nz("[OÒÓÔÕÖ]*", strDefaultFilter)
Case 16: strFilter = Nz("P*", strDefaultFilter)
Case 17: strFilter = Nz("Q*", strDefaultFilter)
Case 18: strFilter = Nz("R*", strDefaultFilter)
Case 19: strFilter = Nz("[SS]*", strDefaultFilter)
Case 20: strFilter = Nz("T*", strDefaultFilter)
Case 21: strFilter = Nz("[UÙÚÛÜ]*", strDefaultFilter)
Case 22: strFilter = Nz("V*", strDefaultFilter)
Case 23: strFilter = Nz("W*", strDefaultFilter)
Case 24: strFilter = Nz("X*", strDefaultFilter)
Case 25: strFilter = Nz("[YÝÿ]*", strDefaultFilter)
Case 26: strFilter = Nz("[ZÆØÅ]*", strDefaultFilter)
Case 27: strFilter = "*"
End Select


strFilter = strFieldName + Chr$(34) + Nz(strFilter, "*") + Chr$(34)
Me.Filter = strFilter
Me.FilterOn = True

'THIS IS THE PART THAT CAUSES THE REPAINT
Select Case cboFieldName
Case "cpFirstName"
Me.OrderBy = "cpFirstName"
Me.OrderByOn = True
Case "cpLastName"
Me.OrderBy = "cpLastName"
Me.OrderByOn = True
Case "DisplayName"
Me.OrderBy = "DisplayName"
Me.OrderByOn = True
End Select

End Sub
Click to expand...
 
K

Klatuu

It is the OrderBy and Order methods that are causing the repaint. Nothing
you can do about that. When you change the form's order, it has to requery
using the new order. If there is something going on visually that is a
problem, you can set Echo off and on so you don't see it happen.

Application.Echo Off
'do the ordering here
Application.Echo On



Gina Whipp said:
Hello All,

The below code works, so why am I here??? Well, the Select Case
cboFieldName cause a repainting which I would prefer to avoid. I would like
it sort by whichever field you choose without the repaint, if that is at all
possible.

Thanks in advance,
Gina Whipp

Private Sub ApplyFormFilter(strFieldName As String)
Dim strFilter As String
Dim strDefaultFilter As String
Dim strDelimiter As String

strDelimiter = Chr$(34)
strDefaultFilter = "*"
strFieldName = "[" + strFieldName + "] Like "

Select Case Me!optFilter
Case 1: strFilter = Nz("[AÀÃÂÃÄ]*", strDefaultFilter)
Case 2: strFilter = Nz("B*", strDefaultFilter)
Case 3: strFilter = Nz("[CÇ]*", strDefaultFilter)
Case 4: strFilter = Nz("D*", strDefaultFilter)
Case 5: strFilter = Nz("[EÈÉÊË]*", strDefaultFilter)
Case 6: strFilter = Nz("F*", strDefaultFilter)
Case 7: strFilter = Nz("G*", strDefaultFilter)
Case 8: strFilter = Nz("H*", strDefaultFilter)
Case 9: strFilter = Nz("[IÃŒÃÃŽÃ]*", strDefaultFilter)
Case 10: strFilter = Nz("J*", strDefaultFilter)
Case 11: strFilter = Nz("K*", strDefaultFilter)
Case 12: strFilter = Nz("L*", strDefaultFilter)
Case 13: strFilter = Nz("M*", strDefaultFilter)
Case 14: strFilter = Nz("[NÑ]*", strDefaultFilter)
Case 15: strFilter = Nz("[OÒÓÔÕÖ]*", strDefaultFilter)
Case 16: strFilter = Nz("P*", strDefaultFilter)
Case 17: strFilter = Nz("Q*", strDefaultFilter)
Case 18: strFilter = Nz("R*", strDefaultFilter)
Case 19: strFilter = Nz("[SS]*", strDefaultFilter)
Case 20: strFilter = Nz("T*", strDefaultFilter)
Case 21: strFilter = Nz("[UÙÚÛÜ]*", strDefaultFilter)
Case 22: strFilter = Nz("V*", strDefaultFilter)
Case 23: strFilter = Nz("W*", strDefaultFilter)
Case 24: strFilter = Nz("X*", strDefaultFilter)
Case 25: strFilter = Nz("[YÃÿ]*", strDefaultFilter)
Case 26: strFilter = Nz("[ZÆØÅ]*", strDefaultFilter)
Case 27: strFilter = "*"
End Select


strFilter = strFieldName + Chr$(34) + Nz(strFilter, "*") + Chr$(34)
Me.Filter = strFilter
Me.FilterOn = True

'THIS IS THE PART THAT CAUSES THE REPAINT
Select Case cboFieldName
Case "cpFirstName"
Me.OrderBy = "cpFirstName"
Me.OrderByOn = True
Case "cpLastName"
Me.OrderBy = "cpLastName"
Me.OrderByOn = True
Case "DisplayName"
Me.OrderBy = "DisplayName"
Me.OrderByOn = True
End Select

End Sub
Click to expand...
 
G

Gina Whipp

Thanks, I will try that!

Klatuu said:
It is the OrderBy and Order methods that are causing the repaint. Nothing
you can do about that. When you change the form's order, it has to
requery
using the new order. If there is something going on visually that is a
problem, you can set Echo off and on so you don't see it happen.

Application.Echo Off
'do the ordering here
Application.Echo On



Gina Whipp said:
Hello All,

The below code works, so why am I here??? Well, the Select Case
cboFieldName cause a repainting which I would prefer to avoid. I would
like
it sort by whichever field you choose without the repaint, if that is at
all
possible.

Thanks in advance,
Gina Whipp

Private Sub ApplyFormFilter(strFieldName As String)
Dim strFilter As String
Dim strDefaultFilter As String
Dim strDelimiter As String

strDelimiter = Chr$(34)
strDefaultFilter = "*"
strFieldName = "[" + strFieldName + "] Like "

Select Case Me!optFilter
Case 1: strFilter = Nz("[AÀÁÂÃÄ]*", strDefaultFilter)
Case 2: strFilter = Nz("B*", strDefaultFilter)
Case 3: strFilter = Nz("[CÇ]*", strDefaultFilter)
Case 4: strFilter = Nz("D*", strDefaultFilter)
Case 5: strFilter = Nz("[EÈÉÊË]*", strDefaultFilter)
Case 6: strFilter = Nz("F*", strDefaultFilter)
Case 7: strFilter = Nz("G*", strDefaultFilter)
Case 8: strFilter = Nz("H*", strDefaultFilter)
Case 9: strFilter = Nz("[IÌÍÎÏ]*", strDefaultFilter)
Case 10: strFilter = Nz("J*", strDefaultFilter)
Case 11: strFilter = Nz("K*", strDefaultFilter)
Case 12: strFilter = Nz("L*", strDefaultFilter)
Case 13: strFilter = Nz("M*", strDefaultFilter)
Case 14: strFilter = Nz("[NÑ]*", strDefaultFilter)
Case 15: strFilter = Nz("[OÒÓÔÕÖ]*", strDefaultFilter)
Case 16: strFilter = Nz("P*", strDefaultFilter)
Case 17: strFilter = Nz("Q*", strDefaultFilter)
Case 18: strFilter = Nz("R*", strDefaultFilter)
Case 19: strFilter = Nz("[SS]*", strDefaultFilter)
Case 20: strFilter = Nz("T*", strDefaultFilter)
Case 21: strFilter = Nz("[UÙÚÛÜ]*", strDefaultFilter)
Case 22: strFilter = Nz("V*", strDefaultFilter)
Case 23: strFilter = Nz("W*", strDefaultFilter)
Case 24: strFilter = Nz("X*", strDefaultFilter)
Case 25: strFilter = Nz("[YÝÿ]*", strDefaultFilter)
Case 26: strFilter = Nz("[ZÆØÅ]*", strDefaultFilter)
Case 27: strFilter = "*"
End Select


strFilter = strFieldName + Chr$(34) + Nz(strFilter, "*") + Chr$(34)
Me.Filter = strFilter
Me.FilterOn = True

'THIS IS THE PART THAT CAUSES THE REPAINT
Select Case cboFieldName
Case "cpFirstName"
Me.OrderBy = "cpFirstName"
Me.OrderByOn = True
Case "cpLastName"
Me.OrderBy = "cpLastName"
Me.OrderByOn = True
Case "DisplayName"
Me.OrderBy = "DisplayName"
Me.OrderByOn = True
End Select

End Sub
Click to expand...
Click to expand...
 
G

Gina Whipp

Klatuu, works like a charm... BIG THANKS!!!

Klatuu said:
It is the OrderBy and Order methods that are causing the repaint. Nothing
you can do about that. When you change the form's order, it has to
requery
using the new order. If there is something going on visually that is a
problem, you can set Echo off and on so you don't see it happen.

Application.Echo Off
'do the ordering here
Application.Echo On



Gina Whipp said:
Hello All,

The below code works, so why am I here??? Well, the Select Case
cboFieldName cause a repainting which I would prefer to avoid. I would
like
it sort by whichever field you choose without the repaint, if that is at
all
possible.

Thanks in advance,
Gina Whipp

Private Sub ApplyFormFilter(strFieldName As String)
Dim strFilter As String
Dim strDefaultFilter As String
Dim strDelimiter As String

strDelimiter = Chr$(34)
strDefaultFilter = "*"
strFieldName = "[" + strFieldName + "] Like "

Select Case Me!optFilter
Case 1: strFilter = Nz("[AÀÁÂÃÄ]*", strDefaultFilter)
Case 2: strFilter = Nz("B*", strDefaultFilter)
Case 3: strFilter = Nz("[CÇ]*", strDefaultFilter)
Case 4: strFilter = Nz("D*", strDefaultFilter)
Case 5: strFilter = Nz("[EÈÉÊË]*", strDefaultFilter)
Case 6: strFilter = Nz("F*", strDefaultFilter)
Case 7: strFilter = Nz("G*", strDefaultFilter)
Case 8: strFilter = Nz("H*", strDefaultFilter)
Case 9: strFilter = Nz("[IÌÍÎÏ]*", strDefaultFilter)
Case 10: strFilter = Nz("J*", strDefaultFilter)
Case 11: strFilter = Nz("K*", strDefaultFilter)
Case 12: strFilter = Nz("L*", strDefaultFilter)
Case 13: strFilter = Nz("M*", strDefaultFilter)
Case 14: strFilter = Nz("[NÑ]*", strDefaultFilter)
Case 15: strFilter = Nz("[OÒÓÔÕÖ]*", strDefaultFilter)
Case 16: strFilter = Nz("P*", strDefaultFilter)
Case 17: strFilter = Nz("Q*", strDefaultFilter)
Case 18: strFilter = Nz("R*", strDefaultFilter)
Case 19: strFilter = Nz("[SS]*", strDefaultFilter)
Case 20: strFilter = Nz("T*", strDefaultFilter)
Case 21: strFilter = Nz("[UÙÚÛÜ]*", strDefaultFilter)
Case 22: strFilter = Nz("V*", strDefaultFilter)
Case 23: strFilter = Nz("W*", strDefaultFilter)
Case 24: strFilter = Nz("X*", strDefaultFilter)
Case 25: strFilter = Nz("[YÝÿ]*", strDefaultFilter)
Case 26: strFilter = Nz("[ZÆØÅ]*", strDefaultFilter)
Case 27: strFilter = "*"
End Select


strFilter = strFieldName + Chr$(34) + Nz(strFilter, "*") + Chr$(34)
Me.Filter = strFilter
Me.FilterOn = True

'THIS IS THE PART THAT CAUSES THE REPAINT
Select Case cboFieldName
Case "cpFirstName"
Me.OrderBy = "cpFirstName"
Me.OrderByOn = True
Case "cpLastName"
Me.OrderBy = "cpLastName"
Me.OrderByOn = True
Case "DisplayName"
Me.OrderBy = "DisplayName"
Me.OrderByOn = True
End Select

End Sub
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

File Path and Name in Import String 2
need help 0
VBA code help 3
Multiselect to append 1
Another Question - QBF with List Boxes 0
Help needed Writing formula that totals data at end of column 7
Help on error message 0
Please check my code. I cannot get the calculated string. 1

Top