Autoincrement problems when adding new records

[Stephen Rynas]

I have an MS Access front-end connected to MYSQL as a back-end. There is a sub-form that is based on a query that uses two tables, each needs to be autoincremented. MYSQL only autoincremented one table and reported the other new records in the other table as being "deleted".

I experimented for a while; came up with a solution based on using DAO. But I also looked into this forum and found a post by Alan Parker responding on a similar issue. http://forums.mysql.com/read.php?65,29697,119633#msg-119633

He wrote: "On the Forms before insert event use.........
Alan = DMax("[Alan]", Me.RecordSource) + 1"

I modified this approach:
Private Sub Form_BeforeInsert(Cancel As Integer)
Me.StoryIDNUM = DMax("storyidnum", "tblStoryList") + 1
Me.LinkIDNUM = DMax("linkidnum", "tblLinkedlist") + 1
End Sub

So far it has worked fine in limited testing.
Any other suggestions for improvement???
---------------------------------------------------------
Since posting I ran accross this helpful discussion.
Don't know yet if it will work.
http://bytes.com/topic/access/answers/204066-access-mysql-continous-form-problem#post780401
----------------------------------------------------------
DAO Alternative Approach

Private Sub Form_AfterUpdate()
Set NewLinkRs = CurrentDb.OpenRecordset("SELECT * FROM tblLinkedList", dbOpenDynaset, dbSeeChanges)
NewLinkRs.MoveLast
NewLinkRs.Edit
NewLinkRs!LinkStoryNUM = StoryIDNUM01
NewLinkRs.Update
Me.Parent.Refresh
End Sub



Edited 1 time(s). Last edit at 05/21/2012 07:57PM by Stephen Rynas.