simple 'After Insert' event question...

[nicktheblue]

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

 

[llkhoutx]

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.

 

[nicktheblue]

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

 

[DBL]

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.