Reporting an incremented "ID" with a sorting requirement

On Thu, 31 Jul 2008 06:40:01 -0700, Klatuu
wrote:

That is not an unreasonable technique. I have seen it used also; however, it
all could be done with a DLookup as I do it to get the current high number
and an SQL Update query to save the new number back to the table.

Any time you can avoid using a recordset, you should. Not that recordsets
are bad, it is just a performance issue.

Also, when using Action Queries, the Currentdb.Execute method is the fastest
way to do it. It bypasses the Access UI and doesn't trigger warning
messages. But, because it doesn't go through Access, but directly to Jet,
you need to use th dbFailOnError option to be sure you get an error returned
if it fails.

But, there is one problem with your code. You are assuming the record will
be found. It will not always be, so you have to plan for that. It will not
be found for the first occurance of the maing designator and document type
combination.
--
Dave Hargis, Microsoft Access MVP

I saw the technique recommended for a multi-user enviornment, as a way to incure
every user would get a unique number. It is true that the first number needs to
be inserted somehow. Typically DMax is the method to use. No argument there.

You are correct. It will ensure each user gets a unique number. The only
problem is that a gap in numbering could happen if a user starts a new
record, then does not save the record, the number is discarded. But, that
may not be an issue.

--
Dave Hargis, Microsoft Access MVP


"Michael Gramelspacher" wrote:

On Thu, 31 Jul 2008 06:40:01 -0700, Klatuu
wrote:

That is not an unreasonable technique. I have seen it used also; however, it
all could be done with a DLookup as I do it to get the current high number
and an SQL Update query to save the new number back to the table.

Any time you can avoid using a recordset, you should. Not that recordsets
are bad, it is just a performance issue.

Also, when using Action Queries, the Currentdb.Execute method is the fastest
way to do it. It bypasses the Access UI and doesn't trigger warning
messages. But, because it doesn't go through Access, but directly to Jet,
you need to use th dbFailOnError option to be sure you get an error returned
if it fails.

But, there is one problem with your code. You are assuming the record will
be found. It will not always be, so you have to plan for that. It will not
be found for the first occurance of the maing designator and document type
combination.
--
Dave Hargis, Microsoft Access MVP

I saw the technique recommended for a multi-user enviornment, as a way to incure
every user would get a unique number. It is true that the first number needs to
be inserted somehow. Typically DMax is the method to use. No argument there.