How to obtain the auto update value after running INSERT INTO in Base Macro

image description

I use sql in basic to update a new member record, because a fee is obtained a transaction record also needs to be created. This transaction record requires a couple of fields from the member record, one being the auto updated member number. How do I get this number. I use ------stmt.executeUpdate(query)--------- to update the Statement Object.
Let me be clear this is working great and I have found a couple of methods to find it by examining the MAX before and adding 1 on or finding a match to some of the fields in a new result query. Both seem messy and will not hold up in a multi-user environment.
So my question is :- Is there a clean method of obtaining the Auto Update number inserted by ----stmt.executeUpdate(query)----

P.S. Query is string with the Sql statement.
Thankyou for any help.
Please don’t tell me to use a linked form and sub-form, I tried for a week to get it working the way I wanted. No Luck. Anyway I’m a control freek.


This really depends upon the database you are using. You have not noted this.

No answers so I went for the work around.