SQL Statement Error

[mugun]

Hello Members, I am currently having problem in Access relationship table coding. Ok this is how my Database it is:

FirstDB(ID,UserID,FirstName,LastName,Title,Nick)
SecondDB(ID,Company,Address,City)

That is sample of DB. The ID column is AUTONUMBER FIELD type. So The FirstDB --> ID is our Primary Key and SecondDB-->ID is our Foreign Key which is LONG INTEGER FIELD type. The Both Primary and Foreign Key are already set Enforce Referential Integrity. Ok now I am going to add new data to UserID,FirstName,LastName,Title,and Nick column. So I use like this :

INSERT INTO FirstDB(UserID,FirstName,LastName,Title,Nick) VALUES (‘mugun’,’xmen’,’two’,’Mr’,’Xmen ’)

The above statement already adds our new data to FirstDB Table. But what I want it is, I am going to add more info about Company, Address and City based on that contact in SecondDB Table. How should I can do that? Please guide me as soon as possible. Thanx In Advanced.

-mugun

[skhanal]

First you have to get the ID value of the record you inserted in FIRSTDB.

select ID
from FIRSTDB
where UserID = 'mugun'
and FirstName = 'xmen'
and LastName = 'two';

I am assuming that you will get only one ID from the above query, if you get more than one you need to add more clauses in where condition.

Then use that ID to insert into seconddb, assuming ID you get is 50

INSERT INTO ID,Company,Address,City)
VALUES (50, 'yourcompany','youraddress','yourcity');

[mugun]

Thanx, I get your point there. But do you have any features in SQL that automatically Insert ID into SecondDB. Thanx in advanced.

[skhanal]

You will have to use VBA to do that. If it is SQL Server I can use something like

insert into firstdb ...

set @newid = @@identity

insert into seconddb (@@identity,...)


in a stored procedure. Since Access does not have a stored procedure you will have to do the same in VBA code.

[mugun]

Thanx skanal. I can understand it very well.