I have a form on which data can added. Each records gets its own recordnumber which is made on moment of enetering data. (I cannot use the internal number because in backgrounf sometime records are added and deleted again)


If I look into the table I see that my field ID in this table starts numbering from 1 after some hundred records. I'm not sure but I think that somehow the recordcount give 0 on some unexpected moment.
Does anybodey see a mistake in the coding?

I use the code below to do so:

Private Sub Form_BeforeInsert(Cancel As Integer)
Dim con As ADODB.Connection
Dim rst As ADODB.Recordset
Set con = CurrentProject.Connection
Set rst = New ADODB.Recordset

On Error GoTo Error_BeforeInsert
rst.Open "Ontvangsten", con, adOpenStatic, adLockPessimistic

If rst.RecordCount = 0 Then 'this is done for the situation the table is empty
Me!ID = 1
Else
rst.MoveLast
Forms![Ontvangsten]!Nummer = rst!ID + 1
End If
rst.Close

You have no way to know what the 'last record' is going to be. This kind of thing is normally done using the DMax() function against the ID field and then adding 1 to that maximum number.

The Missinglinq

Richmond, Virginia

The Devil's in the Details!

Thanks that works fine; is there a simpel query which let met (re) number all the records starting from 1?
I mean a query not with VBA?

Create an Update Query using DMAX+1

Would love to, but I do not know how to work with the DMAX
Could you give an example?

Is this the same issue as thread701-1691734: update query for numbering records? Can we assume your question(s) have been answered?

Duane
Hook'D on Access
MS Access MVP

Almost; I'm still interested in the solution with DMAX for the DCount is very inefficient; I'm working on table with some 1000 records situated on a sharepoint. DCOUNT solution took me 10 minutes to run, although it workde fine!

If you don't know how to work with the DMAX function, press the f1 button.

Better than F1

Duane
Hook'D on Access
MS Access MVP

Great guys!

Gr. Willem