AutoNumber question - MySQL for Access Forms

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

Guest

Hi all,
I am using MySQL for my tables and have a question in regards to the
difference between the AutoNumber (AN) option for Access tables and the
AutoIncrement (AI) option for MySQL. As far as I can tell, the main
difference is that AN will display right away in the field for it, where AI
will not. Unfortunately, this is causing issues with my forms as many events
call upon this field. Does anyone know how to get the AutoIncrement field to
populate so I can use it in future events? Any help would be greatly
appreciated! Thanks!
-gary
 
You might want to give more details.

I would have to say that for the most part, that most events should not
need, nor care.

For example, if you are editing a form, and it needs to launch another form,
and this form needs to refer BACK to the previous form, then you should
force a disk write on the first form. In fact, for years and years, I always
done this (in case the machine locks up, the all open forms below the
current one are saefaly written to disk. Furhter, foring a disk write also
allows you to open another form to the SAME reocord (and prevents the "this
reocrd been edited by anoheer user). Hence, I keep these coding practives
intact when I move to a sql server based ysstem, and this severs me well.


Another exmaple is reocordset code, and agian you have to change code a bit:

Dim rstRecords As DAO.Recordset
Dim lngNext As Long

Set rstRecords = CurrentDb.OpenRecordset("tblmain")
rstRecords.AddNew

'with a jet based mdb back end, you can at this point
' grab the id, eg: lngNext = rstRecords!ID

' however, for sql server, you need to wait untill the upate,
' so, my code would lauctlaly go:
..... do more stuff here....


rstRecords.Update
rstRecords.Bookmark = rstRecords.LastModified
lngNext = rstRecords!ID
rstRecords.Close
Set rstRecords = Nothing
MsgBox lngNext

Note how the above recordset code now has the lngNext varbles set AFTER the
update occurs. I use the lastmodifed bookmark feature to move the record
point back to the record..and grab the id.

So, between forcing a disk write in my forms (when I launch another form),
and the above, a switch over to server based systems don't pose much of a
problem.

In many other cases, using the after update event, or perhaps adding a
record *before* you launch the form also works well. If you look at this
example search screen, note in the forms how there is a add button, and this
simply adds a new record in code, and then lunches the form to that new
record (which also solves the id problem).

http://www.members.shaw.ca/AlbertKallal/Search/index.html

I also don't allow navigation from record to record on most forms, and again
it makes NO sense to load up a form with a whole bunch of records anyway (it
is slow).
 
Back
Top