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. 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.

Options: ReplyQuote


Subject
Views
Written By
Posted
Autoincrement problems when adding new records
2708
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.