Order of bounded fields

[toom]

I just started (again) with vba, well I used it quite a lot in 2006 but that's some time ago and I can not remember everything. Anyway I created a table called auto and it contains the following fields

id PRIMARY
brand
age
power

I also created a form called autoform. Within that form I created three textfields added the tabel auto to the form as Record Source and as Control Source I added a table field for each textfield in my form. Now, I already filled my table with some values:


id brand age power
1 Mecedes 13 129
2 Audi 3
3 Porsche 2
4 BMW 2
*

If I now open the form my textfield are populated with the values of the row with id, ie. Mercedes 13 and 129. If I delete all these textfields and enter some new entries I would expect that I added a whole new line like Toyota,4,134 with new id 5. But that does not happen instead the row with id is altered and the old values get substituted with toyoty,4,134.

Why does this happen? How do I tell my form that i should add a new dataset to my table?

[dicepackage]

If you have the primary key of ID set to autonumber it will go to 5. With autonumber you can delete 4 but still keep it reserved so that it is no longer available elsewhere. Then when you create a new record it will start with an ID of 5.

[toom]

Thanks for the answer. Since I didn't think it would matter I didn't mention that auto is not a table in access but a MySQL table that I imported into access.

Like this

Dim connection As New ADODB.connection
Dim dbs As New ADODB.Recordset

connection.Open "MySQL Lokal", "root", ""
dbs.Open "SELECT DISTINCT TABLE_NAME AS Tabelle FROM information_schema.COLUMNS WHERE TABLE_SCHEMA='testdb' ORDER BY TABLE_NAME ASC", connection
Do While Not dbs.EOF
DoCmd.TransferDatabase acLink, "ODBC Database", "ODBC;DSN=MySQL Lokal;UID=root;PWD=;LANGUAGE=german;" & "DATABASE=testdb", acTable, dbs("Tabelle"), dbs("Tabelle")
dbs.MoveNext
Loop
dbs.Close
connection.Close



Therefore I think I cannot set this column to autonumber. The id column is auto_increment in MySQL but it seems as if this doesn't have any effect.

Is this problem somehow else solvable?

[orange]

The form you are using is BOUND to the table since you said the Auto table is the record source of the form. With the BOUND form, when you change the data values in the textboxes on the Form, those values are changed in the Table. That's part of what Bound Forms are all about.

What do you want to occur??

Here's a link to Bound form description and usage.
http://www.dhdurso.org/articles/ms-access-forms.html