Artist ID Number + 1

[EComCon1]

Hi or, should I say, HELP! (PLEAESE!)

I have gone round and, round with this problem for weeks.

I have a form in Access called, "Info ID Form" and, in this form I have a
field called, "Artist ID Number" to register musical artist's. I have been
using this form for quite sometime now. I have already reached Artist ID
Number: P000006510 already.

I finally have the time to make the field automated now, example: P000006511,
P000006512 and, so on everytime you go to a NEW (BLANK) "Info ID Form".

I have tried to use Microsoft Discussion Groups but, either they leave out
simple information or, I am doing something wrong on my end.

Can anymore please help me?

Many THANKX,
jeff

 

[Stefan Hoffmann]

hi,

I finally have the time to make the field automated now, example: P000006511,
P000006512 and, so on everytime you go to a NEW (BLANK) "Info ID Form".

In such cases I use the forms Before Insert event to set this value:

Private Sub Form_BeforeInsert(Cancel As Integer)

Dim NewID As String

NewID = Mid(Nz(DMax("ArtistID", "yourTablename"), "P0"), 2, 1) + 1
Me![ArtistID] = NewID

End Sub

btw, I would not store the "P" prefix in the table and use a number
field instead of a text field, as the prefix could easily be applied by
formatting the controls.

Using an numeric ID also simplyfies the code:

Me![ArtistID] = Nz(DMax("ArtistID", "yourTablename"), 0) + 1


mfG
--> stefan <--

 

[EComCon1 via AccessMonster.com]

"Thank You" so very much Mr. Hoffmann! I have some time tonight to add in
your great information. And I will do as you said about the "P".

Thank Care,
jeff

hi,

I finally have the time to make the field automated now, example: P000006511,
P000006512 and, so on everytime you go to a NEW (BLANK) "Info ID Form".

In such cases I use the forms Before Insert event to set this value:

Private Sub Form_BeforeInsert(Cancel As Integer)

Dim NewID As String

NewID = Mid(Nz(DMax("ArtistID", "yourTablename"), "P0"), 2, 1) + 1
Me![ArtistID] = NewID

End Sub

btw, I would not store the "P" prefix in the table and use a number
field instead of a text field, as the prefix could easily be applied by
formatting the controls.

Using an numeric ID also simplyfies the code:

Me![ArtistID] = Nz(DMax("ArtistID", "yourTablename"), 0) + 1

mfG
--> stefan <--