Please help with incrementing a field

[Guest]

I am using the following code to increment a field called "SubjectOrder":
*************************
Dim v_Max as String
v_Max = Format(DMax("SubjectOrder", "tblCBC") + 1, "000")
Me![SubjectOrder] = v_Max
**************************
The code works fine if the user enters one record at a time. However, if the
user tries to copy and paste multiple records the code will paste the same
max ID over, for all the pasted records. How can I make this field
"SubjectOrder" increment in case of copy and paste?

As to the question why I did not make this an Autonumber, is that I am going
to make this db as a replica and when this happens the Autonumber becomes
random and give all sort of negative #s, and since the order is very
important in this table, I can not use an Autonumber. I do appreciate any
help. thanks
Al

 

[Nikos Yannacopoulos]

Al,

One way to handle this is to use an intermediate table into which the user
can paste records, and then some VBA code (perhaps behind a "Store Records"
command button on the form) which will store the records in the target table
one by one while calculating the ID, and clear the intermediate table at the
end.

HTH,
Nikos