How to add a search field in Access 2007

[superblade]

Hi,

Wonder if someone can help me?

I have two tables, one is a project table and one is an address table, in addition I have a project form bound to the project table.

I've created a lookup field (at table level) to allow the ability to lookup and select multiple addresses that are related to the project but there are 100,000s of properties so i need a way of searching and then selecting the relative properties from the project form.

E.G.

Project Table
ID - Autonumber (primary key)
Associated Properties - Text

Address Table
ID - Text (primary key)
Address - Text
Zip Code - Text


project form

Associated Properties (Search for properties to link to the project by zip code or address) select the properties related and add to the project.

I tried by just creating a query and putting [Enter Zip Code] as the criteria and that kind of works but it always pops up the box every time i load the form.

Any help is much appreciated

Many thanks

superblade

[jforbes]

Usually, what I do in a situation like this is to create and use a Pop-up Form (Dialog) to Filter and Select the Value.

The basic idea:

1. First, create a Global Variable to hold the Value that you are looking for.
2. Create the Dialog Form that allows the user to perform some Filtering on it's contents to narrow down the list from all the possible items. Sometimes a Continous Form works well in this situation, but I find Continuous Forms a pain to maintain. So, I usually go with a Single Form and either use a ListBox if there isn't much information to display about the Value to search for, or a Datasheet SubForm when there is a lot of columns of information to sift through. Regardless of the way to list the Rows, they are Filtered similarly.
3. Build into the Form the Filtering by placing TextBoxes and ComboBoxes to gather the Filter criteria from the user. Then either with a CommandButton or on the TextBoxes/ComboBoxes AfterUpdate Events, Filter the list of Values. This might explain this step further: http://bytes.com/topic/access/insigh...filtering-form
4. Add a Button to copy the selected Value into the Global Variable setup in the first step and then call DoCmd.Close to Grab the selected Value and close the Form.
5. Then put it all together by adding a button to call the Lookup/Dialog on the Main Form that clears out the Global Variable, calls DoCmd.OpenForm on the New Lookup Form with the Window Mode to acDialog so that when ran, the Code will stop and wait for the Dialog to be Closed. Then the last thing to do in the Button is see if the Global Variable has a value; if it does, set the local Field equal to the Global Variable. If the Global Variable is still cleared out, then the user canceled out of the Dialog Form and nothing should be done.