By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
445,841 Members | 1,736 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 445,841 IT Pros & Developers. It's quick & easy.

Default Primary Without Using Autonumber

P: 6
Hello All,
Im customizing a front end for an existing database that exists between various table with relationships already in place.

There are various forms i have created where the user will input data. However, I do not want them to have to enter any ID fields because this is the primary key and I would like it to simply be a number +1 higher than the previous record.

The ID fields have been entered starting at 1001 (for some ridiculous reason), so row 1 would be 1001, row 72 would be 1072, etc.

I have tried to change it to autonumber, but access wont let me, Ive tried making another autonumber ID field with '/1000' format, deleting the first one and renaming the new one, but Im told it would mean there is redundant data, and the reports show errors when run.

Is there a formula I can enter into the default value argument so that the new ID record is always 1 higher than the previous?

Any help would be great

Cheers
Nov 30 '06 #1
Share this Question
Share on Google+
5 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
On the Form Current Event ...

Expand|Select|Wrap|Line Numbers
  1.  
  2. Private Sub Form_Current() 
  3. Dim tempID As Integer
  4.  
  5.    tempID = DMax("ID","TableName")
  6.    tempID = tempID + 1
  7.    If IsNull(Me.ID) Then
  8.       Me.ID = tempID
  9.    End If
  10.  
  11. End Sub
  12.  
Nov 30 '06 #2

P: 6
SWEET!! Thanks Mate!
Nov 30 '06 #3

MMcCarthy
Expert Mod 10K+
P: 14,534
SWEET!! Thanks Mate!
You're welcome.

Mary
Nov 30 '06 #4

P: 6
i have similar problem as yours. i have this primary number that i want to auto generate incrementally.

example..
client 1 ID : 1/1/2000
client 2 ID : 2/1/2000
client 3 ID : 3/1/2000

the incremental first number will be the client number but the second number in the middle will be the today's month and the last part will be the year.

Can this work too on the coding?

please need help..

thanks
Nov 30 '06 #5

MMcCarthy
Expert Mod 10K+
P: 14,534
i have similar problem as yours. i have this primary number that i want to auto generate incrementally.

example..
client 1 ID : 1/1/2000
client 2 ID : 2/1/2000
client 3 ID : 3/1/2000

the incremental first number will be the client number but the second number in the middle will be the today's month and the last part will be the year.

Can this work too on the coding?

please need help..

thanks
Something like ...

Expand|Select|Wrap|Line Numbers
  1.  
  2. Private Sub Form_Current()
  3. Dim db As Database
  4. Dim rs As DAO.Recordset
  5. Dim tempID As Integer
  6.  
  7.    Set db = CurrentDB
  8.    Set rs = SELECT Max( Left([Client ID], InStr([Client ID], "/") - 1)) As MaxID FROM TableName;
  9.  
  10.    If rs.RecordCount=0 Then
  11.       Exit_Sub
  12.    End If
  13.  
  14.    tempID = rs!MaxID
  15.    tempID = tempID + 1
  16.    If IsNull(Me.ID) Then
  17.       Me.[Client ID] = tempID & "/" & Month(Now()) & "/" & Year(Now())
  18.    End If
  19.  
  20. Exit_Sub:
  21.  
  22.    rs.Close
  23.    Set rs = Nothing
  24.    Set db = Nothing
  25.  
  26. End Sub
  27.  
Nov 30 '06 #6

Post your reply

Sign in to post your reply or Sign up for a free account.