Insert Into

[Guest]

I have an ADD new project form. It is linked to 5 tables.
The tblproject.projID needs to create primary keys to 4 of the other tables
as part of their IDs
If I put txtboxes to the other 4 tables on the form I can get it to work if
I type in the projID in each box. But, I want this to happen without the end
user having to type it in. Is there a way to write a sql to do this when the
form is closed or ???

tblPlans.PlansID
tblDecor.DecorID
tblFinancial.FinancialID
tblPermits.PermitsID

Format is as follows LLLL being the ProjID

LLLL"_PL"
LLLL"_DE"
LLLL"_FI"
LLLL"_PE"

Thanks!

 

[John Vinson]

I have an ADD new project form. It is linked to 5 tables.
The tblproject.projID needs to create primary keys to 4 of the other tables
as part of their IDs

Ummm... why?

Creating empty "placeholder" records is almost NEVER necessary. If you
don't have any data to put into the Plans, Decor, Financial or Permit
tables... don't put any record into the tables at all!

You can use a Form based on the Project table, with subforms based on
these tables; use the ProjID as the master/child link field. The user
won't need to type the ID in, or indeed even see it.

Format is as follows LLLL being the ProjID

This kind of "composite" field is generally neither necessary nor a
good idea. Just use ProjID as a foreign key, and give each child table
its own Autonumber Primary Key.

John W. Vinson[MVP]

 

[Guest]

I followed your directions and now it all works. This was a part I hadn't
worked on since I started 8 mos ago working with Access. Now I understand the
design was convoluted!!

Thanks!