Search Function

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello,

I have a complex problem. I have some users who what a search form.
They want to be able to search by up to 5 different things. The problem is
all 5 are stored in different tables. They are connected through a master
form, but that form is going to have a lot of records. Is there anyway I can
create a search form with the 5 options and they can select any value for any
number of them? I am using some VB code that allows me to search for one
thing and partly works for another, but the other 3 do not work at all.

Thanks,

Austin J. Hagen
 
Subqueries will do the job.

This example assumes you have a form bound to the tblClient table, and you
want to filter your form so that it shows only those clients from
Springfield (a field from tblClient) and also have invoices for September
(from a completely different table):

Me.Filter = "(City = 'Springfield') AND EXISTS (SELECT InvoiceID FROM
tblInvoice WHERE (Invoice.ClientID = Client.ClientID) AND
(Invoice.InvoiceDate Between #9/1/2005# And #9/30/2005#));"

This approach lets you create some very powerful search forms.

If subqueries are new, see:
How to Create and Use Subqueries
at:
http://support.microsoft.com/?id=209066
 
I also have a similar problem. I have 5 fields to search on.
1) contract_no
2) customer_no
3) customer_name
4) contract_agreement_type
5) agreement_date

If I want to search on either 1, 2, 3 or a combination of all fields, how is
the best way to do that?

Thank you,
Geri
 
Thank you, Allen. I will try your suggestions.

Allen Browne said:
Subqueries will do the job.

This example assumes you have a form bound to the tblClient table, and you
want to filter your form so that it shows only those clients from
Springfield (a field from tblClient) and also have invoices for September
(from a completely different table):

Me.Filter = "(City = 'Springfield') AND EXISTS (SELECT InvoiceID FROM
tblInvoice WHERE (Invoice.ClientID = Client.ClientID) AND
(Invoice.InvoiceDate Between #9/1/2005# And #9/30/2005#));"

This approach lets you create some very powerful search forms.

If subqueries are new, see:
How to Create and Use Subqueries
at:
http://support.microsoft.com/?id=209066
Click to expand...
 
You can search for many things but how are you going to display the results
when they come from 5 different tables?
Sounds like a bad design to me. More details are needed on the table
structures and what exactly you are trying to achieve.
- Dorian
 
In your example...
Me.Filter="(City='Springfield') AND....

What syntax do I need to replace 'Springfield' with a user selected field
called contract_no (numeric) value (from the drop-down menu)? And also use
customer_no (the other user selected field from the drop-down menu) as the
additional filter for the search.

Thanks!!
 
If the combo box is named cboFilterContractNo, use:
Me.Filter = "([Contact_No] = " & Me.cboFilterContractNo & ") ...
 
Ok let me try to clarfiy my problem

I have a customer service team that needs to search my main frm Form 1 which
has 5 subforms.

They want a form with the option to search on up to 5 criteria(but no
necessarly all so they could choose to search 2,3 and not 1,4,5...etc)

The Customer Service search form would have

1) a combo box for Item1(which comes from a subfrm1 on Form 1)
2) a combo box for Item2(which comes from subfrm2 on Form 1)
3) a comob box for Item3(which comes from subfrm3 on Form 1)
4) a combo box for Item4(which comes from subfrm4 on Form 1)
5) two boxes to enter a minimum size and maximum size of the product(which
comes from Form 1)

Form 1 main information is from tbl1
subfrm2 inormation is from tbl2(this is a connection for a many-many
relationship)
subfrm3 information is from tbl3(this is a connection for a many-many
relationship)
subfrm4 information is from tbl4(this is a connection for a many-many
relationship)
and minimum size and maximum size are from tbl1(the problem is they are
stored in one field) I know for this you can do it easily in a query using
Between but can you do it the way I want?

Does this clarify at all?
I really need help the person who is suppose to help me is showing no
interest and this is an important function for my team.

Thanks.
Allen Browne said:
If the combo box is named cboFilterContractNo, use:
Me.Filter = "([Contact_No] = " & Me.cboFilterContractNo & ") ...

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

gg said:
In your example...
Me.Filter="(City='Springfield') AND....

What syntax do I need to replace 'Springfield' with a user selected field
called contract_no (numeric) value (from the drop-down menu)? And also
use
customer_no (the other user selected field from the drop-down menu) as the
additional filter for the search.

Thanks!!
Click to expand...
Click to expand...
 
So you want to filter your main form, based on the values from 5 different
tables.

As explained in the first reply, you can create a string to use as the
Filter property of the form. The string must look like the WHERE clause of a
query. You can mock up a query, and switch it to SQL View (View menu, in
query design) to see what the WHERE clause should look like.

Also explained in the first reply is the idea of using a subquery to look
into other tables that are not in the main query. They turn up in the WHERE
clause of the query. You can do exactly the same thing when you create the
filter string for your form, i.e. the WHERE clause can look into tables that
are not in the main form by including a subquery in the filter string.

You will therefore need to write code to create the string that includes the
value from Item1 in the main form's table, and 4 subqueries to include the
values from Item2 - Item4 to include the values from the subform's tables.

