How to add a search box to a form bound to a query not a table

[MsT]

Hi

I have a form based on a query. I'd like to add a search box to the top to look up values and then populate the rest of the form. I can't do it with the combo box wizard because the relevant (third) option doesn't show up, I assume that's because the form is based on a query not a table.

Could anyone point me to an alternative method of adding a search?

Cheers

 

[Ranman256]

Queries are ALL based on tables (Somewhere), so you are good.
If you have the values in the combo box then,
In the afterUpdate event of the combo, put this code:

Me.filter ="[field2filter]=' " &  me.cboBox & " ' "
Me.filterOn= true

I put spaces in the single quote example so you can see them...take them out.

 

[CJ_London]

providing the query is not an action query (insert, delete etc) the search should work in exactly the same way, the fact the 3rd option (whatever that is) means you may have to complete some of the properties manually.

 

[MsT]

The combo box wizard, when used on a form based on a table, gives you three options:
- get values from another table/qery
- type in the values you want
- find a record on my form based on the value I typed in my combo box

I usually use the third option. However with this form (based on a select query which is itself based on two linked tables) I do not get this third option. According to my research so far this is standard behaviour for the wizard.

I haven't ever coded a combo box from scratch, but I am going to have go now!

 

[CJ_London]

the rowsource your your combobox will be something like

SELECT ID, myName FROM myQuery ORDER BY MyName

where myName is the name of the field in your query you want to search on, ID is the unique identifier to be able to select that row and myQuery is the name of your query

you would then set
bound column to 1
number of columns to 2
column widths to 0
note: the controlsource should be left blank (which means the control is unbound)
Also strong advise you change the name of the combobox to something meaningful - such as cboSearch

Then in the combo after update event you would put the code as suggested by Ranman, but the code would be

Me.filter ="[ID]=" & me.cboSearch
Me.filterOn= true