Peculiar Form Behavior (1 Viewer)

[jkfeagle]

I have a form that obviously has an underlying query. One field of the query has a contraint based on a text box on the form using the Like command. When opening the form, it displays all of the records as if no entry was in the text box. The contraint does use wildcards so no value in the text box would produce all records. However, this is true even though I've tested with a default value in the text box. Even after entering a value in the text box and hitting a refresh button, it continues to display all of the records. Now for the peculiar thing. Leaving the form open, I run the query (yes the one underlying the form) and it displays the records correctly. How can this be?

 

[jkfeagle]

One other bit of information. The text box is unbound if that makes any difference.

 

[vbaInet]

If you type information into the textbox, you need to move away from the textbox to another control on the same form for the VALUE property of the textbox to change.

And it's odd for you to base the filter of your form on a control on the same form to be filtered when the form is opened.

 

[gemma-the-husky]

at the time the form is opened, the textbox IS empty - so you get everything. if you want to save the value of the text box, then save it in a public variable, and refer to this in your query instead - then when your form reopens, you can also load the public variable into the textbox.

 

[vbaInet]

at the time the form is opened, the textbox IS empty - so you get everything. if you want to save the value of the text box, then save it in a public variable, and refer to this in your query instead

... via a reference to a function that returns the value from the variable.

 

[jkfeagle]

I appreciate everyone's responses. Maybe I can clear something up. I placed a default value in the text box for a filter at open just as a test. I was trying to solve my issue and that was just something I tried to isolate the problem. Ultimately the filter (text box) will not be used until the form is already open and the user types in a department name they want to see and then clicks on the refresh button. This is what I really want to accomplish but it will not filter the records. It continues to show them all in the form. But as I said before, if I open the underlying query with the form open, it properly shows the filtered records. Do I still need to use the function call to accomplish this? I know I've used combo boxes, etc. to filter records on a form before. The difference being, I've not used the LIKE criteria with wildcards.

 

[vbaInet]

Are you using the Filter property of the form or is the filter applied on the form's record source?

If it's code, let's see your code. If it's in the query, let's see the WHERE part of the query.

 

[jkfeagle]

WHERE ((TRUTRACK_EMPLOYEE.DEPARTMENT) Like "*" & [Forms]![F_OT_Report]![txtDept] & "*")

 

[vbaInet]

You need to create your own Refresh button and put Me.Requery in its Click event.

 

[jkfeagle]

Already did that and it still returns all of the records even with a value in the text box.

 

[vbaInet]

Upload a stripped down version of your db and I'll take a quick look. Ensure that the version you are about to upload isn't working and that you have included some sample data for testing purposes.