Data Entry form dupilication

[Billy12excel]

Newbie Question.
Working on new db for inventory.
Will use a form for data entry, form will have 11 columns.
Will have separate tables for different suppliers. Is there way to have one master form, that will then allow me to save that form, with the supplier's name on a form , thus making that a new table and form (tbl_abc and frm_abc, for that individual supplier. Would this be done best with a small macro? Office 2000

Hope this makes sense! thank you!

 

[Norie]

You should use one table for suppliers, not a separate table for each supplier.

With that setup it's unlikely you would need any code, depending on what you actually want to do.

What data are you entering?

 

[Billy12excel]

ProductID, Prod_Description, Size, Color, Location,
Rack, Bin, Cost, HowPacked/orCase/Each, Notes, ReorderQuantity

The problem is the suppliers ( at least 6 or 7 of them have literally about 5000 to 20,000 products each, many of which have the same or very similar items, ( I order based on availability and time for freight delivery ). While at most I may only stock a max of 100 to 250 from any one supplier. I would like to be able to have the ability to quote on any of their many many other products ( I think I can get an excel of the larger suppliers to avoid having to type it all in )

Thus was my reason for separate suppliers tables for the time being, till I understand how to pull it all together. Trying to keep it very simple till I have started, and then working on integrating it with invoicing. If I'm out to lunch let me know.
Thank you for your reply.

 

[xenou]

Hi,

Just copy the original form and table. There's only seven - no need for macros.

Try very hard to keep the tables exactly alike as some day you probably *will* want to merge this supplier data into a single table.

ξ

 

[Norie]

Having a separate tables for each supplier is not going to simplify things, it'll probably complicate things.

To start with you should have a table for suppliers with fields relevant to the supplier and a primary key to uniquely identify each supplier.

The primary key should be unique but also arbritrary, it shouldn't be based upon something like the suppliers name.

Relevant fields would be the supplier name, contact details etc.

Usually the best candidate for this type of field is an AutoNumber.

Then you would have a product table which lists all products from all suppliers.

Again this would have a primary key to uniquely identify each product, and again using an AutoNUmber is probably the best option.

Each product record would also have a foreign key field which would be the primary key from the suppliers table.

This field links each product back to the supplier.

That's the basics really, you would need various other fields in either table and of course other tables.

If you start with a setup like this it shouldn't be too hard to add all the other necessary elements for the database.

 

[Billy12excel]

Ok, this gives me some thoughts to consider.
I'll poke around and see if I can find some simple examples, that will hopefully allow me to also search through that many records to find what Im looking for as quick as possible.

Thank you!