Item not found in this collection

[maximark]

Hi, i am trying to update a loginout table with the date/time out but i am getting an item not found in this collection message - here is the code.

Dim MyDB As DAO.Database
Dim rsLogout As DAO.Recordset
Dim MyQuery0 As DAO.QueryDef
Dim strSQL As String
Dim stDocName As String
Dim stLinkCriteria As String
Set MyDB = CurrentDb()

' Define all of the queries to be used in the procedure
'
' Retrieve tbl_Logins record
strSQL = "PARAMETERS parmLogin long; " & _
"SELECT tbl_Logins.LoginID " & _
"FROM tbl_Logins " & _
"WHERE tbl_Logins.LoginID = [parmLogin] "
Set MyQuery0 = MyDB.CreateQueryDef("", strSQL)
MyQuery0("parmLogin") = Forms!frmMainStartup!txtLoginID
Set rsLogout = MyQuery0.OpenRecordset()

If rsLogout.BOF And rsLogout.EOF Then
MsgBox "Could not insert Logout data, please notify your manager!"
rsLogout.Close
DoCmd.Quit
Else
'if tbl_Logins record found then update it with logout info
rsLogout.Edit
rsLogout("DateOut") = Now()
rsLogout("TimeOn") = Format(Now(), "Short Time") - Format(rsLogout!DateIn, "Short Time")
rsLogout.Update
rsLogout.Close
End If

DoCmd.Quit

it seems to find the record - i stored the recordid on the login screen
but i cant do the rsLogout.update?? i get the item not found in this collection

if you can help thanks very much!

 

[spikepl]

You are doing too many things in one go, without checking the intermediate steps.

1. For building queries in VBA, make the query in the query designer, check it delivers what you want (your current doesn't) , and first THEN make it in VBA
2. In VBA , use

debug.print strSQL

to dump your SQL string to the immediate window, where you can compare it with what it should be

3. Splitting datatime into day and time is only a good idea in very limited circumstances, and real PITA in most others. Do you have a good reason for the split?

BTW: "ITem not found in this collection" means just that: something that you expected to be in this specific bucket, but it wasn't. Do not panic over Access error messages but read what they actually say.

 

[maximark]

Thanks for replying Spike,

I am not having a problem with the query - it finds that the record exists -
i am getting the error when it hits the rsLogout("DateOut") = Now() line.

I am not splitting the date/time s up - i capture the date/time logged in using a general date/time format and i am trying to do the same for logout. i was going to calculate the actual time spent online to make reporting easier later on (although i might not do that either)

i was thinking the query would find the record and if it exists i could just do an edit and add the now() date/time for the log out??

 

[spikepl]

If you insist that you do not have a problem with the query then that surely will make the problem go away, right?

AS to splitting time - yes , I misread what you did. However, you are trying to find the difference between two strings, and that is a silly idea. Check in the documentation what type of output the function Format gives. And then lookup the function DateDiff in the documentation.

 

[maximark]

Hi Spike, i figured it out.
On my select statement i am only retrieving the ID field and in my edit statement i am trying to update another field that isn't in the recordset.

I agree with the datediff function - i just ran across that and plan to use it.
Thanks very much for responding to my thread and for your thoughts.

Mark

 

[spikepl]

You see ? Just a nudge and a kick in the right direction makes you perform wonders ...