Need Help adding one record to all tables 1

[vp7799]

I am building a database that has many tables. The primary key for all tables is a social security number. When I Enter a social security number into the primary table I want all other tables to have a record of that social security number. I want this becuase when I do a query on a person I may have a field in the query from a table where the person may not have been entered and they will not show in the query even though I want them to. Please help if you need more description of the problem please let me know.

 

[KenReay]

HI

Without questioning the wisdom of your table desgin, which may or may not be right:

In the afterInsert event of the form used to maintain the 'main' table put.


Dim Db As Database
Db.Execute "INSERT INTO tblOne (SSNo) VALUES (" & txtSSNo & ""
Db.Execute "INSERT INTO tblTwo (SSNo) VALUES (" & txtSSNo & ""

you need to substitute your tablenames, and column names of course, and you need a Db.Execute line for each table you wish to add too.

In all of the tables, ensure SSNo is defined as a unique key (or the prime key if appropriate), in this way, if a SSNo record exists in that table, the Db.Execute will simply fail.

Hope this helps

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
UK
kenneth.reaySPAMNOT@talk21.com
remove SPAMNOT to use

 

[vp7799]

Thanks for the help. I am still having trouble though. I went and made a form and made a add record button. In the record button i but the
Db.Execute "INSERT INTO tblOne (SSN) VALUES (" & txtSSN & "".
and nothing happended. The SSN was not added to the other table. Did I put this in the wrong place. I then tried

Dim Db As Database
Db.Execute "INSERT INTO tblOne (SSNo) VALUES (" & txtSSNo & ""

and got an error Compile error User-defined type not defined. I can not figure out what this means.
and i am probaly not using the best design,
Thanks for any help

 

[mymou]

Hi vp7799

The important part of KenReay's post was the first sentence. Access is suppossed to be really easy. If things are getting difficult - for something that should be simple - then you are probably doing something wrong.

There are different types of joins - just right click on a join (in a query) and look at the 'Join Properties'. Should be easy from there.

Your method will work - but really does break a lot of the rules for databae design.

Stew

 

[KenReay]

Hi

Sorry I missed out the SET Db = CurrentDb()

You probably have ADO rather than DAO

Dim Db as DAO.Database
Set Db = CurrentDb()
Db.Execute etc

You also need to have the DAO library in your references.

It may be possible to do same thing with ADO, but offhand I do not know exact syntax



Hope this helps

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
UK
kenneth.reaySPAMNOT@talk21.com
remove SPAMNOT to use

 

[vp7799]

Thats IT, that is what I have been pulling my hair out over. AHHHHHHHHHH. Thank you both. I just feel a little slow, ok really slow. In case you cant tell building databases is not my job, but something of a hobby. the Join Properties solved the whole problem. Thanks, Thanks, Thanks. I am going to go kick my self now.