Autoincrement problem when adding new records (1 Viewer)

[Steve R.]

I was able to successfully transfer my magazine MS Access back-end to MYSQL. Basically everything worked well, except that I could not add new records in a sub-form. The sub-form is based on a query that uses two tables, each of which must be auto-incremented.

After making the final entry before going to the next new record, MYSQL reported the records of one of the tables as being "deleted". They weren't, the linking "value" was not copied, so the query failed.

Anyway, in searching the MYSQL forum, I found a post by Alan Parker that presented a solution to a similar failure to auto-increment.

He posted the following:

I have assumed that the autonumber field is called Alan , just adjust this as neccessary for each form

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

Note the form recordsource has to be a table or query NOT a Select statement. If it is a select statement save the select statement as a query .. dead simple..

I modified that approach to:

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

So far it is working. Any additional thoughts on improving this solution?

Update: Still working. Ran across this helpful post on the need to have a time-stamp field: Access and MYSQL continuous form problem. Tested out, with the time-stamp field and it failed. Removed the time-stamp field and the Access front-end worked again.

 

[PhoenixofMT]

I found this post after realizing this might be a problem with a database I've just started developing. It might not be the exact same problem, but it is related.

I decided to try out a MySQL back-end (kinda one of those "I've got a free hour..." situations ) with this database. I manually replicated the tables (such as I had) and got my Access front-end connected to it. Then I copied over the small amount of test data I had and started working on the forms, etc.

It turns out that, in Access 2010 at least, this is not as big of a problem in forms. I found that if you try to enter data directly into the tables, it won't let you leave the key field blank. BUT, if you make a simple datasheet form, lock the key field, and set the Tab Stop to No, it just works! It doesn't show the highlighted "Auto Number" or retrieve the number as you begin editing the record, but it displays it for you when the record is saved. In datasheet subforms you can hide this field and not have to worry about it.

Now I guess I'll have to add a form for each table I want to edit directly, but this is quick work with the form wizard and I don't even need to change the auto-name (tblFoo).


P.s. I suppose that if you wanted SOMETHING to appear in the key field when you start editing you could work with the OnDirty event to retrieve the current "Max + 1" and then check it in BeforeUpdate to be sure its still valid. But you're not supposed to let the users see the primary key anyway (it will be hidden in my normal data entry forms), so I suppose this is moot.