Code to filter form

  • Thread starter Thread starter John Martin
  • Start date Start date
J

John Martin

I wish to create an input box into which the user can type all or part of a
field to open a form which will show all the fields matching the criteria.
According to "Help" a SQL expression (without the word WHERE) should do the
trick. I imagined that one of the following would work, but neither does:

DoCmd.Openform "Table Name", acViewnormal, , Field Name = "ma" & "*"

DoCmd.Openform "Table Name", acViewnormal, , Field Name LIKE "ma"

Or am I going about this the wrong way? This must be a common procedure. You
will appreciate that I am more than eligible for "gettingstarted"
 
John,

Is "Table Name" the name of your form? Quite unusual, but I'll assume it
is, or you'll have to change it to the actual name of the form you are
trying to open! Anyway... the syntax in the WHERE clause of the OpenForm
is not correct. Change to:

DoCmd.Openform "Table Name", acViewnormal, ,"[Field Name] = '" & vStr & "*'"
(watch out for text wrapping in your newsreader!)

where I have assumed that vStr is a variable which is assigned the
string you are filtering on (ma in your post). This can be doen through
an InputBox in the code, or by reading a control on a form or... you are
not specifying how you intend to do it.
Note:
* the whole WHERE clause is a string argument, therefore it must be
enclosed in quotes;
* I have enclosed the field name in [], so Access can handle it if it
indeed contains spaces.

HTH,
Nikos
 
Back
Top