The first reply you received gave you a link about subqueries. I suggest you
get one working, and then use that experience to create the other 3 as well.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

hagen31 said:
Ok let me try to clarfiy my problem

I have a customer service team that needs to search my main frm Form 1
which
has 5 subforms.

They want a form with the option to search on up to 5 criteria(but no
necessarly all so they could choose to search 2,3 and not 1,4,5...etc)

The Customer Service search form would have

1) a combo box for Item1(which comes from a subfrm1 on Form 1)
2) a combo box for Item2(which comes from subfrm2 on Form 1)
3) a comob box for Item3(which comes from subfrm3 on Form 1)
4) a combo box for Item4(which comes from subfrm4 on Form 1)
5) two boxes to enter a minimum size and maximum size of the product(which
comes from Form 1)

Form 1 main information is from tbl1
subfrm2 inormation is from tbl2(this is a connection for a many-many
relationship)
subfrm3 information is from tbl3(this is a connection for a many-many
relationship)
subfrm4 information is from tbl4(this is a connection for a many-many
relationship)
and minimum size and maximum size are from tbl1(the problem is they are
stored in one field) I know for this you can do it easily in a query using
Between but can you do it the way I want?

Does this clarify at all?
I really need help the person who is suppose to help me is showing no
interest and this is an important function for my team.

Thanks.
Allen Browne said:
If the combo box is named cboFilterContractNo, use:
Me.Filter = "([Contact_No] = " & Me.cboFilterContractNo & ") ...

gg said:
In your example...
Me.Filter="(City='Springfield') AND....

What syntax do I need to replace 'Springfield' with a user selected
field
called contract_no (numeric) value (from the drop-down menu)? And also
use
customer_no (the other user selected field from the drop-down menu) as
the
additional filter for the search.

Thanks!!

:

Subqueries will do the job.

This example assumes you have a form bound to the tblClient table, and
you
want to filter your form so that it shows only those clients from
Springfield (a field from tblClient) and also have invoices for
September
(from a completely different table):

Me.Filter = "(City = 'Springfield') AND EXISTS (SELECT InvoiceID FROM
tblInvoice WHERE (Invoice.ClientID = Client.ClientID) AND
(Invoice.InvoiceDate Between #9/1/2005# And #9/30/2005#));"

This approach lets you create some very powerful search forms.

If subqueries are new, see:
How to Create and Use Subqueries
at:
http://support.microsoft.com/?id=209066


I have a complex problem. I have some users who what a search form.
They want to be able to search by up to 5 different things. The
problem
is
all 5 are stored in different tables. They are connected through a
master
form, but that form is going to have a lot of records. Is there
anyway
I
can
create a search form with the 5 options and they can select any
value
for
any
number of them? I am using some VB code that allows me to search for
one
thing and partly works for another, but the other 3 do not work at
all.
Click to expand...
Click to expand...
Click to expand...
 
Thanks, Allen. I'll try it out today. If I get stuck, may need your further
advice. Thank you!
 
This is generally done with a criteria form with unbound controls. In your
case you would have five comboboxes. The key here is to set the rowsources
of the comboboxes so that they only return values that are already in the
database. All five fields that the comboboxes represent need to be
represented in a query that serves as the recordsource for the form or
report you wish to display. You then need an expression in the criteria of
each of the five fields that looks like:
Forms!MyCriteriaForm!NameOfCombobox1 Or
(Forms!MyCriteriaForm!NameOfCombobox1 Is Null)
Note: In the other fields change 1 to 2, 1 to 3, 1 to 4 and 1 to 5.

The last part of the above expression is what makes it possible to enter one
or more criteria. You don't have to enter them all.
 
Do you mean create 5 separate queries (or how many combo boxes I have)?
Then, add under the criteria for each query the Forms!... line as you
described? what code do I need to add in the Event Procedure?

Thank you!

geri
 
I am new at having to write code in Access so I am totally confused here.
Does the expression go into the Event Procedure, and if so what happens after
that. I am not sure what code I need to write. I know what I want to do but
am lost about how to do it. Also do I then need a button that says ok or
something to start the search and have the main form appear?

Thanks
 
Your master table needs to be based on one master query. The query needs to
include PKID1, PKID2....PKID5 where PKID is the primary key of each of your
five tables. You need five comboboxes where the user can select PKID from
each of your tables. Of course you wouldn't display the PKID but would
display a field in your table that is meaningful to your users. The value of
each combobox would equal the valie uf PKID however.
The criteria for PKID1 in your master query would be:
Forms!NameOfSearchForm!NameOfComboBox1 Or
(Forms!NameOfSearchForm!NameOfComboBox1 Is Null)
The criteria for PKID2 in your master query would be:
Forms!NameOfSearchForm!NameOfComboBox2 Or
(Forms!NameOfSearchForm!NameOfComboBox2 Is Null)
The same for PKID3 thru PKID5.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com

If you don't get the help you need in the newsgroup, I can help you for a
very reasonable fee. Over 1000 Access users have come to me for help.
 
Back
Top