Sequential numbering

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have this table called Resin_Type which has a long integer number in the first field and a name in the second field. The current last number in the first field is 3, this field is a primary key field. What I’d like to do is via a query or other method advance this number to 4, then 5, then 6 etc, etc, etc as other Resin Type names are entered. I know. I know, I can use an auto-number in this field. Due to the fact that this application will be moving to SQL Server in the near future, I need to proceed with things as they are currently.

Is there a method that can be used to accomplish this? If there is, how?????

Thanks for your idea/solution contributions over the years since this forum has been around. KEEP UP THE GREAT WORK!
 
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Easy: You can use the DMax() domain function. E.g.:

INSERT INTO Table_Name (Sequence, ... )
SELECT Nz(DMax("Sequence", "Table_Name"), 0) + 1 As Sequence, ....
FROM ...

Problems w/ easy solution:
If more than one user is saving records at the same time they may save a
duplicate Sequence number.

There is a work-around in VBA provided by the Access # Developer's
Handbook by K.Getz, et.al. In MS SQL you could probably use a trigger
to do the same thing.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQQ/PrYechKqOuFEgEQIF2gCfSzDGFK7EATr9cyc0lax/0b+3hPsAnR7I
hTjvxyEcwVBoBiuoBacZ5wCv
=pBX5
-----END PGP SIGNATURE-----
 
Thanks, Mr Foster:

It worked!

MGFoster said:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Easy: You can use the DMax() domain function. E.g.:

INSERT INTO Table_Name (Sequence, ... )
SELECT Nz(DMax("Sequence", "Table_Name"), 0) + 1 As Sequence, ....
FROM ...

Problems w/ easy solution:
If more than one user is saving records at the same time they may save a
duplicate Sequence number.

There is a work-around in VBA provided by the Access # Developer's
Handbook by K.Getz, et.al. In MS SQL you could probably use a trigger
to do the same thing.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQQ/PrYechKqOuFEgEQIF2gCfSzDGFK7EATr9cyc0lax/0b+3hPsAnR7I
hTjvxyEcwVBoBiuoBacZ5wCv
=pBX5
-----END PGP SIGNATURE-----
Click to expand...
 
Back
Top