VBA for next number stopped working

[Cozzy]

Morning people I have a problem with some vba coding, basically I have this code

#Private Sub Text6_DblClick(Cancel As Integer)
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim i As Integer
Dim strSQL As String
Set dbs = CurrentDb
strSQL = "select * from [tbl sale invoices]"
Set rst = dbs.OpenRecordset(strSQL, dbOpenDynaset)
rst.MoveLast
i = rst![Sale Invoice ID]
Forms![FRM Invoice dataview]!Text6 = i + 1
Set rst = Nothing
Set dbs = Nothing
End Sub#

now I don't know what the customer has done as when I handed it over this always worked as it should (generating the next highest number for there invoice number), but now it seems to be suck on a specific number "6009" if i keep double clicking on other orders it starts plusing one and then randomly goes back to "6009"??? if I carrying on click again will again start adding from the highest number again?

 

[vbaInet]

Have you tried DMax()?

 

[vbaInet]

now I don't know what the customer has done ...

Why does your client have access to the code?

Have you tried DMax()?

 

[Cozzy]

I have locked down the database, they can't access the code, tables etc, but they can delete records for orders, invoices etc, all I know it was working and I was told that they "accidentally" deleted records as they didn't realise it was live data, can you give me an example of dmax()

thanks for your response

 

[vbaInet]

http://lmgtfy.com/?q=ms+access+dmax

 

[Cozzy]

lol already done that, thanks anyway

 

[Galaxiom]

Your recordset has no Order By on the SQL. Consequently the last record won't necessarily be the one with the highest number.

Only luck has kept it working until it broke like this.

You could add the OrderBy on the InvoiceID to the SQL and that will fix it. Then your function will essentially be doing the same as a DMax function.