automatically create new records in second table

[bobjackson]

I am currently developing a database, that due to the number of fields requires several tables. I currently have "tblmain" and "tblprocess". Both with a primary key "rec" which is set to an autonumber. Is there a way that when I create a new record in "tblmain" that it creates the corresponding record in "tblprocess"

Never smile at work, they may think you're enjoying yourself too much and cut your pay.

 

[PHV]

Provided that tblprocess.rec is defined as a long integer, Primary Key and, in the RelationShips windows, Foreign Key pointing to tblmain.rec, you may simply follow the wizard.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[bobjackson]

tblprocess.rec is defined as a long integer, primary key, what do you mean foreign key pointing to tblmain.rec and following the wizard. I can't find a wizard in the relationship window is it something recent as I'm using Access 2000.

Never smile at work, they may think you're enjoying yourself too much and cut your pay.

 

[PHV]

In the relationship window create a one to one relation between tblmain.rec and tblprocess.rec
Then create the main form and the process form.
Finally, in the main form follow the wizard to insert the process form as a subform.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

Thanks,

I've done that, but it still doesn't automatically create a new record in tblprocess unless I add to the fields in the subform. Which I don't want to do from this form.

 

[dhookom]

To automatically create a new record in a "child" table when a record is created in a "master" table, you need code in the form that is bound to the master table. Consider add to the After Insert event of the form. Something like:

Private Sub Form_AfterInsert()
    Dim strSQL As String
    strSQL = "INSERT INTO tblChild (fkField) " & _
        "Values (" & Me.PK & ")"
    DoCmd.RunSQL strSQL
End Sub

Duane
MS Access MVP
Find out how to get great answers faq219-2884.