User defined searchfield

[Bongobob21]

Hi,



I have an idea in mind, but I don't know if it is possible to implement it in Microsoft Access or not.
I created a form with several search fields, unfortunately there are about 25 search fields, which makes the Form very unclear. As the users will need a maximum of maybe 5 search field per query, my idea was to let the user select up to 5 criteria he wants to search by.

Does anybody have an idea how to do that? My first thought was using a combo box to pick new criterias, but this is not possible, as I can't change the name of a search field without switching to Design mode. And it looks like that it is not possible to switch from Form mode to Design mode using a macro.

I know that this is a very vague request, but maybe somebody has an creative idea how to do it.

Thanks

[ranman256]

You need some code to search every box to see if it is being used, then create the sql filter:
I use a countinuous form then filter the results.
You cant use form boxes in a query if there's nothing in them..so..
Test all controls for a possible filter then build the where clause.

sub btnFIND_CLICK()
if not isnull(cboState) then   sWhere = sWhere & " and [state]='" & cboState & "'"
if not IsNull(txtName) then    sWhere = sWhere & " and [Name]='" & txtName & "'"
if not IsNull(chkContact) then sWhere = sWhere & " and [Contact]=" & chkContact.value

    'remove 1st And
sWhere= mid(sWhere,4)

  'just use the filter

me.filter = sWhere
me.filterOn = true

   'OR   
   'apply the sql to the form

sSql = "SELECT * FROM tblCompany WHERE " & sWhere

[Bongobob21]

I don't know If I made my point clear (sorry I am not a native speaker). My goal is that the form does not display all 25 search field anymore but just 5 the user picks before typing in any search criteria.

I Just have pretty basic knowledge of VBA, therefore I don't really know where to use that code. Can you maybe provide an Access example?

[ssanfu]

I think I have a form like what you are asking for. I will have a look tonight. Haven't seen it in a long time.........

[NoellaG]

Hi,
you could put 5 text fields on the form and then replace the label next to the fields by a combobox that displays a list of all existing fields to choose from.
After that you can build up your sql search string according to the used searh fields.

[Bongobob21]

I thought about that too, but I can't change the names of the search fields according to the combobox. Therefore I can't connect the searchfields to a query, as the name of the serachfield will always be the default. As far as I know you can't interactively change the name of a searchfield dependent on a combobox for example.

@ssanfu It would be awesome if you could help me out with an example ;-)

[rpeare]

look at the the attachment:
Account Database.zip

On this thread:
https://www.accessforums.net/queries...due-51509.html

Look at the form:

AccountInformationSearchForm_2

it dynamically builds a search criteria and applies it to a report. You can use the same methodology to find records on a form.

[Bongobob21]

Thanks a Lot but i can't find AccountInformationSearchForm_2 in that Database

[rpeare]

then poke around :P

frmCLAcctInfo

[NoellaG]

Hi here you'll find another example db with a dynamic search form. In this example it is important that the names of the fields you want to search on are correct (table fieldname = clubXYZ, on the form the fieldname is txtXYZ or has another 3 char prefix & XYZ) Amll search fields have the tag "SF" & [N] for numbers or [T] for text.

FilterByForm.zip

[ssanfu]

I finally found the file:
http://access.mvps.org/access/resources/downloads.htm
The file name is "Find Record 2000"

I'm not sure how useful it will be for you. If I understand what you want, this form might not work for your purpose. It might take a lot of modifications to the code.

Import the form into your dB and open it. Good luck......

[June7]

I do what you describe - takes quite a bit of VBA. I limit the search to two fields.

I have two comboboxes that list the table fields. Combobox 2 is activated after selection in Combobox 1 and the field list in Combobox 2 will not include the field selected in Combobox 1. Then I have 2 more comboboxes that list values from the selected fields. Then code builds the filter string based on values in comboboxes. Code has to deal with whether selected fields are text, number, or date type.

As you discovered, not practical with dynamic parameterized query, which I never use. Also don't use macros.

[Bongobob21]

Thanks for your great support! But I think my main issue is, that I need an easy way for the user to create a new searchfield out of a given set of searchcriteria. To link those searchboxes with the query they have to get names related to the choosen searchcriteria. So that i can relate the searchboxes to the query beforehand. Unfortunately I can't really create an example of what I am looking, as this is the problem. I think what June7 is explaining is the closes to what I am looking for.

[NoellaG]

If you want to look at the solution of June: it is exactly what's the example FilterByForm I've sent is doing. Be carefull, if you are using American notation, you have to change the delimiter in the code that builds the field lists from ";" to "," but it is noted in comment next to the code line where you have to change it.

success
Noëlla