Simultaneously create records in multiple tables

[RebeccaKT]

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!

 

[Dorian]

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".

 

[Roger Carlson]

Rebecca,

You have a much more severe problem than you even know. If you have so many
fields that you reached the 255 field maximum for a table (and have even
expanded to 4 tables), there are serious design issues with your database.
Continuing with this design will only create more headaches as time goes on.

You need to stop right now and rebuild it from scratch using the principles
of Normalization.

On my website (www.rogersaccesslibrary.com), I have a Database Design
Tutorials section
(http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=238). There
are a few topics that explain Normalization and proper design, followed by
some tutorials that walk you through several projects. I strongly suggest
you take the time to learn the basics. It will save you mounds of work
later.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L