Recordset Issue (1 Viewer)

[mkaeser]

Hello All,

I have the following code:

techid = Forms!Main!txtCurrentUserID
Task = 2
note = "Work Instructions"
strSQL = "SELECT *"
strSQL = strSQL & "FROM [tblTracking] "
strSQL = strSQL & "WHERE ([EmpID]=" & techid & ");"
Set rs = CurrentDb.OpenRecordset(strSQL)
With rs
.MoveLast
If !endtime = "" Or IsNull(!endtime) Then
.Edit
!endtime = Time
.Update
End If
End With

For whatever reason, this code will move to the last record that was entered in YESTERDAY, but I want it to move to the last record that was entered in TODAY. If I change the code to only records that are dated today, it claims there are no records (EOF), when there clearly are. I am not sure why the code will not move to the last record of the table, it is only moving to the last record that was entered yesterday, any advice? Thank you!:banghead:

 

[tehNellie]

Ignoring the date issue for now, your SQLis not sorting your data at all so the rather un-necessary bit with the recordset (use queries, that's what they're there for) is going to the last record in your results, not necessarily the last record in the table.

Do not assume an order of the data in your query output that you have not explictly defined.

Initially stick a debug.print strSQL after you've built the string, are you actually creating the SQL that you think you are?

Run the SQL in the query editor and look at the results, that is what is feeding into the recordset. If you don't agree with the results, modify the SQL and re-run it, once you know it is acting as you expect then if you really insist on creating dynamic SQL in code and all the fannying around with recordsets then paste it back into your module and try it again, if it's still misbehaving then you can at least eliminate the SQL as a reason.

But if you create and call a query instead then you can debug the SQL directly within the query, independent of the code, and see what is going on, be happy that it works as you expect and then just call the query from the code instead.

 

[mkaeser]

Thank you for that information. I ran a query and saw the information is not being organized according to the table. I guess I was wrong in assuming it would be, thank you! I will work at it and try to get things in order.