Get current record ID in MYSQL backend

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Now that we've migrated the backend of the database to MySQL, I can't get the
current record ID after I do a AddNew code. I've tried the following two
methods but to no avail. I want to obtain the current record ID so that I
can add some other new data to 2 other tables related by the current record
ID. I've spent so much time on this!

Method 1:
Dim Rec As DAO.Recordset, curID As Long
Set Rec = db.OpenRecordset("CUSTOMER ACCESS", dbOpenDynaset)
With Rec
.AddNew
![UserName] = Me!txtUsername
.Update
Me![txtcurID] = curID
.Close
End With

Method 2:
Dim Rec As DAO.Recordset, curID As Long
Set Rec = db.OpenRecordset("CUSTOMER ACCESS", dbOpenDynaset)
With Rec
.AddNew
![UserName] = Me!txtUsername
.Update
strcriteria = "[USERNAME] = '" & Me!txtUsername & "'"
.FindFirst strcriteria
Me![txtcurID] = curID
.Close
End With

Has anyone tried this? Is there a better method?
 
I normally use the first method but in your code, you declared variable
"curID" and then you used it in the statement:

Me![txtcurID] = curID

before assigning any value to it. Thus [txtcurID] will always have the
default value of curID which is zero!

My guess is you want something like:

Me![txtcurID] = Rec.Fields("IDFieldName").Value

which will retrieve the ID of the added Record.
 
Van,
the problem I'm experiencing once I migrate the backend to MySQL is that
during the update process, I can not obtain the auto-increment id field. This
was working when the backend was still MsAccess. Now, it always give me a
currentID=1 even though we've had tens of records already. I must be missing
something? thanks for your quick response.

Van T. Dinh said:
I normally use the first method but in your code, you declared variable
"curID" and then you used it in the statement:

Me![txtcurID] = curID

before assigning any value to it. Thus [txtcurID] will always have the
default value of curID which is zero!

My guess is you want something like:

Me![txtcurID] = Rec.Fields("IDFieldName").Value

which will retrieve the ID of the added Record.

--
HTH
Van T. Dinh
MVP (Access)




Samantha said:
Now that we've migrated the backend of the database to MySQL, I can't get
the
current record ID after I do a AddNew code. I've tried the following two
methods but to no avail. I want to obtain the current record ID so that I
can add some other new data to 2 other tables related by the current
record
ID. I've spent so much time on this!

Method 1:
Dim Rec As DAO.Recordset, curID As Long
Set Rec = db.OpenRecordset("CUSTOMER ACCESS", dbOpenDynaset)
With Rec
.AddNew
![UserName] = Me!txtUsername
.Update
Me![txtcurID] = curID
.Close
End With

Method 2:
Dim Rec As DAO.Recordset, curID As Long
Set Rec = db.OpenRecordset("CUSTOMER ACCESS", dbOpenDynaset)
With Rec
.AddNew
![UserName] = Me!txtUsername
.Update
strcriteria = "[USERNAME] = '" & Me!txtUsername & "'"
.FindFirst strcriteria
Me![txtcurID] = curID
.Close
End With

Has anyone tried this? Is there a better method?
Click to expand...
Click to expand...
 
I use Ms-SQL Server 2000 Back-End and similar code works fine.

It must be some differences in the way MySQL Server / MySQL ODBC driver
handles Record addition. It seems like the CurrentRow remains on the first
Record after addition and the added Record doesn't become the CurrentRow.

Try opening the Recordset, MoveNext then use the Debug.Print to Record the
ID. Then created a new Record and after .Update, check the ID again. If
this is the same as previously recorded in the Immediate window, then it
sounds like MySQL does not change the CurrentRow and the ID returned is from
the CurrentRow, not from the added Row.

A possible work-around: You may want to try MoveLast after update (so that
the added Row becomes CurrentRow) before retrieving the value of the ID.


--
HTH
Van T. Dinh
MVP (Access)


