Help-Search feature in a form using a Query?

[russ1985]

I do not know whether to put this in the form or query forum section (sorry if it’s wrong)

I am creating a database to track all enquiries in a letting agents.

What I am looking to do is enter the person’s requirements in the form and have a list of all matching properties shown in a sub report below.

I think that the best way to do this is by creating a query on the properties table that uses filters from fields in the report.

In the query design i have used the build feature to enter the following in one of criteria fields.

Like '* Forms![New Enquiry]![Property Size] *'

As i am sure you have guessed this does not work the query just returns no records. I am totally self taught with access and so kinda just bumble my way through. Any help would be gratefully appreciated.

As i am sure you have guessed this does not work the query just returns no records. I am totaly self taught with access and so kinda just bumble my way through. any help would be greatfully appreciated.

 

[JANR]

Change this:

Like '* Forms![New Enquiry]![Property Size] *'

To this:

Like "*" & Forms![New Enquiry]![Property Size] & "*"

JR

 

[russ1985]

Thank you for the above reply,

I have made the changes however it is now showing all properties regardless of the value entered in Forms![New enquiry]![Property Size].

It is definatly linking though as when the new enquiry form is not open it throws up a box to enter the parameter.

 

[JANR]

It is definatly linking though as when the new enquiry form is not open it throws up a box to enter the parameter.

The form HAS to be open for the query to see which criteria to filter on. If you don't enter any value in "Proberty size" in your form, then the query has no Where-clause and will show you all your records.

JR

 

[russ1985]

Hi JR,

Thanks for your reply again,

I have done a little fiddling and the above solution works for standard text fields, however it is not working when used on lookup fields in the form(on the form the property size field has a dropdown box which you can select the size). Dont suppose you know why this does not work?

Sorry to be a pain.

 

[DCrake]

Here is a link to a post made yesterday that may help.

Look at the sample mdb

There is also documentation found here
Link
David

 

[JANR]

It all depends on the "lookupfield" aka Combox. If you are not familiar with them, read up on them.

The basic combobox is often set up like this:
You have a minimum of 2 fields in them. One is a recordID field and a description field. The recordID is often hidden but it is usually the Bound column as in the value which is stored in the combobox. So if select from the combobox lets say "John" and his recordID is 255 the if you use the combobox as a filter in your query you will filter on Like *255* and not Like *John* and ofcause it will fail.

What you can do insted is setting a WHERE-Clause like this:

Like "*" & Forms![New Enquiry]![yourcombobox].Column(1) & "*"

The number (1) tells the query to evaluate the 2. column in your combobox.

edit: sorry this last code was horseshit. I'v tested it but it coulden't work. To use a combo box you must change the boundcolumn to the correct column, in my example it would be BoundColumn = 2

JR

 

[russ1985]

sorry for my thinkness but i do not understand how to change the boundcolumn

'edit: sorry this last code was horseshit. I'v tested it but it coulden't work. To use a combo box you must change the boundcolumn to the correct column, in my example it would be BoundColumn = 2'

 

[JANR]

You change the boundcolumn in the properties of your combobox in your form.

In designview of your form select your combobox and open properties and under "Rowsource" you find it.

PS Use a unbound combobox so you don't change any data in your table.

DCrake's solution is also a useful way to do this.

JR

 

[russ1985]

Sorry again for my thinkness but i am getting the following error message when i try to run the query

Undefined Function [Forms]![New Enquiry]![Property Size].Column(1) in expression.

 

[JANR]

That's what I said in post #7 it will not work like that in a query.

You have to refrence it like a textbox just make sure which value the combobox is "storing" aka the boundcolumn.

JR