Results 1 to 8 of 8
  1. #1
    Join Date
    Mar 2010
    Posts
    3

    Unanswered: MoveLast problems

    Hello,

    In my application I have a table where whenever a user logs in he is added at the end with the following info: name, id and time that he logged in.

    In the application you have the option to see the last person that loged in. It worked fine for a while, but one day (without chaging anything code/proprieties) it just stopped. I currently have in my table around 70 record but always the movelast comand takes me to record 47 even if I continued adding records to that table.

    Here is the code

    Private Sub Form_Load()
    DoCmd.Maximize
    Set db = CurrentDb
    Dim rst As DAO.Recordset
    Set rst = db.openrecordset("log_in", dbOpenTable)

    rst.MoveLast
    Me.user_nume = rst!nume_user
    Me.user_prenume = rst!prenume_user
    End Sub

    If anyone has any idea how what's wrong please reply.
    Thank you!

  2. #2
    Join Date
    May 2009
    Posts
    258
    Hi leyaclaire,

    I've had this same problem in the past. The recordset is not ordering the records correctly using dbOpenTable, so when it goes to the last record, it is not getting the highest ID number.

    The easiest way to overcome this problem is to order the records by ID, rather than just opening the table:
    Code:
    Dim strSQL As String
    strSQL = SELECT nume_user, login_time FROM log_in ORDER BY id"
    Set rst = db.openrecordset(strSQL, dbOpenDynaset)
    You could order descending and get the last record without using MoveLast as well.

    Another option to try would be to use the DLast function (not sure if it has the same problems as dbOpenTable), but it'd just give you the user name:
    Code:
    Me.user_nume = DLast("nume_user", "log_in")
    Regards,

    Ax

  3. #3
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    You should try opening the log_in table to see how it is ordered. It is possible to change the order of the table and save it. The reordered table is saved and if you look in the properties of the table you will see what order it is using. You should be able to open the table and resort the table and save it. But that is only a temporary fix since it can happen again.

    I agree with Ax238, you should use SQL to make sure the data is sorted properly before grabbing the last record.

  4. #4
    Join Date
    Mar 2010
    Posts
    3
    Hello again,

    Thank you both for your answers
    Ax238, I tried you solution and it work just great! I waited for a while to reply because I wanted to check if I get errors after some time like it happened before, but nothing until now

    Thanks again!

  5. #5
    Join Date
    Mar 2010
    Posts
    3
    I just encountered a problem.

    I have to create a table that changes the name/number of columns. I know this is not something recommended but unfortunately this is the way I need it.

    Because the columns are changing in the SQL statement I can't write the exact name of the columns and I tried to replace it with "*" like below

    Dim rst5 As DAO.Recordset
    strSQL = "SELECT * FROM sortiment_pf ORDER BY data, schimb"
    Set rst5 = db.openrecordset(strSQL, dbOpenDynaset)
    rst5.MoveLast
    With rst5
    rst5.edit
    rst5!produs_finit = Nz(rst5!produs_finit, 0) + Nz(Me.nr_buggy_pf, 0)
    rst5.Update
    End With
    rst5.Close

    But I get Run-time error '3265' - Item not found in this collection at the line "rst5!produs_finit = Nz(rst5!produs_finit, 0) + Nz(Me.nr_buggy_pf, 0)" where produs_finit belongs to table sortiment_pf

    Any suggestions?
    Thank you!

  6. #6
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Are you sure you have a field called produs_finit in your sortiment_pf table? I might try a copy/paste to make sure the spelling is spelled correctly.

    Nz(Me.nr_buggy_pf, 0) is a little wierd and ideally should be: Nz(Me!nr_buggy_pf, 0) to indicate a field on the form (unless you mean for it to be a field in the recordset.)

    I might suggest a little bit easier field/table naming. The characters you use can easily get mistyped. And 'data' is not a good field name (ie. strSQL = "SELECT * FROM sortiment_pf ORDER BY data, schimb"). Data is a reserved word. Be very careful on using reserved words such as: Data, Date, Time, Value, Field, Table, etc... in the naming of your table fields.
    Last edited by pkstormy; 04-07-10 at 05:42.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so what is your primary key?

    I'd suggest a composite of userid and datetime of event.. that would do away with all these issues
    optionally you could include and additional field to indicate if they logged on or logged off.

    whilst you are at it I'd also suggest you use Dev Ashish's API call to find the computerID the person logged on, and seeing as you've used his API calls you might want to look at using the Network logon. PKStormy has an equivalent set of calls, heck they may even be badge engineered versions in the codebank at the top of this forum
    I'd rather be riding on the Tiger 800 or the Norton

  8. #8
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Instead of recording each time the user logins, I use the getuser() routine healdem suggested and a simple function to update a 'LastLoginDateTime' field when the user logs in. (but you wouldn't get the history.) For example, I have a tblSysUser which has all the loginID's I allow to open the mdb and a function called: IsUser which returns true if the getuser() is in the table or false if the user is not (and then exits the mdb.)

    ex to update the lastlogindatetime field (in ADO)
    Function updateLoginDateTime()
    Dim rs as adodb.recordset
    set rs = new adodb.recordset
    dim strSQL as string
    strSQL = "Select * from tblSysUser where LoginID = '" & getuser() & "'"
    rs.open strSQL, currentproject.connection, adopendynamic, adlockoptimistic
    rs!LastLoginDateTime = now()
    rs.update
    rs.close
    set rs = nothing

    and I call this function when the app opens. The getuser() is demonstrated in the code bank and automatically grabs the user's loginID.
    Last edited by pkstormy; 04-07-10 at 06:03.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •