Create new record in VBA

[thelonelyghost]

First off, I already know of one answer to my question, but it's horribly... clunky, to put it lightly. What I'm looking for is a better way to go about this. Now my obligatory header

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:

1. ModificationID - Autonumber - (Primary Key)
2. CTSLogNumber - Number(Long Integer)
3. AuthorName - Text(50)
4. FieldName - Text(255)
5. dtModDate - Date/Time(Short Date) - Default Value: =Format(Now()," MM/DD/YYYY")
6. 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?

[Steven Kogan]

This works... Instead of the debug.print you would put your code to add the log file record... Instead of 'AutoKey' you would put 'ModificationID ', etc.

        Dim rst As DAO.Recordset
        Dim strSQL As String
        strSQL = "SELECT * " & _
        "FROM [table] " & _
        "WHERE 1=2"

        Set rst = CurrentDb.OpenRecordset(strSQL)
        rst.AddNew
        
        rst("Description") = "My new record"
        rst("Codetext") = "test data"
        
        rst.Update
        rst.MovePrevious
        
        Debug.Print rst!AutoKey
        
        rst.Close
        Set rst = Nothing

[thelonelyghost]

(1)Thank you very much for your response, Steven Kogan. I had forgotten DAO has the function to create a new record.

(2)My apologies, I guess I was being unclear. I want to import Table A's autokey into the CTSLogNumber field of Table B. What is shown above is Table B. Changing around what table generates the key is not an option, either.

Another thing I realize upon rereading my OP is that this is a multi-user interface so something static like "I am a new record" for every user is out of the question. It could cause a major mishap with an UPDATE query if two people happen to use this form at the same time. In addition, I'll be porting this over to SQL Server with VB forms (and such) so I don't think the idea to create a new record and move back one will work. :(

So to reiterate, does anyone have a better solution than a salted hash like I was originally thinking? In other words, when creating a new record with DOA or ADO, is there a way to set the newly generated autonumber to a variable upon creation of the record?

EDIT: Woot! Broke the 100-post mark!

[Steven Kogan]

I'm interested in seeing another solution as well. This one should work okay though.

I've modified the code so it is closer to what you are describing. Note that SQL Server has functions to do what you'd like, so if your data is stored in SQL Server you may want to use a stored procedure instead.

The code provided is a sample, of course. It should work in a multi-user situation, including when a different user adds a record while this code is running.

The recordset created will only see the newly added record: records added by other users will not be seen.

Note also that by using 1=2 in the where clause, you will not accidentally go to any existing record. It begins as an empty recordset.

Dim rst As DAO.Recordset 
        Dim strSQL As String
        Dim lngCTSKey as long
        strSQL = "SELECT * " & _ 
        "FROM [Table A] " & _ 
        "WHERE 1=2" 
  
        Set rst = CurrentDb.OpenRecordset(strSQL) 
        rst.AddNew 
  
        rst("AuthorName") = me.AuthorName 
        rst("FieldName") = me.FieldName
          
        rst.Update 
        rst.MovePrevious 
  
        lngCTSKey = rst!CTSKey 
  
        rst.Close 
        Set rst = Nothing 

        ' Insert code to add new record to Table B here, using lngCTSKey which is the autonumber of Table A

[thelonelyghost]

Ah I see now! Maybe I was just confused before as to what the code accomplished, but the most recent explanation dispelled any of that. Thanks a lot, Steven Kogan! I'll choose yours as the best answer as soon as I test it.