Creating new records... despite lookup tables

[meanswell]

I'm trying to fix a bug in a MS Access 2000 database that tracks sales
prospects & clients. It won't let me create a new employee record,
because "a related record is required in the lookup table". This
lookup table connects employees, companies & addresses, and only
contains the autonumber fields for each connecting table, plus it's own
autonumber field.

I'm using a lookup table because some companies have multiple locations
AND some employees have multiple addresses (mainly mailing, e.g. PO
Box) & physical (where the office actually is). And of course
companies generally have multiple employees.

But I'm having a hard time forcing the database to create a new record
in the lookup table when I want to add a new employee.

Any thoughts? I'm stumped & frustrated.

 

[Allen Browne]

You have 4 tables:
- Employee: one record for each employee, with EmployeeID primary key.
- Company: one record for each company, with CompanyID p.k.
- Address: one record for each address, with AddressID p'k.

The 4th table has fields:
ID AutoNumber primary key
EmployeeID relates to Employee.EmployeeID
CompanyID relates to Company.CompanyID
AddressID relates to Address.AddressID.
so it says contains records that say:
Employee 77 works for Company 9 at Address 15.
Employee 2 works for Comapny 21 at Address 13.
etc.

Now you are making a form for the 4th table. You probably have 3 combo boxes
so you can select the employee, company and address. Or perhaps you have a
main form for Company, and a subform bound to the 4th table. The subform
will have 2 combos (because the company is there in the main form.)

When entering a new record in this form, you cannot enter a new employee at
the same time. You could use the DblClick event of the combo to open the
form where you enter the new Employee. Then use the AfterUpate event of this
form to requery the combo on the original form so it gets to hear about the
new employee.

I'm not sure I have understood you correctly, so please post a reply if you
have further questions. (The 4th table is not a lookup table: the other 3
function as lookups for it.)