Problems with rs.recordcount 1

[Schaap]

I gotta database and I wanna add a new record to this database. To arrange the next indexnumber for the new record I use rs.RecordCount (see code below) this is possible because the first record has indexnumber "1".So a new indexnumber for the new record will be rs.recordcount + 1 !!!

This doesn't work properly ! I get a wrong value for this recordcount. And it did work some time ago, but I changed (a lot) and I don't know what the problem can be.

It's about the second rs set and the InvestNR/IndexNR in the code below :

Set rs = Db.OpenRecordset("select InvestDate, InvestNumber From InvestmentPlanning order by IndexNumber desc;")
If Datum = rs!InvestDate Then
InvestNR = rs!InvestNumber + 1
Set rs = Db.OpenRecordset("select * From InvestmentPlanning;")
IndexNR = rs.RecordCount + 1
DoCmd.OpenForm "Newapplicationform", acNormal
Else
If Datum > rs!InvestDate Then
Set rs = Db.OpenRecordset("select * From InvestmentPlanning;")
IndexNR = rs.RecordCount + 1
InvestNR = "001"
DoCmd.OpenForm "Newapplicationform", acNormal
Else
End If
End If

 

[PHV]

Have you tried a call to the rs.MoveLast method before testing the rs.RecordCount property ?
You may also consider have a look at the rs.EOF property to make your code more robust.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Schaap]

Thanks for the info, dude

It was helpfull