Yet another "Item not found in this collection" post

[KACJR]

I come again to the Well of Knowledge...

I have dealt with and resolved "Item not found in this collection" errors before. This is a new on me because it happens when the SQL statement "DELETE tbl.* from tbl" command. What would cause this on something as simple as a DELETE statement?

Regards,
Ken

 

[CJ_London]

please clarify - this is a query have created? or a sql string you have written to subsequently execute? And if the latter, what method are you using to execute the sql string?

 

[Isaac]

Post all your code

 

[KACJR]

please clarify - this is a query have created? or a sql string you have written to subsequently execute? And if the latter, what method are you using to execute the sql string?

This is an SQL statement executed within a VBA module. This statement has been executing without fail forever. It just started doing this today. I could understand if this happened because of a missing field, but a DELETE statement??

 

[Isaac]

How do you know this statement caused it to fail? Where did you put the breakpoint - on which line of code? If not, put one to be sure

 

[KACJR]

Post all your code

Private Sub PhoneBook_Click()
On Error GoTo ShowMeError
ProgressMessages = ProgressMessages & "Updating Phone Directory." & vbCrLf: ProgressMessages.Requery
TILLDataBase.Execute "DELETE tblPhoneDirectory.* from tblPhoneDirectory;", dbSeeChanges
TILLDataBase.Execute "INSERT INTO tblPhoneDirectory ( Department, Location, LocationDetail, LastName, FirstName, EmailAddress, JobTitle, " & _
"InternalExtension, HasPhoneOnDesktop, ExternalPhoneNumber ) " & _
"SELECT tblPeople.Department, tblPeople.OfficeCityTown AS Location, tblPeople.OfficeLocationName AS LocationDetail, tblPeople.LastName, " & _
"tblPeople.FirstName, tblPeople.EmailAddress, tblPeople.StaffTitle AS JobTitle, tblPeople.DID AS InternalExtension, " & _
"tblPeople.HasPhoneOnDesktop, tblPeople.StaffExtPhone AS ExternalPhoneNumber " & _
"FROM tblPeople WHERE (((tblPeople.IsStaff) = True)) " & _
"ORDER BY IIf([Department]='Residential Services',1,0), tblPeople.Department, tblPeople.LastName, tblPeople.FirstName;", dbSeeChanges
ProgressMessages = ProgressMessages & "Preparing report." & vbCrLf: ProgressMessages.Requery
Call ExecReport("rptDirectoryAlphaShort")
ProgressMessages = "": ProgressMessages.Requery
Exit Sub
ShowMeError:
Call DropTempTables
MsgBox "Error # " & Str(Err.Number) & " was generated by " & Me.Name & Chr(13) & Err.Description, vbOKOnly, "Error", Err.HelpFile, Err.HelpContext
End Sub

 

[KACJR]

How do you know this statement caused it to fail? Where did you put the breakpoint - on which line of code? If not, put one to be sure

I put the breakpoint at the beginning of the sub. The error got thrown when I stepped into the DELETE statement.

 

[Isaac]

What's TILLDataBase ?

 

[KACJR]

What's TILLDataBase ?

Dim TILLDatabase as Database

 

[CJ_London]

Have you verified that TILLDataBase exists and isn't nothing? You are not substantiating it within your code

you need a Set TillDataBase=something somewhere

 

[Isaac]

But it's not

 

[KACJR]

But it's not

It is. It's a Public Dim It's used in multiple Subs and Functions throughout my app.

 

[theDBguy]

Just curious, what is ProgressMessages? Maybe try using Recalc instead of Requery?

Just guessing...

 

[KACJR]

OK, following your suggestions, I learned that the TILLDatabase variable is, in fact, Nothing. Somewhere in the midst of processing, TILLDatabase lost its value. Now I need to trace where this happened because it definitely should not be. The only place where I explicitly set it to Nothing is right before I close the app.

My workaround is to take those Execute commands and move them into a Query. This resolved the immediate issue but now I need to trace the place where TILLDatabase is getting "Nothing-ed" out.

 

[Isaac]

You'd still have to set it - yeah I would just declare and set it within the procedure. I would not try to have it always going - I've never tried that before but can't imagine relying on it

is it really that hard to just change your code to

dim tilldatabase as dao.database
set tilldatabase = currentdb

??

and you should use DAO.

 

[KACJR]

You'd still have to set it - yeah I would just declare and set it within the procedure. I would not try to have it always going - I've never tried that before but can't imagine relying on it

is it really that hard to just change your code to

dim tilldatabase as dao.database
set tilldatabase = currentdb

??

and you should use DAO.

In eleven years, this has never happened. But I will take your suggestion under advisement.
Yes, the Dim state is DAO.Database; I just didn't spell it all out.

 

[Minty]

The other option is to make the TILLDatabse object a self healing property. (It's not directly possible with the database object but the article below shows how to do it.)
It then can't ever be nothing when it is called upon.

Have a read here:

Use self-healing objects to avoid issues in your code.

Use self-healing objects to avoid problems with objects not being initialized when running your code.

accessexperts.com

or here:

Self-Healing Object Variables | DEVelopers HUT

Exploring how we can replace traditional CreateObject calls with Self-Healing variables to improve overall application performance and simplify code.

www.devhut.net

 

[Gasman]

OK, following your suggestions, I learned that the TILLDatabase variable is, in fact, Nothing. Somewhere in the midst of processing, TILLDatabase lost its value. Now I need to trace where this happened because it definitely should not be. The only place where I explicitly set it to Nothing is right before I close the app.

My workaround is to take those Execute commands and move them into a Query. This resolved the immediate issue but now I need to trace the place where TILLDatabase is getting "Nothing-ed" out.

Can't you use the Watch window for that?

 

[The_Doc_Man]

I put the breakpoint at the beginning of the sub. The error got thrown when I stepped into the DELETE statement.

If you stepped into that statement, you haven't executed it yet. You should be able to use the mouse cursor to hover over the TILLDataBase variable to see its content - which will be something or "(nothing)" - BEFORE you execute that statement.

Also as a simplification, when you have a single-source query (i.e. only one table in the FROM clause) you don't have to qualify anything. You can just do "DELETE * FROM tblPhoneDirectory;". You need to qualify something only when there is a JOIN going on inside the query.

 

[ebs17]

Strictly speaking, a delete query always eliminates entire records. There is no need for a field list:

DELETE FROM tblPhoneDirectory