Simultaneously create records in multiple tables

I'm creating a project database for my company in access. We use a specific
Project Number for each of our projects (i.e. 09-01-100).
Each project has so much information that I actually used all the fields in
my first table! It has expanded to four tables that list each project (i.e.
09-01-100) and continue on with that project's information. The primary key
for each table is the Project Number and they are referentially enforced.

My problem is, when I want to add a new project, it needs to be added to all
four tables. It is cumbersome to re-type the project number to each one,
though I found a way to do it even though its referentially enforced.

Is there a macro or some way that I can click a button saying "Add New
Project" type in the new project number once, and Access automatically adds a
record to all four tables?

I don't know programming or fancy language, so please keep directions as
simple as possible! Thank you!

Yes this can be done, you need to add a command button to a form and have it
run an event procedure as follows:

On Error GoTo ER
CurrentProject.Connection.BeginTrans
CurrentProject.Connection.Execute "INSERT INTO tblTable1 (ProjectNumber)
Values('09-01-100')",,adExecuteNoRecords
CurrentProject.Connection.Execute "INSERT INTO tblTable2 (ProjectNumber)
Values('09-01-100')",,adExecuteNoRecords
CurrentProject.Connection.Execute "INSERT INTO tblTable3 (ProjectNumber)
Values('09-01-100')",,adExecuteNoRecords
CurrentProject.Connection.CommitTrans
Exit Sub
ER:
CurrentProject.Connection.RollbackTrans

I'm assumning the Project number is the primary key of each table.
-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".


"RebeccaKT" wrote:

> I'm creating a project database for my company in access. We use a specific
> Project Number for each of our projects (i.e. 09-01-100).
> Each project has so much information that I actually used all the fields in
> my first table! It has expanded to four tables that list each project (i.e.
> 09-01-100) and continue on with that project's information. The primary key
> for each table is the Project Number and they are referentially enforced.
>
> My problem is, when I want to add a new project, it needs to be added to all
> four tables. It is cumbersome to re-type the project number to each one,
> though I found a way to do it even though its referentially enforced.
>
> Is there a macro or some way that I can click a button saying "Add New
> Project" type in the new project number once, and Access automatically adds a
> record to all four tables?
>
> I don't know programming or fancy language, so please keep directions as
> simple as possible! Thank you!