Forecast autofield value in Base form

[MicheleSalvador]

For my database I created a form with “Add data only” property to insert new records in a table.

Every record has a unique “ID”.

The ID field generally displays <AutoField> until the record is saved, after that the ID number is generated and displayed in his form field.

I’d like to display this new ID number just when the form is opened, before the record is saved.

So I created a Query SELECT COUNT( * ) + 1 "NewID" FROM "Items" (I know this is not really the AutoField value, but could be enough for my project).

I added a subform with the Query as Content.

And into the subform added a new ID field with “NewID” assigned as Data field.

This partially works: when the form is opened the ID field is 1, and it’s updated to the total number of records after the record is saved…

Is it possible to “refresh” the ID field just after the form is opened? So that it immediately shows the total number of records + 1.

Should I use a macro?

I found this topic asking the same but not really solving the problem.

Thank you!

[Ratslinger]

Hello,

A macro will do what you want but it would be easier to just put a button on the form and set Property Action to Refresh form.

That being said, your process has a hole in it. The number of records (count) has no bearing on what the next auto increment number will be. Lets say you have entered 25 records and the next increment # is 26. Now you delete six of these records which leaves you with 19 good records (count) but the next increment # is still 26.

Statement would be:

SELECT MAX("ID") + 1 FROM "Items"

I believe this is more of what is needed.

Edit:

After further thought, even the above statement can present problems. First, you don’t specify which database you are using. I’ll guess it is the default which is HSQLDB embedded v1.8.0 which comes with Base.

Next, my statement above is based on the last record being the last record entered. In other words, in the above example it would be # 25. If, however, the last six records were deleted, the last auto incremented number posted would have been #19 (same result for my statement & using count) but the actual next # would be 26.

There are ways in other DB’s to access this value but I haven’t found it in v1.8.0 as it is pretty old and limited in what was available. The actual indication that the field was auto incremented was extremely difficult until v2.x was released.

If I do run across an answer I will post, but until you can get that value the display will only be a maybe.

Edit:

In preparing a sample I changed the approach. A button is no longer needed. The ID text Box is on a sub form & tied to query. Correct value displays on form open & every record.

Sample: DisplayAutoID.odb

Simple one form one table odb.

[MicheleSalvador]

Sorry but did you try what you suggest? Unfortunately when I’m entering a new record the Refresh button seems to refresh the form but has no effect on displaying the query result into ID field Instead when the form loads an existing record the ID field displays correctly the result of COUNT or MAX without need to refresh the form.

Auto-increment and last ID misaligned by deleted records is not a problem for my project.

Yes I’m using embedded HSQLDB version 1.8.0.

I await new tips!

[MicheleSalvador]

If entering a new record into the form, the ID field doesn’t display the query result:

Queries > Edit in SQL View… > Edit menu > Run SQL command directly must be checked on.

[Ratslinger]

Yes, that is in the sample provided.