How can I get the last insert value from MySQL database and update a field in Access?

Question

I have a complex database involving human remains, so the Access forms to input the data are also quite complicated. I have several subforms (Skeleton_index, Element_index, and a few others). The two subforms I mentioned by name send data to their respective fields (element_link, skeleton_link) on an unbound master form, from where the data is used by other forms. For Skeleton_index the user has to input the name of the individual and it shows up in the skeleton_index field instantly. The problem I have is that I don't want the user to have to input the primary key data for Element_index as it should auto_increment. This is a problem because the auto incremented value in Element_id doesn't show up in the element_link field instantly. To get it to show, the user has to create a new element and then go back to the one they were editing. I want to avoid this.

What I want to do is update the Element_id textbox to a new auto_increment primary key when it gets focus. The VBA code should fetch the last primary key from the MySQL (InnoDB) Element_index table, add one to it and then update the value in the Element_id field in the Element_index form.

This is my attempt and it just plain fails.

Private Sub Element_id_GotFocus()
    SQL = "SELECT LAST_INSERT_ID();"
    lastID = DoCmd.RunSQL(SQL)
    newID = Int(lastID) + 1
    Element_id.Value = newID
End Sub

EDIT:

The database will have a single user in the first instance, but there may be more in the future.

SOLUTION: I added a button with two macros: one that saves the inserted record and a second one that refreshes the form. No need for complicated VBA.

Answer 1

Your attempt is wrong in many ways. The main ones being:

1. You can't use DoCmd.RunSQL to run select queries. It only runs action queries.
2. You need to use a passthrough query to run queries that contain MySQL-specific functions.
3. You can't use LAST_INSERTED_ID() unless you execute it over the same connection that was used to insert a row into the table you're interested in.

You're better off using a QueryDef to execute a passthrough query, and using the INFORMATION_SCHEMA.TABLES table to retrieve the next autonumber:

'Create a new temporary query, uses `With` instead of storing it in a variable
With CurrentDb.CreateQueryDef("")
    'Make it a pass-through query that connects to MySQL
    .Connect = "ODBC;<enter connection string here>"
    'Set the SQL for the query: queries table definition, gets auto_increment seed
    .SQL = "SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = ""<table name here>"" AND TABLE_SCHEMA = ""<database name here>"""
    'The query should return records (not an action query)
    .ReturnsRecords = True
    'Execute the query, get the results into a snapshot-type recordset
    'And set your field equal to the first column of the first row of the recordset
    Element_id.Value = .OpenRecordset(dbOpenSnapshot).Fields(0).Value
End With

Answer 2

I rely on error, when ID is not valid.

First get the max(ID), then increment and at last execute Insert-Query with the incremented ID. If no error, you have the ID for FK, on error handle it (e.g. retry with next increment)

On Error Goto myErrHandler
Dim newID as Long
newID = Nz(DMax("ID","myTable"),0) + 1
With CurrentDb()
    .Execute "INSERT INTO myTable (ID) Values (" & newID & ")", dbFailOnError
End With
'...
' here code for inserts in tables with ID as FK