Software: Access 2000
OS: Windows XP Professional SP3
Problem: Copying over the primary key (Autonumber) from Table A to Table B when creating a new record in Table A.
Sorry if the synopsis is confusing, allow me to explain. I have a database of records with data saying this and that. I have also set up a table to log which fields in each record change containing this information:
- ModificationID - Autonumber - (Primary Key)
- CTSLogNumber - Number(Long Integer)
- AuthorName - Text(50)
- FieldName - Text(255)
- dtModDate - Date/Time(Short Date) - Default Value: =Format(Now(),"MM/DD/YYYY")
- tmModDate - Text(8) - Input Mask: 00:00:00;0;# - Default Value: =Format(Now(),"HH:MM:SS")
What I'm looking for is a way to get the primary key from the table I'm logging, after running an INSERT INTO command in SQL. FYI most, if not all, of my record manipulation is done through SQL.
Clunky Solution: In the Data Entry form, VB code creates a random string of characters individual to the time, date, and author (such as the server time and date salted with the author's name???). Take that value and set it equal to one of the available fields in the table I'm monitoring and have the autonumber generate a new primary key. Then, as soon as that's done, I read what the value of the key is where the salted hash is located and copy that over to the appropriate field in my logging table. I'm then free to update the monitored table with the data I originally wanted to enter and the rest of the record in the log.
That's the best I can come up with for getting the automatically generated primary key from another table on record creation. Is there a better/easier way to do this?