Default Primary Without Using Autonumber

[PsyClone]

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

[MMcCarthy]

On the Form Current Event ...

 
Private Sub Form_Current() 
Dim tempID As Integer
 
   tempID = DMax("ID","TableName")
   tempID = tempID + 1
   If IsNull(Me.ID) Then
	  Me.ID = tempID
   End If
 
End Sub

[PsyClone]

SWEET!! Thanks Mate!

[MMcCarthy]

SWEET!! Thanks Mate!

You're welcome.

Mary

[bubblegirl]

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

[MMcCarthy]

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 ...

   
Private Sub Form_Current()
Dim db As Database
Dim rs As DAO.Recordset
Dim tempID As Integer

   Set db = CurrentDB
   Set rs = SELECT Max( Left([Client ID], InStr([Client ID], "/") - 1)) As MaxID FROM TableName;
 
   If rs.RecordCount=0 Then
	  Exit_Sub
   End If
 
   tempID = rs!MaxID
   tempID = tempID + 1
   If IsNull(Me.ID) Then
	  Me.[Client ID] = tempID & "/" & Month(Now()) & "/" & Year(Now())
   End If

Exit_Sub:
 
   rs.Close
   Set rs = Nothing
   Set db = Nothing

End Sub