Help with Simple SQL Code Required Please

[nicky1234567]

Hi,

I have a database with 8 tables. One of these being staff

I have staffID as a Primary key in the table staff
The format of this is "STA"0000
and input mask "STA"9999

I would like staffID to be automatically generated by taking the value of the previous staffID (eg STA0001) and creating a new staffID for the next member of staff that is to be added (eg, STA0002).

I have access experience but have never had to use SQL code and am struggling. If anyone could be kind enough to help me with this I would be very grateful.

Thank You

Nicholas

 

[Mile-O]

Lookup using DMax() to create a custom autonumber.

Basically, you just use DMax to get the last number and then add one to it in the BeforeUpdate event of your form.

 

[nicky1234567]

Hi, i looked at the information for DMAX and have created this code which is not correct. Every time I go to enter a new staff member the staffID is coming up as STA0000. Would you please be able to advise me where I have gone wrong.

Thank You

Nicholas


(Copied from Access)

Private Sub staffID_BeforeUpdate(Cancel As Integer)

staffIDSeqNum = Nz(DMax("staffIDSeqNum", "staff", "STA0000 = " & Me.staffID), 0) + 1

End Sub

 

[Mile-O]

staffIDSeqNum = Nz(DMax("staffIDSeqNum", "staff")) + 1

 

[nicky1234567]

I have tried entering that code and am still finding the same problem that when I enter a new member of staff the staffID is coming up as STA0000.

Is there any way that the code that you sent could be incorrect or would it be an error on my part? I have followed all instructions correctly but still to no avail.

Please can someone solve my problem, im literally tearing my hair out trying to work this out.

Thank You

Nicholas

 

[Pauldohert]

Are you updating the staffIDSeqNum number each time in the table this may explain why you are stuck at 0.

Personally I would try to avoid a primary key like STA0009 and just stick with 9.

Paul

 

[Mile-O]

I'd remove the formatting - it's unnecessary in the table.

 

[nicky1234567]

thank you SJ

you have saved me hours of trouble. i really appreciate it