Access 2010 - Form - Box "Search" in Navigation bar

[ancucchi]

I'm tryng to replicate the box in object that can filter the visualized data while I digitize searching in every field of the table.

I have realized something similar with this simple code that filter olny one field:
""""" Me.Filter = "[LastName] Like '*" & [ControlName] & "*'"
Me.FilterOn = True """""

But write the some code for hundreds filed become reaaly hard.

It's possible change [LastName] with something similar that mean [EVERY FIELD]?

Or other suggestions?

Tony from Sardinia (ITALY)

 

[John W. Vinson]

I'm tryng to replicate the box in object that can filter the visualized data while I digitize searching in every field of the table.

I have realized something similar with this simple code that filter olny one field:
""""" Me.Filter = "[LastName] Like '*" & [ControlName] & "*'"
Me.FilterOn = True """""

But write the some code for hundreds filed become reaaly hard.

It's possible change [LastName] with something similar that mean [EVERY FIELD]?

Or other suggestions?

Tony from Sardinia (ITALY)

If you have hundreds of fields in your table - it's NOT properly
normalized!!!!

Short answer: No. Query criteria are field by field.

Longer answer: you can use VBA code to loop through the unbound controls on a
search form, constructing a SQL string containing criteria for those controls
in which the user entered something.

Could you explain the context? What's in the table? Why does it have hundreds
of fields?
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[ancucchi]

That database manage clients of an enterprise and all their data.

My client asked specifically one box where write something for filter the all data... for example they write "MOKBA" and want see only the people birth in Mokba or living in Mokba or named Mokba and so on... exactly as the little search box in navigation bar do... only more bigger and inside the form...

Tony from Sardinia...

Il giorno giovedì 30 agosto 2012 18:18:37 UTC+2, John W. Vinson ha scritto:

I'm tryng to replicate the box in object that can filter the visualized data while I digitize searching in every field of the table.

I have realized something similar with this simple code that filter olnyone field:

""""" Me.Filter = "[LastName] Like '*" & [ControlName] & "*'"

Me.FilterOn = True """""
But write the some code for hundreds filed become reaaly hard.
It's possible change [LastName] with something similar that mean [EVERY FIELD]?
Or other suggestions?
Tony from Sardinia (ITALY)

If you have hundreds of fields in your table - it's NOT properly

normalized!!!!



Short answer: No. Query criteria are field by field.



Longer answer: you can use VBA code to loop through the unbound controls on a

search form, constructing a SQL string containing criteria for those controls

in which the user entered something.



Could you explain the context? What's in the table? Why does it have hundreds

of fields?

--



John W. Vinson [MVP]

Microsoft's replacements for these newsgroups:

http://social.msdn.microsoft.com/Forums/en-US/accessdev/

http://social.answers.microsoft.com/Forums/en-US/addbuz/

and see also http://www.utteraccess.com

 

[John W. Vinson]

My client asked specifically one box where write something for filter the all data...

for example they write "MOKBA" and want see only the people birth in Mokba or
living in Mokba or named Mokba and so on... exactly as the little search box
in navigation bar do... only more bigger and inside the form...

Well, you'll have to use VBA code to write the SQL then, and it won't be
trivial.

I honestly think this is a case where the customer is NOT necessarily right.
It makes no sense to me to search (say) a number or date field for text, or
even search a text field for unrelated text. "Mokba" only has meaning in a
context.
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[Bob Barrows]

I'm tryng to replicate the box in object that can filter the
visualized data while I digitize searching in every field of the
table.

I have realized something similar with this simple code that filter
olny one field: """"" Me.Filter = "[LastName] Like '*" &
[ControlName] & "*'" Me.FilterOn = True """""

But write the some code for hundreds filed become reaaly hard.

It's possible change [LastName] with something similar that mean
[EVERY FIELD]?

At the very least you will need to "fold" your table so all the text data is
in a single column, each row identified by the table's key. To do this, you
would use a union query:

select keyfield1,...,keyfieldN,"Testfield1_Name" As TextFieldName,
TextField1 As TextValue
FROM tablename
union
select keyfield1,...,keyfieldN,"Testfield2_Name", TextField2 FROM tablename
union
select keyfield1,...,keyfieldN,"Testfield3_Name", TextField3 FROM tablename
....
union
select keyfield1,...,keyfieldN,"TestfieldN_Name", TextFieldN FROM tablename

Save the query as "qTextUnion", for example. Then, to search for "MOKBA":
SELECT keyfield1,...,keyfieldN, TextFieldName, TextValue
FROM qTextUnion
WHERE TextValue LIKE "*MOKBA*"

The problem of course will be performance.