Form and Subform Search Query

C

Casualman123

I have a table with 2572 records in it, and i want to have a form for these
records where a i can add, update and delete these records. I have managed
to do this but it gets tedious having to search through a couple of thousand
reocrds to find the one you want.

So what i want to be able to do is set up a search facilty on the form so
when i type in the product name and size it will display the product/s in
either the form or at least a sub form.
 
D

Damon Heron

Create a new blank form, and set the record source to the table with the
records. First, add a combobox to it (before adding any other textboxes).
You should then get a Wizard for the combobox. Select the third option,
that says "Find a record on my form based on the value I selected in my
combobox" then complete the wizard. Hopefully, your table is setup with an
autonumber ID and a record name that is recognizable (like Customer Name,
for instance). Finally, add all the textboxes and associate them with your
table fields. Now, when you open the form, you can search the combobox for
the the record you want and it will go to that record. Additionally, you
can type the first few characters of the record in the combobox which will
take you to that section of the records -like "Wil" for "Wiley Coyote
Anvils":)

HTH
Damon
 
C

Casualman123

The combo box displays all 2572 records, your post has helped to some extent,
but my table has groups of products, e.g.

Product Code Diameter PRice Descripton

ADT300
ADT400
BFU1000/45 1000 £12.99 Fabricated bend dia = 1000 angle
= 45

there is many different items, but all the products have a unique product
code. and i would like to make it easier for the user to find a product.
Maybe by using a query so they can enter the name of a product and the size
and it displays it in the form, i have tried this way but it displays the
results in a seperate table.

If i try and enter a new column in the table called product name and use the
combo box for this then it displays 2572 of the same name. e.g

Product Name Access Door
Access Door
Access Door
Access Door
Access Door
Fabricated Bend
Fabricated Bend
Fabricated Bend
Fabricated Bend
Fabricated Bend
Fabricated Bend
Fabricated Bend
Fabricated Bend
i would like it to have only one of each and when the item is selected then
the form will display only the items with the name i have selected.

cheers for your help with the first post, hope you are able to help
 
D

Damon Heron

There are two ways of looking at this. Every record in a table should be
unique, and that can be established by a primary key and autonumber. But
looking for a record by autonumber is not user friendly, so a Name is
preferable. If you have names that are the same but there may be multiple
instances of the product, then I would use two comboboxes - the first to
select the name (or Type) as in your example "Fabricated Bend" and then a
second combobox to select the product code based on the first combo
selection.
This really requires a new table for ProductType, consisting of a minimum of
TypeID, Type.
Then your first table would have a TypeID linked to this new table.

The first combobox would have a record source of the ProductType table, and
the second combobox would have a query based on the value of the first
combobox. So, there may only be 10 types of product in the first table,
once one is selected, the user then has the choice in the second table of
only those products.
this idea of two combos is explained at this website:
http://www.mvps.org/access/forms/frm0028.htm

Allen Browne's excellent website also has another approach:
http://allenbrowne.com/ser-32.html

Good luck!

Damon
 
Top