Help with a Combobox search

[jamie.rowland]

Hi,

Need help setting up a search field in a user form.

The form allows the user to select data from a previous table. It fills
in fields on the form and allows the user to add more information to
the record. The record, along with the additional information is stored
in a query when the user is finished...

The tricky bit is this... The user may need to go back to a particular
record at any time to perform an update of the information contained
within...

The user can only do this using the above form (frmTrans4).

The 'autopopulated' fields are the fields that the user would ideally
like to search on to find a particular record.

those fields are as follows:

ProdName
ProdID
Trans1ID
FormulationCode

Can I set up a combo box (cboSearch) that allows the user to select the
field they would like to search on and then add text to a txt box
(txtsearch), click a button (cmdSearch) and the form searches by the
field selected in the cboSearch combo box?

I.e.

If the user wants to search for a product name, they would select
ProdName in the CBOSearch box, then enter the desired Product name (say
'oil') and then hit search to find a matching record...?

Exact matches only are required (LIKE searches are not necessary!)

Cheers in advance

jamie

 

[Allen Browne]

You can easily add a combo that contains the name of the fields to choose
from. Just put the combo on your form, and set these properties:
Row Source Type Field List
Row Source Query1
Use the query that feeds your form in place of Query1.
We will assume this combo is named cboField.

You can also put a text box on the form for entering the value, and use its
AfterUpdate event procedure to find the record that matches. Of course there
might be several matches, so you might like to set the Filter of the form.
The Filter string will need to include the right delimiters around the
value, and the delimiters depend on the field type: quotes around text, #
around dates, and nothing around numbers. You can use BuildCriteria() to do
this for you. If the value is in Text0, it will be something like this:
BuildCriteria(Me.cboField,Me.RecordsetClone.Fields(Me.cboField).Type,
Me.Text0)

There is also several other issues with this approach:
- Some of the Fields in the query that feeds the form may not be visible (so
there is no point offering them to the user in the combo).
- The fields may be labelled something different (so the user will not be
able to tell which matches which).
- There may be combos where the bound column is hidden (so the user has no
idea what value they need to type to get a match.)
- The user may enter the wrong type of data, e.g. where it should be a
number or date.
- Some fields are not suitable for searching (e.g. OLE Objects.)

Don't know if it will suit what you need, but there is a new utility here
that you are welcome to use:
Find as you type
at:
http://allenbrowne.com/AppFindAsUType.html
It consists of:
- a module you copy into your database,
- a combo to select the field, and
- a text box for entering the value.
You add just one line to the form's Load event, and it loads the combo with
the right fields, figuring out which fields the user can see, how the user
sees them labelled, which ones are suitable for searching, whether the
combos can be searched in your version of Access even if their bound column
is hidden, and so on. And as the user types into the text box, with each
keystroke it progressively filters the form to only records that match.