Specific Search Criteria

[John]

Hi,
I would like to carry out a search based on the crtiteria selected on my
form. Please let me know if this is possible or an alternative solution.
The forms record source is based on a query called "qryCandidates"

On my form, I have a combo box "cboSearchType" linked to a table called
"tblSearchType", with specific entries i.e. Date of Birth, Surname etc.

Below this I have a text box called "SearchCriteria", where users can enter
details like a date of birth "22/08/70", "Smith"

I then have a command button which I would like to produce a list from the
above criteria based on the forms record source query.

Any ideas please

regards

John

 

[Allen Browne]

John, are you just trying to offer *one* field at at time?
If so, this might help:
Find as you type - Filter forms with each keystroke
at:
http://allenbrowne.com/AppFindAsUType.html

It's basically a plug'n'play solution you can use on any form. You include
the module in your database, put the combo (for choosing a field) and text
box (for entering a value) on your form, set one property, and it works from
there.

Alternatively, if you want to offer the user multiple boxes where they can
enter their criteria, and it returns the records that match all their
entries, this example may help:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html

This one serves as an example, showing how to build the code to filter the
different field types. It's not just plug'n'play, but it is really worth
learning the filtering techniques to use with your forms and reports.

 

[John]

Allen,
I do want to run the query based on 1 field, with the criteria set in a
textbox.
However the form I have designed does not show any records. I just want to
select the field using the combobox and specify the criteria followed by a
command button to show the results in a report.

Sorry for the confusion.

Regards

John

 

[Allen Browne]

Check out the FindAsUType solution.
You will find it really easy to implement.

 

[John]

Ok Allen, Thanks
I'll give it a try

Regards

John

 

[efandango]

Allen,

Will your findasyoutype code work with a mainform/subform; where the subform
is continous and is the one that your code searches on?. If so, can you give
me some pointers on how best to adapt your code (assuming you will will allow
me to).

I want to have the main form contain the search criteria boxes and the
subform contain the continous form that shows the results; the reason for
wanting to do this is because I also want to incorporate another continous
form, and as you know, the main form has to be a 'Single' form in order to
allow a continous subform.

 

[Allen Browne]

To use this utility:
http://allenbrowne.com/AppFindAsUType.html
without making any changes, you need to copy the combo and text box onto
the form where you want it to work. That can be a main form or a subform,
but it searches only the form where you put the 2 controls. A continuous
subform should work fine: you will probably put the combo and text box in
the Form Footer or Form Header (of the continuous subform.)

You can modify the code to do other things if you want, but it then won't be
as portable (to just use on any form.)

For suggestions on how to go about filtering a main form so that it is
filtered by fields in the subform, see this example:
http://allenbrowne.com/ser-28.html

 

[efandango]

Allen,

Thanks for replying and explaining things further. What I wanted to do was
have a main form containing two continous sub forms, with the main form
holding the search controls and the 1st subform holding the search results in
a continous form format, with the 2nd subform holding other data based on the
found addresses.

But by the sounds of it, I cannot do this as I have to have the search
controls placed on the continous form I am using for the found addresses,
which would mean that I cannot have a continous subform inside another
continous form. Is that correct?

 

[Allen Browne]

... which would mean that I cannot have a continous subform inside
another continous form. Is that correct?

AFAIK, you can't do that anyway.

 

[efandango]

Allen,

You are correct, I cannot have a continous form containing a continous
subform.

On another note, can you tell me if it is possible to have your FindAsUType
control find just, for example:

"Park Road"

instead of, for example:

North Park Road
South Park Road
Park Road
West Park Road
Any Park Road
etc...

 

[Allen Browne]

In the module ajbFindAsUType, under "Configuration options", change:
Private Const mbcStartOfField = False
to:
Private Const mbcStartOfField = True

It will then match the start of the field instead of anywhere in the field,
which should achieve what you need.

 

[efandango]

Allen,

Thanks for replying... Can I have my Cake and eat it?.

Is it possible to have both options, with say, the search 'start of field'
option on a button/switch, and the default find 'anywhere'. So incredibly
useful is your feature that I find I will use both options and it would be a
great shame to have only one or the other.

 

[Allen Browne]

Yes: you could modify the code to use a variable instead of the constant.

Since you are tying this to an option button on a particular form, you need
to use a module-level variable in the module of each form (since different
forms could be open at the same time and have different settings), and pass
it to the generic function. Naturally you will need to code your own
modifications here.