Samantha said:
Van,
the problem I'm experiencing once I migrate the backend to MySQL is that
during the update process, I can not obtain the auto-increment id field.
This
was working when the backend was still MsAccess. Now, it always give me a
currentID=1 even though we've had tens of records already. I must be
missing
something? thanks for your quick response.

Van T. Dinh said:
I normally use the first method but in your code, you declared variable
"curID" and then you used it in the statement:

Me![txtcurID] = curID

before assigning any value to it. Thus [txtcurID] will always have the
default value of curID which is zero!

My guess is you want something like:

Me![txtcurID] = Rec.Fields("IDFieldName").Value

which will retrieve the ID of the added Record.

--
HTH
Van T. Dinh
MVP (Access)




Samantha said:
Now that we've migrated the backend of the database to MySQL, I can't
get
the
current record ID after I do a AddNew code. I've tried the following
two
methods but to no avail. I want to obtain the current record ID so
that I
can add some other new data to 2 other tables related by the current
record
ID. I've spent so much time on this!

Method 1:
Dim Rec As DAO.Recordset, curID As Long
Set Rec = db.OpenRecordset("CUSTOMER ACCESS", dbOpenDynaset)
With Rec
.AddNew
![UserName] = Me!txtUsername
.Update
Me![txtcurID] = curID
.Close
End With

Method 2:
Dim Rec As DAO.Recordset, curID As Long
Set Rec = db.OpenRecordset("CUSTOMER ACCESS", dbOpenDynaset)
With Rec
.AddNew
![UserName] = Me!txtUsername
.Update
strcriteria = "[USERNAME] = '" & Me!txtUsername & "'"
.FindFirst strcriteria
Me![txtcurID] = curID
.Close
End With

Has anyone tried this? Is there a better method?
Click to expand...
Click to expand...
Click to expand...
 
Samantha said:
Now that we've migrated the backend of the database to MySQL, I can't get the
current record ID after I do a AddNew code. I've tried the following two
methods but to no avail. I want to obtain the current record ID so that I
can add some other new data to 2 other tables related by the current record
ID. I've spent so much time on this!

Method 1:
Dim Rec As DAO.Recordset, curID As Long
Set Rec = db.OpenRecordset("CUSTOMER ACCESS", dbOpenDynaset)
With Rec
.AddNew
![UserName] = Me!txtUsername
.Update
Me![txtcurID] = curID
.Close
End With

Method 2:
Dim Rec As DAO.Recordset, curID As Long
Set Rec = db.OpenRecordset("CUSTOMER ACCESS", dbOpenDynaset)
With Rec
.AddNew
![UserName] = Me!txtUsername
.Update
strcriteria = "[USERNAME] = '" & Me!txtUsername & "'"
.FindFirst strcriteria
Me![txtcurID] = curID
.Close
End With

Has anyone tried this? Is there a better method?
Click to expand...

You can get the last autoincrement used by MySQL by calling "SELECT
AutoIncField FROM Table WHERE AutoIncField IS NULL."

I recommend you wrap this in a function, something like this:

Public Function CreateNewContact(String Name) As Long
Globals.MySQL.Execute("INSERT INTO Contacts (Name) VALUES (""" & Name &
""")")
Dim NewContact As Recordset
Set NewContact = Globals.MySQL.OpenRecordset("SELECT ContactNum FROM
Contacts WHERE ContactNum IS NULL")
CreateNewContact = NewContact!ContactNum
NewContact.Close
Exit Function

Obviously, you'd need to customize the table and field names, put in some
error checking, add more parameters, etc.

In a quick search, I couldn't find the page that documents this feature.
This is an alternate technique:
http://dev.mysql.com/doc/refman/5.0/en/odbc-and-last-insert-id.html

This page has a few tips on using MSAccess with MySQL:
http://dev.mysql.com/doc/refman/5.0/en/msaccess.html

Let me know if you have any trouble!
 
Back
Top