Updating a Counter Field in a Transaction Block 1

[RippleJD]

I need to increment a field within a transaction block. I call a function like the following within the transaction (there is more to the function, but this is the relevant portion).

Public Function UpdateGL() As Boolean
Dim rsGL As Recordset
Dim NewGLID As Integer
Dim db as Database

Set db = CurrentDb()
Set rsGL = db.OpenRecordset("select * from [GL_Table]&quot
NewGLID = DMax("[GL_ID]", "[GL_Table]&quot + 1
With rsGL
.AddNew
![gl_id] = NewGLID
.Update
End With

End Function

If I call this function a second time within the same transaction, the counter does not increment. It stays the same as the first pass. Is there any way to access the "current" value of GL_ID without committing the transaction?

Thanks!

JD

 

[Lightning]

Unless there is a good reason to keep it open, I suggest you close the recordset after the update. Then if you need to call the function again, the recordset will re-open with the updated dataset.

HTH

Lightning

 

[jfischer]

Try making a separate table "GL_NextNum" with one record and one field in it, [NextGL_ID]. Open "GL_NextNum" and set the intial value of [NextGL_ID] (to DMax("[GL_ID]", "[GL_Table]&quot + 1) at the beginning of your transaction block. Then, still inside your transaction block, not in a separate function, access (.MoveFirst) and update the field [NextGL_ID] for each corresponding record you add to "GL_Table". That should resolve any transaction block issues.

 

[RippleJD]

Thanks folks! I was able to use the same GL_Table, but I used the Max(GL_ID) function in a recordset instead of using the DMax function.

Set rs = db.OpenRecordset("select max([GL_ID]) from [GL_Table]&quot

I guess the key is using a recordset object instead of a domain aggregate function.

Thanks again and happy holidays!

JD