Using a "wildcard" as a Search Tool

  • Thread starter Thread starter krystle h. via AccessMonster.com
  • Start date Start date
K

krystle h. via AccessMonster.com

Hello,

I have been using Access for awhile in the work setting, i.e. forms, tables, -
the basic. I have yet to use Visual Basic which I think I need to complete a
task. I would like to create in my database something similar to a "search
engine". The Database is compiled of Vendors and their trades along with
other company information. Through this filter, I would like the user to be
able to use for instance, "paint", as a wildcard and be able to bring up all
vendors that have paint listed as one of their trades. I hope this is not as
difficult as I think it's going to be but if someone could please help, I
would greatly appreciate it.
 
The answer to your question will depend on how your tables are set up.

If one vendor supplies many products, and any one product can come from
multiple vendors, you have a many-to-many relation, so you probably have 3
tables:
- tblVendor (one record for each vendor), with a VendorID primary key;
- tblProduct (one record for each product, with a ProductID primary key;
- tblVendorProduct (one record for each combination of vendor + product),
fields:
VendorID who supplies this product
ProductID what the product is.
So, if Product 99 is a 1 gallon tin of white Duluxe exterior, and you can
purchase it from vendors 6, 9, and 15, you will have 3 records for this in
tblVendorProduct:
6 99
9 99
15 99

If that is how you have set the tables up, you will have a form for the
Vendors, with a subform for the product supplied by those vendors (bound to
tblVendorProduct). Now you want to filter the Vendors form to only those who
supply a particluar product.

If that's the approach you have, see:
Filter a Form on a Field in a Subform
at:
http://allenbrowne.com/ser-28.html
The solution does involve some VBA code.
 
Back
Top