[Solved] Auto increment not working on form

[bartjeman]

I have a HSQLDB table with primary key "ID" auto increment.

I have a sub-form based on this table. When creating a new record in the sub-form, I would expect the ID field to be automatically be populated with the next primary key increment. But no joy. The form control for the ID field is also set to increment.

Screen shots attached. Thanks.

[Villeroy]

I think your auto-increment field does not work at all, not on the form nor by any other means. [DRAFT[ Auto generated fields - Using AutoField might be helpful.

Sometimes turning an existing PK into an auto-incrementing one seems to be impossible. In this case I use to copy the stucture of the old table to a new blank table, edit the structure and finally copy over the old data into the new structure and reorganize any relations. Keep the old table until you are sure that everything works as expected, particularly any relations to other tables.

[bartjeman]

You are correct. In the table settings, "ID" AutoValue = no

When I create the table by pasting from spreadsheet, there is an option to create primary key, but *no* check box for AutoValue.
however
When I create a table using the wizard, there is an option to create a primary key as well as a check box for AutoValue. But I think there is a bug, because even though I checked the AutoValue box during creation, the ID AutoValue is set to No.

So I try to edit the table: the field settings are all grey (inaccessible). The only thing I can edit is the Index. IOW I can not change the ID AutoValue to Yes. Is there another way to do this? SQL?

Shouldn't AutoValue=Yes be the default?

[bartjeman]

I tried Create Table in Design View - this will create the ID field with AutoValue=Yes
Great
But I am at a loss how to bring in my spreadsheet data.

[Villeroy]

1) Copy the spreadsheet range including any header row or copy the respective table icon of a connected Base document.
2) Select the icon of the target table and paste. Do not open any of the database tables.
3) A wizard pops up. Make sure that the table name is the one of your target table and choose the option to append data from the copied table.
4) The next step lets you map the fields of the source table to the fields of the target table. You can skip fields such as auto-IDs in the target table or obsolete fields in the source table. You can adjust the order of fields so until each source field appears side by side with its respective target field. Move unwanted fields down to the end of the field list.
5) The import fails when the source table has inconsistent data (missing values, wrong data types). In case of failure you will be prompted to break or continue. The latter skips all records that are invalid for the same reason.

[bartjeman]

Excellent!
My data is consistent, the paste went perfectly.
Mission accomplished. You can all go home