Modified Search Button

[Eric58132]

Good morning board,

Is there any way to modify a search command button so that it only searches through one column in a table? I'm currently working with a horribly designed table (which I didn't make) that houses about 45k unique records....all of which contain roughly 100 columns of data...a real mess.


The generic search command button on forms for record search will check every table cell until it finds the appropriate data. The only thing people ever search for is "Account#", which is always contained in the same field of the table. Honing in the search to just this field would drastically improve the search speed.

Is this possible?

[hansup]

Have you tried Filter By Form?

[Eric58132]

can I do that after I've already filtered by form? Each form has a separate filter on it ahead of time, and now I'm trying to let them search through only those records which have been filtered

[hansup]

I haven't tried, but suspect not. I think you would be creating a brand new filter condition which would replace the form's original filter condition.

Did you try?

You might could try basing the form's Record Source on a query which incorporates your first filter condition. Then Filter By Form could allow the user to apply an additional filter condition to narrow down the selection.

I'm really just grasping at straws here, Eric. Your question was thin on information.

[CreganTur]

The best suggestion I can offer is to create a query that is linked to the textbox on your form where users enter the account number. On the query's criteria row for the account number field, enter this:
[Forms]![FormName]![ObjectName]
where FormName is the name of the form, and ObjectName is the textbox's name. Then click on Queries -> Parameters. Enter the same code in the first field, and then select the correct data type in the next field. Then click Ok.

On your Form, you'll need to use 'Me.Requery' whenever someone changes the value of the textbox (or when the click the 'Search' button, if you have one). This will cause the query to run again, using the new account number that has been provided.

HTH:thumb