How to create a Form that can let a User type text that is bound to a Field's Criteria

S

somebody

This may be a simple thing to do but I do not know how to do this...I
want the User to be able to type text pertaining to a Field's
records....Like a field is "Company Name" and the user enters text in
a form ie: Int* and the query gives back Company names starting with
Int....the query, as it stands now, provides ALL company names along
with other records....can someone point me in the right direction?
Thanks
 
L

Larry Linson

Well, yes, you can create an unbound Text Box into which the user enters the
information, then a Command Button to initiate the Query. In the Click event
of the Command Button, you construct the SQL for the Query, employing the
information entered by the user... here's a code sample... it assumes the
Text Box is named txtCompany.

Dim strQry as String
. . .
strQry = "SELECT * FROM tblCompanies WHERE"
strQry = strQry & " [CompanyName] LIKE """
strQry = strQry & Me.txtCompany & """"
. . .

then what you do with the SQL string is up to you... you can use it as the
RecordSource for a Form or Report, or execute it from code. (I don't just
run Queries from code and give users the Datasheet view because there are so
many things they can inadvertently do that are problematical.)

But, if you are simply trying to select one particular Company and display
its information, try using a Combo Box... use a Row Source that returns all
the Company Names... as you type, it scrolls to the closest match, and you
have the option of scrolling manually and clicking to select once you get
close. On top of all this, the Control Wizard will lead you through the
steps of constructing the Combo Box (this is sometimes called "Query by
Form").

Larry Linson
Microsoft Access MVP
 
S

somebody

Thanks Larry
Good point about the Datasheet view....I did not consider that....that
said is there a way to do this with aReport? I've tried and
cannot...Yep I'm new to Access...BTW, there are several records
associated with the Company name....so when I enter the name in the
text field it should display all the records, in this case sales
orders
Thanks!

On Sat, 4 Dec 2004 20:48:08 -0600, "Larry Linson"
 
Top