Hello forum,
easy question for you guys (I hope).
when I create a new record using a form, I think I need to use the 'After Insert' event to make access create a new record in a different table, passing thru' the OrderID field value to the other tables OrderID field. For starters, I don't know how to make access do this. Secondly, I was wondering if it could be done simply by using a query to give a full list of all OrderID's and then requerying using after insert event. If somebody can give me a REALLY simple answer to the first question, then forget the second.
I have lots of books on access VBA but getting answers to do simple things seems to be beyond the remit of the authors (or perhaps I don't read them properly).

Thanks in advance,

Nick

On the AfterInsert event, write a little code which uses DAO coding to add the record, example

dim db as DAO.database
dim rs as DAO.recordset

set db=currentdb()
set rs=db.openrecordset _("YourTableName",dbopendynaset)
rs.addnew
' first field in 2nd table
rs!field(0) = "field off form"
' second field in 2nd table
rs!field(1) = "another field off form"

etc.

rs.update
rs.close
set rs=nothing
set db=nothing

Note: use the expression builder to build the references to the fields on your form.

Thanks for reply 'llkhoutx'!
Access didn't like this line -
set rs=db.openrecordset _("YourTableName",dbopendynaset)
but I found a different solution that does work using SQL statements, searching the 'knowledge base'.
Is this because I am using a different version of Access than yourself? I am using 2000. thanks all the same!

Nick

You would probably need to add the DAO Microsoft 3.6 reference library to get the code llkhoutx gave you to compile. Open the module window and click Tools> References and search for and tick Microsoft 3.6 DAO reference library. You should be able to compile the code with no problems after you add that.