I have a form with multiple tabs that allows the user to insert data into a customer table. The ID is an Identity column.
After a row is inserted into the database I would like to retrieve the ID number for that row that was just inserted into the database. Can you tell me the best way to do this?
When the user clicks on a Relatives tab the application inserts the customer to the database. If I can get the the newly inserted customer ID then I can use it for the CustomerID column of the Relatives table when inserting a Relative so the 2 tables are properly related.
At present the program uses this code to do just that but this code will only be good for a single user system. It's possible that we may sell it as a multi-user system and I think the current way we are doing this will fail because there is not telling whose ID will be returned in that scenario.
Here's the query that we are using:
SqlStatement = "SELECT Max(ID) " & _ "FROM Customers"