Julie said:
I have 2 combo boxes one which is the account number and the other is the
name combo box which allows the user to enter the entire account number or
just a portion of it. I purposely put an account number in the memo box as
sometimes we will have old account numbers that we make reference to when the
client gets a new account number (different type of account gives them a new
account number). When I enter information in the memo box which is a text
box and do an account search, it isn't bringing up the account number that
was entered into the memo box even though this field is in my table...what do
I do to get it to search all the fields in my table?
Hello again.
In the Query Builder, there are many lines available for criteria.
Entries on the same line combine to create a criterion (match if x AND
y). Items on separate lines represent alternative criteria (match
either x OR y). So you could have two criteria lines, one to match the
value in Account Number, and one to match the value in the Memo field -
it'll return rows where either match. You can use parameters in your
query (each will prompt in succession) or you can use filter-by-form -
note the "OR" tab at the bottom of the filtering version of the form.
You say you're using a combo box, and you're trying to create a custom
form. A combo box is used when you want the option to present the user
with a range of predetermined choices - this only makes sense if you
have a relatively small number of options, and it doesn't sound suitable
for an account number, where there could be thousands. You'd be just as
well off with a text box.
Think either filter or query. One scenario would be to have a command
button to fire the search off once you've entered some characters
(possibly including wildcards) in the text box. If using a filter, the
command button's On-Click event-procedure would apply a filter written
to refer to the value found in the text box. The filter belongs to the
form, and is created using the Form's property dialogue.
Alternatively, the command button could run a query written to refer to
the value in the text box. This page gives details of how to write
those references:
http://www.mvps.org/access/forms/frm0031.htm
However, alarm bells are ringing - your table design sounds perfectly
barbarous. Sounds like you need a table for clients and a table for
accounts (containing the client-id as a "foreign key"). More than one
account? More than one record. Need to link them? Search for the
client-id. You MUST get your table design right at the outset or you
will drown in complexity.
From the way you write, you are a beginning user, and you're a bit out
of your depth trying to use Access to create forms. I'd stick to using
the built-in facilities (filters and parameter queries) for now. When
you're ready, you'll need to do some studying on how to use controls on
forms. You can get really good, affordable training (and some free
sample lessons) on Lynda.com.
Phil, London
