Adding New records in multiple tables

[Leo]

I have a database with several tables (tblONE, tblTWO,...)
Table tblONE serves as source for frmONE which is the first form the user
enters the data. All tables except tblONE (and therefore all the forms except
frmONE) have an AutoNumber fields as TWOid, THREEid etc. The tblONE,
instead, has a field called [Account_Number] which is an autonumber field
(formatted as LongInteger). All other tables also have a field called
[Account_Number] formatted as LongInteger. All the table have established
relationship to tblONE.

When the user enters new information in a blank frmONE the auto number
generated in tblONE serves as the clients AccountNumber. When another related
form is open the user has to enter Account_Number manually. And the subform
in those forms bring other pertinent data from tblONE.

What will be the Sql code and where will I enter it, so that when frmONE is
completed and the user closes it the autonumber generated in the
[Account_Number] will automatically entered into all other tables as new
records?

Thanks
Leo

 

[PieterLinden via AccessMonster.com]

Just create the relationships between tblOne and the other tables. If you've
created the indexes properly, you should end up with one-to-many
relationships. Then you can drop your subforms into your main forms and you
should be able to enter the data and Access will automatically enter the
correct foreign key value.

I have a database with several tables (tblONE, tblTWO,...)
Table tblONE serves as source for frmONE which is the first form the user
enters the data. All tables except tblONE (and therefore all the forms except
frmONE) have an AutoNumber fields as TWOid, THREEid etc. The tblONE,
instead, has a field called [Account_Number] which is an autonumber field
(formatted as LongInteger). All other tables also have a field called
[Account_Number] formatted as LongInteger. All the table have established
relationship to tblONE.

When the user enters new information in a blank frmONE the auto number
generated in tblONE serves as the clients AccountNumber. When another related
form is open the user has to enter Account_Number manually. And the subform
in those forms bring other pertinent data from tblONE.

What will be the Sql code and where will I enter it, so that when frmONE is
completed and the user closes it the autonumber generated in the
[Account_Number] will automatically entered into all other tables as new
records?

Thanks
Leo

 

[Leo]

I do understand that and I have done that. And it already does what you
described. But what I want to do is this.

frmONE is an admission form. After the client is admitted the autonumber
generated sereves as AccountNumber. Then frmTWO is opened to enter more
information pertaining to the same client. When the user initially opens the
frmTWO on a new client he has enter the AccountNumber manually so that the
forign key and subforms are activated.

The question is whether the user can bypass this procedure of adding the
account number manually on frmTWO and frmTHREE, frmFO... and instead when he
saves or closed the frmONE that accountnumber generated could be added
automatically on all other tables so that when he or others open the frmTWO
or frmTHREE it will open automatically instead of a blank form (for the first
time ofcourse)?

Thanks
Leo

Just create the relationships between tblOne and the other tables. If you've
created the indexes properly, you should end up with one-to-many
relationships. Then you can drop your subforms into your main forms and you
should be able to enter the data and Access will automatically enter the
correct foreign key value.

I have a database with several tables (tblONE, tblTWO,...)
Table tblONE serves as source for frmONE which is the first form the user
enters the data. All tables except tblONE (and therefore all the forms except
frmONE) have an AutoNumber fields as TWOid, THREEid etc. The tblONE,
instead, has a field called [Account_Number] which is an autonumber field
(formatted as LongInteger). All other tables also have a field called
[Account_Number] formatted as LongInteger. All the table have established
relationship to tblONE.

When the user enters new information in a blank frmONE the auto number
generated in tblONE serves as the clients AccountNumber. When another related
form is open the user has to enter Account_Number manually. And the subform
in those forms bring other pertinent data from tblONE.

What will be the Sql code and where will I enter it, so that when frmONE is
completed and the user closes it the autonumber generated in the
[Account_Number] will automatically entered into all other tables as new
records?

Thanks
Leo

 

[Piet Linden]

I do understand that and I have done that. And it already does what you
described. But what I want to do is this.

frmONE is an admission form. After the client is admitted the autonumber
generated sereves as AccountNumber. Then frmTWO is opened to enter more
information pertaining to the same client. When the user initially opens the
frmTWO on a new client he has enter the AccountNumber manually so that the
forign key and subforms are activated.

The question is whether the user can bypass this procedure of adding the
account number manually on frmTWO and frmTHREE, frmFO... and instead whenhe
saves or closed the frmONE that accountnumber generated could be added
automatically on all other tables so that when he or others open the frmTWO
or frmTHREE it will open automatically instead of a blank form (for the first
time ofcourse)?

Thanks
Leo

Just create the relationships between tblOne and the other tables.  If you've
created the indexes properly, you should end up with one-to-many
relationships.  Then you can drop your subforms into your main forms and you
should be able to enter the data and Access will automatically enter the
correct foreign key value.

I have a database with several tables (tblONE, tblTWO,...)
Table tblONE serves as source for frmONE which is the first form the user
enters the data. All tables except tblONE (and therefore all the formsexcept
frmONE)  have an AutoNumber fields as TWOid, THREEid etc.  The tblONE,
instead,  has a field called [Account_Number] which is an autonumberfield
(formatted as LongInteger). All other tables also have a field called
[Account_Number] formatted as LongInteger. All the table have established
relationship to tblONE.
When the user enters new information in  a blank frmONE the auto number
generated in tblONE serves as the clients AccountNumber. When another related
form is open the user has to enter Account_Number manually. And the subform
in those forms bring other pertinent data from tblONE.
What will be the Sql code and where will I enter it, so that when frmONE is
completed and the user closes it the autonumber generated in the
[Account_Number] will automatically entered into all other tables as new
records?
Thanks
Leo

If you create relationships between the tables, then the forms should
populate the foreign keys for you...