need to get the last record of a table

VGuerra67
VGuerra67 used Ask the Experts™
on
I have a client table where i assign client numbers.  I would like to go to the last record and add one to the client numbers.  
This is my code.

 Set db = CurrentDb
        Set rs = db.OpenRecordset("SELECT RIGACCT FROM ClientInfo")
        rs.MoveLast
        'rs.Move -1
        'Debug.Print rs("rigacct")
        BacctNum = rs!RIGAcct
        AcctNum = BacctNum + 1
        Me.RIGAcct = AcctNum
        'Me.Requery

It stopped working for me. it gets stuck on the Set rs.  The error message that i get is in valid type.

Any help would be appreciated
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Consulting
Distinguished Expert 2017
Commented:
Hi,

It looks like you are facing an ambiguity syndrom:
you think variables are on a given type where in fact it is on another.
This can be due to the use of multiple libraries (references) that define different data types with the same name.
Example:
Recordset type in ADO library VS Recordset type in DAO library. Same name but different type.

The solution is to fully qualify your data types:
Dim db As DAO.Database
Dim rs As DAO.Recordset
'// ...ect and so on

Open in new window

Author

Commented:
thank you
Remya 6488Senior Programmer

Commented:
HI,

I think instead of iteratiing the resultset till the end, you can use a simple  query like
    SELECT TOP 1 RIGACCT + 1 FROM ClientInfo ORDER BY ID DESC
   where, Id is the unique auto generating column of the table

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial