Query to lookup a value in a field on a form and use in Criteria.. 1

[Tasuki]

I have a form (Form1), that has one field (Text1), and a subform which is the table view of a query (Form2) using (Query1) to show the data. I wanted to add to the criteria, something like:

Like " & [form1].[Text0] & " & *

And therefore, will update the query based on what I type on the form in field (Text1). I don't think my Criteria part is correct since it always returns an empty table. Does anyone know the right way to pull the value from the form and use it in the query? Will I need some sort of button on my form (Form1) to update the subform (Form2) once I type in a search criteria in field (Text1)?

Hopefully that's not too confusing.

Thanks for looking,

-T

 

[PHV]

Like [Forms]![Form1]![Text1] & '*'

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Tasuki]

Thanks PHV.

Although it doesn't seem to match anything in that field on the form. It does return all non-null fields though, but matching doesn't seem to work. How would I make it show both null and non-null fields? And how come the statement doesn't seem to work when I do a search?

I guess something's wrong with the like statement...

-T

 

[Tasuki]

Okay, it works searching on non-null values. But there's no way to query the null values using that LIKE statement.

I changed to: Like "*" & [Forms]![Form1]![Text1] & "*"

That way it will search everything in the text field and not just from the beginning.

Two last questions if anyone can help..

How can I get it to List the Null Fields also?
How can I get the subform (Form2) to refresh when I click the button?

Thanks for looking.

-T

 

[PHV]

Remove the criteria from the query.
In the AfterUpdate event procedure of Text1:
Me![Form2].Form.Filter = "Nz([name of field],'') Like '" & Me!Text1 & "*'"
Me![Form2].Form.FilterOn = True

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Tasuki]

Thanks PHV for all your help.

I settled with a button with onClick:

Me![Form2].Refresh

Although I haven't solved the problem with it not showing NULL fields when search criteria is blank, I'll try to figure it out later.

Tried your way above too, but it kept popping up the PropertyTag field (I replaced the name of field part with that) and asking for me to enter a value.

Well, thanks and if anyone knows why it won't show NULL values, please let me know.

-T

 

[Tasuki]

Woot! I figured it out... doing a search on the Access Other Forums.

In the OR below the CRITERIA, add:

OR [Forms]![Form1]![Text1] Is Null

And it works!

Hope this helps anyone else who wants to do a dynamic search form (can be dynamic if you don't use the button like I did, just do it in the on update even of the control).

-T