access 97, search database 1

[mercury1]

I have a database with the following fields:
Product, Vendor, Quantity, Mfr, Price & Date.

I would like to set up a form that let me search database and also enter new data.

Thank you!!

 

[petrosky]

Hi Mercury1,

Here is an easy way to have a search combo.

Build a form based on your table.
Place all the fields on the form.
click on the "magic wand" and then the combo box control.

Place the combo where you like, (form header or footer is good) then click "find a record on my form based on a value I selected in my combo box"
Click next to choose what field you would like to search on eg. products.

I hope this helps.

Peter Remember- It's nice to be important,
but it's important to be nice

 

[mercury1]

Peter,thank you for your quick reply.

The Product field has many of the same items with different Vendors.

I need to search database and have all the results appear in datasheet format so that I can see all results.

Thank you.

 

[aaahhhrrggghhhh]

Hello mercury1

If you have a number of vendors per product then this is a 1-to-many relationship. You need to create a form for vendors (maybe use the form wizard), then create a form for product. There must be a relational-key in both, so for example, product1 is "white Loaf", it has three vendors (spar, centra and marks and spencers). The tables (for this example) would be populated this way :

Product Table
-------------

ProductID 1
Product Description White Loaf

Vendor Table
------------

VendorID SP
Vendor Name Spar

VendorID CTR
Vendor Name Centra

VendorID MAS
Vendor Name Marks and Spencers

OK, so now you need to create a link-table to link this info.

ProdVend Table
==============

ProdVendID 1
ProductID 1
VendorID SP

ProdVendID 1
ProductID 1
VendorID CTR

ProdVendID 1
ProductID 1
VendorID MAS

OK, so now you have the tables created, you need to create a form called ProdVend. You can neaten it up by using combo boxes to allow the user to select actual descriptions of vendors and products, rather then enter the actual ID's.

Once you have created the form ProdVend, save it. Now go to the Products form and insert a subform. Use the keys in ProdVend to create your forms.

Hope this helps.
Mr Big

Dont be small. be BIG