RecordsetClone: "Item not found in this collection" error (1 Viewer)

tonylomax · Oct 19, 2018

I have a form that shows records that match the users input parameter. On this form I've created a button with the following VBA Sub:

Code:

Private Sub Send_Email_Click()
        
    Dim rs As DAO.Recordset
    Dim bcc As String
      
          Set rs = Forms!KeywordSearch.RecordsetClone
          If rs.RecordCount > 0 Then
              rs.MoveFirst
              Do
                If Not IsNull(rs!Email) Then
                      bcc = bcc & rs!Email & ";"
                      rs.MoveNext
                 Else
                      rs.MoveNext                              
                   End If                   
             Loop Until rs.EOF
                          
        DoCmd.SendObject acSendNoObject, , , "", , bcc, , , True
                 End If                 
End Sub

The purpose is to open an email with all the emails copied into the BCC box on the email. This sub works fine on one form (ContactSearch), however when I copy it over to another very similar form (KeywordForm) I get the Item not found error.

The only difference in the forms that might be relevant is that the KeywordSearch query uses a table with multi-valued fields (I know this is bad form, I didn't make it and will figure out how to change it a later date). I've checked the name of the Form/Field and everything is spelt correctly.

Could the issue be related to different subs using RecordsetClones?

Thanks for any help/suggestions!

Ranman256 · Oct 19, 2018

theres no need for Clone.
use a query to get your records then get them all. (no searching)

same with finding a record, use a filter.
me.filter = "[id]=" = txtBox
me.FilterOn = true

faster, simpler.

Minty · Oct 19, 2018

@Ranman - how does that help with building a BCC email string collection ?

You still need to loop through the recordset, and the OP is using a form to filter down the list of contacts by the sounds of it, then using that as his recordset. A query could only do part of this.

@OP what happens if you remove the multivalued field from the forms recordset as a test?

The_Doc_Man · Oct 20, 2018

Please confirm for us that the highlighted line is

Set rs = Forms!KeywordSearch.RecordsetClone

and not

If Not IsNull(rs!Email) Then

Also, Minty's question is relevant. I suspect that when an MV field is involved, the recordset isn't what you think it is - which could explain why it thinks there is no such item.

Finally, and this is just a "Duh" type of question... Is the form named KeywordSearch open at the time? Because if not, THAT is the item not found, and it is not found in the Forms! collection.

tonylomax · Oct 22, 2018

Thanks for the replies!

@Minty Yes that's exactly what I'm doing with the form

I've tried removing the multivalued field from the form and the issue persists. I might try and change the query itself to see if that does it.

@The_Doc_Man

The highlighted field is "If Not IsNull(rs!Email) Then"

When I hover over the "rs.recordcount" line it shows the correct number of records, when I hover over the line above it shows the item not found error. Which suggests it is seeing the correct recordset and it's only the Email field which it can't see.

Yes the KeywordSearch is definitely open at the time, it's from a button within that form that I'm running this code.

arnelgp · Oct 22, 2018

is Email a multivalue field?

tonylomax · Oct 22, 2018

arnelgp said:
is Email a multivalue field?

No the only multivalued field is one called Keywords.

arnelgp · Oct 22, 2018

is Email field included in KeywordSearch query. run the query to verify.

tonylomax · Oct 22, 2018

arnelgp said:
is Email field included in KeywordSearch query. run the query to verify.

Yes it it!

arnelgp · Oct 22, 2018

is KeywordSearch query the recordsource of your form?
are you sure the button is in the correct form?
try to debug:

Code:

Private Sub Send_Email_Click()
        
    Dim rs As DAO.Recordset
    Dim fld as da.field      
          Set rs = Forms!KeywordSearch.RecordsetClone
    debug.print rs.Name
    debug.print ""
    for each fld in rs.fields
         debug.print fld.name
   next
   set rs=nothing
End Sub

tonylomax · Oct 22, 2018

KeywordSearch is the source yes and yes I've checked the button and the spelling of everything a few times.

When I debug I get this:

KeywordSearch

Database.Contact_ID
Database.Full Name
Database.Job Title
Institution.Institution
KeywordSearch

Database.Contact_ID
Database.Full Name
Database.Job Title
Institution.Institution
Database.Email
Database.Email 2
Database.Work phone
Database.Work phone 2
Database.Mobile
Database.Notes
Database.Date created
Keyword Junction.Keywords
Database.Record Type
Database.Email
Database.Email 2
Database.Work phone
Database.Work phone 2
Database.Mobile
Database.Notes
Database.Date created
Keyword Junction.Keywords
Database.Record Type
KeywordSearch

Database.Contact_ID
Database.Full Name
Database.Job Title
Institution.Institution
Database.Email
Database.Email 2
Database.Work phone
Database.Work phone 2
Database.Mobile
Database.Notes
Database.Date created
Keyword Junction.Keywords
Database.Record Type
KeywordSearch

Database.Contact_ID
Database.Full Name
Database.Job Title
Institution.Institution
Database.Email
Database.Email 2
Database.Work phone
Database.Work phone 2
Database.Mobile
Database.Notes
Database.Date created
Keyword Junction.Keywords
Database.Record Type

KeywordSearch

Database.Contact_ID
Database.Full Name
Database.Job Title
Institution.Institution
Database.Email
Database.Email 2
Database.Work phone
Database.Work phone 2
Database.Mobile
Database.Notes
Database.Date created
Keyword Junction.Keywords
Database.Record Type

isladogs · Oct 22, 2018

Post 11 was moderated. Now approved. Posting here to trigger email notifications

arnelgp · Oct 22, 2018

can you use Hard code, your email is position 4 (zero based).

bcc = bcc & (rs(4) + ";")

Gasman · Oct 22, 2018

Why is the first block so much smaller than the others, with no Email in it?

tonylomax · Oct 22, 2018

arnelgp said:
can you use Hard code, your email is position 4 (zero based).

bcc = bcc & (rs(4) + ";")

Thanks for the suggestion tried it and no joy, the only difference is that when I hover over it, it does shoe the first entry in the email field which it doesn't on my original code.

@Gasman

Good question, I don't know and should point out that I didn't create this query

The_Doc_Man · Oct 22, 2018

First comment:

Yes the KeywordSearch is definitely open at the time, it's from a button within that form that I'm running this code.

In that case, simplify your life. Change

Code:

Forms!KeywordSearch.RecordsetClone

to

Code:

Me.RecordsetClone

Second comment: The experiment proposed by arnelgp shows that you have optional items in the list. See, for example, the first section of the printout. It DOES NOT contain an Email field. This is common with all MS Office, but particularly when dealing with Outlook.

What happens (and I'll try to explain this as clearly as my muddled thinking will allow) is that you instantiate an object. The object has optional parts. If those parts come into being only for a particular option, then the parts contained within the option don't exist unless the option applies.

This happens in other situations as well. When you have a bound form, the bound controls have a .Value property and a .OldValue property. But if you take that same exact form and erase the .Recordsource property of that form, the controls that WOULD have been bound no longer have a .OldValue property. It is not that it is Null. It is that the .OldValue property DOES NOT EXIST.

I suspect that something similar is going on, and arnelgp's experiment revealed this case to be true: The !Email sometimes does not exist. It is not merely Null (which offers its own complications.) It DOES NOT EXIST, hence the "not found in this collection" error.

When I run into things like this, I end up having to run a test kind of like this:

Code:

EMailX = ""
XError = 0
On Error Resume Next
EMailX = NZ(x.Email,"")
XError = Err.Number
On Error GoTo {my error handler}
If XError <> 0 Then Goto {not there}

By bracketing the extraction of the field in question with the error handler override, I get either the content of the .Email item or I get an empty string, but I CANNOT get the error about "not found in collection" because at that moment I have blocked error traps. This method allows me to differentiate between "really not there" and "there but null or empty." But if you wouldn't care and would treat either case as "no email" then the part dealing with XError is optional and could be omitted.

arnelgp · Oct 22, 2018

no joy, try this:

bcc = bcc & (rs("Database.Email") + ";")

tonylomax · Oct 23, 2018

@The_Doc_Man

Thanks, I'm still pretty new to Access/VBA so not had much look with the code you suggested however I've been using Debug.Print to look at the recordset and the email field is showing up as NULL.

@arnelgp This hasn't worked though I think it's definitely some issue with the recordset itself rather than the syntax to get the fields.

tonylomax · Nov 21, 2018

@The_Doc_Man I've taken a break and come back to the issue hoping a solution would have occurred to me - it hasn't. Assuming !Email does not exist, which seems likely, is there anything I can do to re-instantiate the object?

The_Doc_Man · Nov 21, 2018

If the object exists from another application like Outlook, the issue is that the other app will define what it thinks is correct. If in this context it chooses to not instantiate certain properties, it is because the properties were associated with a feature that wasn't used.

Office tends to be an example of "sparse" programming. That term, if you analyzed it closely enough, means "why build it if you don't need it?"

Therefore, one of the things that is most useful in any multi-object situation, is the ability to determine of the given properties are present and take (for lack of a better term) avoidance measures. You want to avoid even making the reference. That means that you pre-decide what you will store in any variable when the variable's source hasn't been created and therefore is totally inaccessible. Whether you choose to insert a blank or put it a "not defined" string (or words to that effect) is kind of up to you. Either way works.

Re-instantiating something that another app didn't want to build is tricky because you don't know how the other app will respond when it sees something IT didn't build when it had the chance. Now if you went ahead and instantiated the entire object including those properties by attaching something to be sent, that is one thing. But to just create an empty, never-used dummy FOR ANOTHER APP is questionable and COULD (not saying WILL) have unusual side effects.

RecordsetClone: "Item not found in this collection" error (1 Viewer)

Registered User.

Well-known member

AWF VIP

Immoderate Moderator

Registered User.

..forever waiting... waiting for jellybean!

Registered User.

..forever waiting... waiting for jellybean!

Registered User.

..forever waiting... waiting for jellybean!

Registered User.

MVP / VIP

..forever waiting... waiting for jellybean!

Enthusiastic Amateur

Registered User.

Immoderate Moderator

..forever waiting... waiting for jellybean!

Registered User.

Registered User.

Immoderate Moderator

Similar threads

Users who are viewing this thread