getting the autonum from one table and storing it in another?

[stuartam]

hi, one last question for today

i have a form and am using it to store the info in one table and some in another table ( see my other topic if interested :http://www.access-programmers.co.uk/forums/showthread.php?t=70834 )

i want it to store the autonumber created when the data is added to the history table to another table this i hope will make it a lot easer for me to retreave the pack info and the latest ( most up to date ) history infomation )

any ideas on how i can get this to work, i can store the data fine but how do i find out the autonumber given to the new data ( which i need before i can store it ).

best regards

 

[WayneRyan]

Stuart,

    With rs
        .Open "Contracting Agency List", cn, adOpenStatic, adLockPessimistic 'open it
        .AddNew
        .Fields("Vendor") = CompanyName
'
' Right here .Fields("YourAutoNumberField") has the proper value
' after the .Update, its value will be wrong.
'
        .Update 'update the table
        .Close 'close the recordset connection
    End With

Wayne