MySQL Forums
Forum List  »  Microsoft Access

Autoincrement problems when adding new records
Posted by: Stephen Rynas
Date: May 19, 2012 09:01PM

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.,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.
DAO Alternative Approach

Private Sub Form_AfterUpdate()
Set NewLinkRs = CurrentDb.OpenRecordset("SELECT * FROM tblLinkedList", dbOpenDynaset, dbSeeChanges)
NewLinkRs!LinkStoryNUM = StoryIDNUM01
End Sub

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

Options: ReplyQuote

Written By
Autoincrement problems when adding new records
May 19, 2012 09:01PM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.