Autopopulate Form

[Dave]

I submitted this before and it never appeared in this forum. I'll try again.

I have a form where I enter ProductID and I want the adjacent form fields to
automatically fill in the associated ProductName, Dept, Supervisor, and
Email. I have the 6 depts, supervisors and emails in one table (6 rows).

I'm not sure how to set up the table for ProductID and ProductName for each
dept, because each dept has many products. Do I make a separate product list
table for each dept? How would I program it to search all 6 product tables
for associated info when I enter the ProductID in the main form?

Thanks,
Dave

 

[Klatuu]

No, you do not want a product table for each department.
You want one product table with a field identifiying to which department it
belongs.
If it is possible that more than one department can have the same product,
you need a junction table that has a record for each product/department
combination.

Do it with 6 tables and I will be waiting for your next question:
How do I join my six tables so I can get them all on one report.

 

[Rohn Everson]

Dave,
I found this link to be very useful in starting my database.

Description of database normalization basics in Access 2000.
http://support.microsoft.com/?kbid=209534

Regards,
Rohn

 

[Dave]

6 tables? - To clarify, I have one table with 6 records, 1 for each
department with 3 fields: Dept, Supervisor, Email. You are suggesting
another table for ALL products and fields for: ProductID, ProductName, and
Dept. It's not clear how I need 6 tables.

Thanks,
Dave

 

[Klatuu]

I was resonding to this:
How would I program it to search all 6 product tables
for associated info when I enter the ProductID in the main form?

Assuming your product table has a department code field in it, bind a combo
box to that field using the 6 row department table as the combo's row source.
The Department code in your product table should be a foreign key to the
primary key of the deparment table. I would make that field a Long data
type in the product table and an Autnumber field in the department table.
Set your deparment table up like this:

DeptID - Autonumber - PK
DeptDescr - Name of the department
DeptSupervisor
DeptEmail

 

[Dave]

I will try your suggestion and condense the 6 product tables to one product
table. Thanks for the help.

A side note - It's been difficult to access this forum this week. I keep
getting "no results" messages and when I do get a list of postings, I rarely
can find the response to my post. It took numerous tries to find this one.
I'm am signed in and I'm using the proper search criteria. Is this a forum
website problem, or is it likely my computer? Any ideas would be
appreciated.

Thanks,
Dave

 

[Klatuu]

These groups go nuts from time to time.
A couple of weeks ago I opened a new post. It was someone complaining I had
left them adrift and would no longer help them. Turns out I wasn't getting
notifications for a couple of day. I also had a problem where I couldn't
respond to any posts for a day and a half.