I have a main table with an autonumber key. I need to introduce a new table, related to the main table in a one-to-many relationship (i.e. a record in the main table could conceivably link to several records in the new table). If the new table links to the main table via that autonumber key, then I (or the user) has to supply the corresponding number for each new record in the new table. Is there a way to automate any aspect of this, so the user doesn't have to keep cross-checking this number when he makes a new entry?
Most brands provide methods to retrieve the key an insert created in an identity or auto_increment column.
Unfortunately there is no general method for this, it will depend on the brand.
For example in MySQL it's called LAST_INSERT_ID(), in Transact SQL (SQLServer) @@identity (better: scope_identity()).