Problem with filter continuous form

[basilyos]

Hello Guys

i want to filter my continuous form on change event not after update

i use this code

Dim strFilter As String

Me.Refresh

strFilter = "video_name like '*" & Me.Text41 & "*'"

Me.Filter = strFilter
Me.FilterOn = True

but i can't type more than one letter

so i add this line

Me.Text41.SelStart = Nz(Len(Me.Text41), 0)

but now i can type any word but i can't use the space

any help??

 

[sneuberg]

I'm guessing you want the subform content to update as you type into this text box. I don't know how to do this, but I suspect you need to toggle the filter off and then back on each time. If you upload you database I'll play with this and see if I can come up with something.

 

[basilyos]

thanks for your try but i don't have a sub form
it's on the same form where my records will show in the details section of the form

 

[sneuberg]

it's on the same form where my records will show in the details section of the form

Well that probably makes it easier. My offer still stands. If you upload your database or a stripped down version of it I'll see if I can get something working for you. Doesn't look like you're getting much help from anyone else.

 

[basilyos]

ok sir this is my database link

http://www.filefactory.com/file/6y77y6k93sqb/RyBaKoV Database.accdb

the form name is frm_videos_list and the text box is text41

thanks in advance

remember eveything is ok but i can't use the space i just can make search for one word

that's my problem

 

[sneuberg]

Just to let you know where am at I'm trying some code I found at

http://answers.microsoft.com/en-us/...icrosoft/aa6ebf52-c51a-4d28-8933-9618fdb6fee2

that's supposed to work, but it's doing the same thing you code is doing. I guess that tells us something, but I'm not sure what.

 

[sneuberg]

This isn't looking good. I found another post http://www.access-programmers.co.uk/forums/showthread.php?t=240613 that has a example of this for a subform and it does the same thing; blocks the spaces. I guess I really don't understand anything about filters. I thought they just affected the form's record set. It blows my mind the way assigning a value to the filter has this mysterious affect on an unbound text box even when the text box is in the parent form of a form/subform situation.

I hope one of the other forum members can shed some light on this. I'd really like to know what's going on.

 

[WayneRyan]

Basilyos,

The OnChange event fires for each keystroke.

Use Me.Text41.Text for the value of the current string.
If you just want to check it after entry use the Before Or After Update of the Text41 control.

Wayne

 

[sneuberg]

Use Me.Text41.Text for the value of the current string.

I've tried it that way and it still doesn't work for one than one word. There doesn't seem to be any code that does. The question is why and how does assigning a value to the filter affect a unbound text box.

 

[WayneRyan]

sneuberg,

The filter doesn't affect the unbound text box.

The unbound text box affects the filter which affects the rows that the form displays.

As you continue to type in the unbound text box; it will continue to modify the filter.

Wayne

 

[sneuberg]

The filter doesn't affect the unbound text box.

That's what I alway thought, but please take a look at the attached database. In this simple example I have an unbounded text box named txtBox. It's on change event's code is:

Private Sub txtBox_Change()

Me.Filter = "[Table1].F1 = '" & Me.txtBox & "'"
Me.FilterOn = True

End Sub

If you type in the text box you will see what you type in reverse order. Why is this happening?

 

[WayneRyan]

In the OnChange event, you have to use me.txtBox.TEXT

You can't use the txtBox.Value or just me.txtBox

Wayne

 

[sneuberg]

I changed it to .text in the attached database. It does the same thing; reverses whats typed into it.

 

[WayneRyan]

S,

Me.Filter = "[Table1].F1 Like '" & Me.txtBox.Text & "*'"

hth,
Wayne

 

[sneuberg]

There's no point in going through this piecemeal. I've attached a database where this is tried on a subform with the same result the OP is experiencing; the spaces in the unbounded text box are being suppressed by this code:

Private Sub Text4_Change()

Dim strFilter As String
Me.Refresh
strFilter = "locality like '*" & Me.Text4.Text & "*'"
Forms!FRM_PCodeFilter!FRM_Pcodesubform.Form.Filter = strFilter
Forms!FRM_PCodeFilter!FRM_Pcodesubform.Form.FilterOn = True
Me.Text4.SelStart = Nz(Len(Me.Text4.Text), 0)

End Sub

Somehow the filter DOES have an effect on the unbounded text box. If we just knew how and what the rules were, maybe we could work around this.

 

[WayneRyan]

S,

Access is helping you too much.

The Me.Refresh is removing the trailing space and not applying it to the filter.

Note: Set a breakpoint (or put the word Stop) in your OnChange event and look around.

Remove it and it should work fine.

Wayne

 

[sneuberg]

S,

Access is helping you too much.

The Me.Refresh is removing the trailing space and not applying it to the filter.

Note: Set a breakpoint (or put the word Stop) in your OnChange event and look around.

Remove it and it should work fine.

Wayne

Hmm, that actually helped. Thanks. Now I'll see if I can get that far with the OP's original problem.

 

[sneuberg]

That didn't do it for the single form case. In the chopped down version of the attached OP's database the change event has the following code

Private Sub Text41_Change()

Dim strFilter As String
strFilter = "video_name like '*" & Me.Text41.Text & "*'"
Me.Filter = strFilter
Me.FilterOn = True
Me.Text41.SelStart = Nz(Len(Me.Text41.Text), 0)

End Sub

which is virtually the same as the subform version and yet in this case the spaces are still suppressed.

@basilyos You may have to use a subform. At least it seem to work in that case. I need to call it a day. I'll putz with this tomorrow some.

 

[sneuberg]

I think I got although I still don't understand it. The filter causes the cursor to go to the beginning of the unbound textbox. To overcome this the Selstart is set to the len of the textbox content, but the spaces are somehow ignored in this process. Assign the text box content to a string seems to be the way around this. In the code that follows I've also include a handy function to escape single quotes. This has nothing to do with your original problem, but you need it if you want to be able to type in single quotes without getting errors. Please let me know if this works for you.

Private Sub Text41_Change()

Dim strTextBox As String
strTextBox = Me.Text41.Text
Me.Filter = "video_name like '*" & ESQ(strTextBox) & "*'"
Me.FilterOn = True
Me.Text41 = strTextBox
Me.Text41.SelStart = Len(strTextBox)

End Sub

'function to escape single quotes
Private Function ESQ(str As String) As String

ESQ = Replace(str, "'", "''")

End Function

 

[basilyos]

sneuberg you are genius

thank you so much for everyone in this post ans specially you