Auto ID Needed

[Guest]

I have a database I need to create a sytem generated unique id for each
record. This ID can not be an AutoNumber because I will be using it to link
data in other tables. I would like the ID to auto populate when a user
creates a new record on the form (the form has subforms for the other two
related tables). The user first has to enter a ssn (or leave it blank if none
is available) and then tab to the Record ID. The ID should be 8 characters
long with no punctuation marks.

I have tried to use some of the VB code in other posts but was unable to
make it work. I am not a coder but usually can adapt the code if I have a
clear understanding what it is doing.

Any help ould be greatly appreciated.

 

[Rick Brandt]

I have a database I need to create a sytem generated unique id for
each record. This ID can not be an AutoNumber because I will be
using it to link data in other tables. I would like the ID to auto
populate when a user creates a new record on the form (the form has
subforms for the other two related tables). The user first has to
enter a ssn (or leave it blank if none is available) and then tab to
the Record ID. The ID should be 8 characters long with no
punctuation marks.

I have tried to use some of the VB code in other posts but was unable
to make it work. I am not a coder but usually can adapt the code if
I have a clear understanding what it is doing.

Any help ould be greatly appreciated.

I don't understand. AutoNumbers work great for relating to other tables.
It is in fact their primary purpose. However if you don't want to use
AutoNumbers for some other reason then a popular substitute is to use a
DMax()+1 code routine to find the highest existing number and add one to it,
applying the result into the new record. BeforeUpdate of the form being the
safest event to use for this if you need to handle simultaneous users.

 

[Guest]

I didn't mention that the field is a character field and probably, if
possible, should remain as such. Somehow I don't think that will be possible
though

Also the child tables may have many records for that one ID.

Once I get an understanding of what DMax is I will try. Thank You for your
help.

 

[punjab_tom]

autonumbers won't work because you use it to link to data in other
tables?
I don't understand that sentence at all

you should just keep your data in SQL Server
and just use simple identity fields for everything you do

-Tom

 

[Guest]

I did try making the an AutoNumber since there are records already in the
database I was unable to do so. The database does need to remain in Access.
Thanks for the posts and help so far

 

[Joseph Meehan]

I did try making the an AutoNumber since there are records already in
the database I was unable to do so. The database does need to remain
in Access. Thanks for the posts and help so far

:

I believe that if you copy the structure of the table, then change the
ID field to autonumber type then append the existing records to the new
table, you will get what you want.

 

[punjab_tom]

if you do that in SQL; it will 'inherit' the identity settings.. unless
one of 10 things; dont have the list here it's in a book I have 'SQL
Server 6.5 Secrets'

-TOm