Ask Your Question

Revision history [back]

click to hide/show revision 1
initial version

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.

The best way to do this is using a macro. In the macro, execute an SQL statement retrieving the record with the highest auto increment field and sequenced by it. Go to the Last record in the record set, get the value and add one to it. Now you should have the information wanted. Statement would be:

SELECT MAX("YOUR_FILED_NAME") FROM "YOUR_TABLE_NAME"

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.

The best way to do this is using a macro. In the macro, execute an SQL statement retrieving the record with the highest auto increment field and sequenced by it. Go to the Last record in the record set, get the value and add one to it. Now you should have the information wanted. Statement would be:

SELECT MAX("YOUR_FILED_NAME") FROM "YOUR_TABLE_NAME"

It is also possible to just display this on the form from a query but you would need to refresh the form each time. Just options.

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.

The best way to do this is using a macro. In the macro, execute an SQL statement retrieving the record with the highest auto increment field and sequenced by it. Go to the Last record in the record set, get the value and add one to it. Now you should have the information wanted. Statement would be:

SELECT MAX("YOUR_FILED_NAME") MAX("NewID") FROM "YOUR_TABLE_NAME"
"Items"

It is also possible to just display this on the form from a query but you would need to refresh the form each time. Just options.

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.

The best way to do this is using a macro. In the macro, execute an SQL statement retrieving the record with the highest auto increment field and sequenced by it. Go to the Last record in the record set, get the value and add one to it. Now you should have the information wanted. Statement would be:

SELECT MAX("NewID") MAX("ID") FROM "Items"

It I believe this is also possible to just display this on the form from a query but you would need to refresh the form each time. Just options.more of what is needed.

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.

Sorry for multiple posts. Kept seeing better ways.

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.

Sorry 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 multiple posts. Kept seeing better ways.

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.

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.