Incrementing an "index" in a query.

[kjoost001]

I have used some VBA code to increment a "index" row in an append query. See below. The code works correctly when used from my local desktop, but once accessed through citrix, it does not perform the same. What happens is: it calls the function once, and for each record, it places the same "index". My goal is to have unique "indicies". I cannot figure out why this occurs. Anyone know?

Global IncrementVariable As Long
Global FT_SOURCE_ID_OLD As Long
Global FT_SOURCE_ID_NEW As Long

Function IncrementValues(FT_SOURCE_ID_NEW) As Long
If (FT_SOURCE_ID_NEW = FT_SOURCE_ID_OLD) Then
Else
IncrementVariable = 0
End If
IncrementVariable = IncrementVariable + 1
FT_SOURCE_ID_OLD = FT_SOURCE_ID_NEW
IncrementValues = IncrementVariable
End Function

 

[scking]

Is there some reason you don't want to use an autonumber field? You could use DMax to get the largest number in the index directly from the table and then increment it by one. It has the advantage of not requiring any global variables, which incidentally get reset on a system error.

Dim Incr As Long
Incr = DMax("[FieldName]", "Tablename&quot + 1


----------------------
scking@arinc.com
Life is filled with lessons.
We are responsible for the
results of the quizzes.
-----------------------