In Access you can get the new number by the following:

rstRecordset.open "SELECT * FROM MyTable WHERE 0 = 1;", cnConnection, adLockOpptomistic

With rstRecordset
   .AddNew 
   !Field1 = txtFirst.Text
   !Field2 = txtSecond.Text
   .Update
   NewAutoNumber = !tblID
End With

This works fine for Access but does not work with SQL so be wary! The WHERE 0 = 1 is there so that you open the conenction but don't bother filling a recorsdet as you are only adding a record.