Recordset2 user-defined type errors

[Aeristan]

Good morning. I'm new to the forums, and I'd be so grateful if I could get some help. I do my best to research all the questions already answered, but this one has me baffled. (I am using Microsoft Office 365 ProPlus, so, I think it's Access 2016.)

I am working with a Form, form_CHARACTER, which is bound to a table named CHARACTER. I am trying to work with Me.Recordset in the event handlers, to do what seems to be simple tasks. There must be an easy way that I have overlooked.

Upon opening this Form, I want to set the current record to a specific Character, according to their name.

Here is the location of the error: (I have stripped out things like VbCrLf and vbTab, to make the code more readable)

Private Sub Form_Open(Cancel As Integer)
    Debug.Print ("form_CHARACTER: form Open event   ( Character = " & Me.ID & " """ & Me!Name & """ )")
    Debug.Print ("TypeName(Me.Recordset) = " & TypeName(Me.Recordset) & " Me.Recordset.Name = " & Me.Recordset.Name)

...

    Me.Recordset.FindFirst ("[Name] = ""Raven"" ")
End Sub

Here is what I get in the Immediate window:

form_CHARACTER: form Open event   ( Character = 4 "Sarjenka" )
    TypeName(Me.Recordset) = Recordset2    Me.Recordset.Name = CHARACTER

...and then the code fails at the last line with the following error message:

Method 'FindFirst' of object 'Recordset2' failed
​

The TypeName of the Recordset is coming back as Recordset2. I did my research of course, and I see that Recordset2 is listed as a member of DAO. The purpose of this object type is to accommodate multi-value fields and other special situations. The docs clearly state, however, that "a Recordset2 object contains all of the same properties and methods as the Recordset object. The Recordset2 object contains a new property, ParentRecordset, that support multi-valued field types."

I am not finding that to be true. When I dim a variable as DAO., the auto-complete feature includes Recordset and Recordsets properties, but not Recordset2. A type mismatch error occurs if I try to refer to this Recordset as a DAO.Recordset object. Then, however, I have the following subroutine:

Public Sub debug_Recordset (rst As Object)
    Debug.Print ("--> debug_Recordset(" & rst.Name & ")     rst.Type = " & rst.Type & " TypeName(rst) = " & TypeName(rst) )
End Sub

Which fails with the following message:

Run-time error 438: Object doesn't support this property or method
​

That part really confuses me, because I thought that all Object types in VBA had Name and Type properties.

A couple of things:

1) I'm using a DAO Recordset... or at least, I think I am...??? I disambiguate this in my code using the DAO reference, but I don't know how to set - or check - the default Recordset type of the Form itself.

Here is my list of references, in order:
Visual Basic for Applications
Microsoft Access 16.0 Object Library
Microsoft DAO 3.6 Object Library
OLE Automation​

2) The CHARACTER table does not contain any multi-valued fields. I double-checked. There is another table, ITEM, in my database that does contain one such field, but that table is not referenced in any way from this form.

3) I am certain that it didn't always do this. I have gotten this form to work and have spent a lot of time working with it and it's subforms. The problem started a couple weeks ago. However, as I go back through my older backups, I am unable to find any files within the last few months which aren't giving the same error.

My goal: to get the Recordset2 object to show the same properties and methods as the Recordset object, like the docs say that it should, OR to coerce a Recordset2 object into becoming a "normal" Recordset object.

Thoughts?

 

[theDBguy]

Hi. By adding a reference to DAO 3.6 library, I think you lose access to a Recordset2 object, that's why you don't get it with intellisense. Try removing the reference to DAO and put back the one for ACE.

 

[MajP]

The brackets are incorrect and cannot be used here.
Me.Recordset.FindFirst "[Name] = ""Raven"" "

Not sure if that is the issue because the recordset2 thing is strange.

 

[Deleted member 147267]

I can only remember dealing with Recordset2 object once before and don't recall much about it. However, I can definitely say that not all properties are exposed by intellisense, so don't let that throw you. Go by the documentation. Here's my shortcut
https://docs.microsoft.com/en-us/office/vba/api/overview/access/object-model
At the bottom of the left nav list is the language link, which expands to cover vba topics. Sometimes the two areas point to the same subject matter, sometimes not as there is some cross-over.

 

[The_Doc_Man]

Your sub debug_Recordset( rst as object) would fail in that context if the item passed in to the subroutine as actual parameter 1 was not instantiated at the time.

You should be able to discover whether Recordset2 is available by going into Object Browser and searching for that name. It will tell you which libraries actually use that kind of object, so that way you know what kind of reference to set.

 

[Aeristan]

Hi. By adding a reference to DAO 3.6 library, I think you lose access to a Recordset2 object, that's why you don't get it with intellisense. Try removing the reference to DAO and put back the one for ACE.

ACE? I don't see any library in the list with those letters in the name. Will you tell me the exact name of it please?

The brackets are incorrect and cannot be used here.
Me.Recordset.FindFirst "[Name] = ""Raven"" "

You're right and that does make sense. The error is still occurring.

I can only remember dealing with Recordset2 object once before and don't recall much about it. However, I can definitely say that not all properties are exposed by intellisense, so don't let that throw you. Go by the documentation ... At the bottom of the left nav list is the language link, which expands to cover vba topics.

I... apologize again but I just don't see that object listed in the "language reference" section at the bottom of the nav window. All I can find in the docs is this:

docs . microsoft . com /en-us/office/client-developer/access/desktop-database-reference/recordset2-object-dao, which lists Recordset2 as a member of DAO.

( Sorry, I haven't made 10 posts yet! )

Your sub debug_Recordset( rst as object) would fail in that context if the item passed in to the subroutine as actual parameter 1 was not instantiated at the time.

...wouldn't it be? I am calling that subroutine from the Form_Open event, and passing in Me.Recordset .

You should be able to discover whether Recordset2 is available by going into Object Browser and searching for that name. It will tell you which libraries actually use that kind of object, so that way you know what kind of reference to set.

It isn't listed at all in the object browser, but I suspect that this is because I have yet to correct the library reference situation that theDBguy was explaining. I can't find a library with "ACE" in the name.

 

[Aeristan]

I'm sorry I haven't gotten back to you yet. I haven't ignored your advice, I simply haven't had time to code in the last few days. This is where I will pick up when I get back to it. Thank you in advance.

 

[The_Doc_Man]

For Ac2010, Recordset2 is instantiated as a Class in the DAO library.

As to passing in Me.Recordset, that SHOULD be OK as long as it is a bound form.

 

[essaytee]

I've checked my references for a sample db and they are a follows:

Try adding in the Microsoft Office 16.0 Access databases engine Object Library in lieu of the DAO reference.
In a test form load

    Debug.Print ("TypeName(Me.Recordset) = " & TypeName(Me.Recordset))
    TypeName(Me.Recordset) = Recordset2

I never noticed this Recordset2 before.

In my intellisense when dimming a recordset I now notice a Recordset and Recordset2.

If all else fails, there's the old decompile, compact and repair. The equivalent of IT help desk saying, turn your computer off and on and that should fix things.

 

[theDBguy]

I've checked my references for a sample db and they are a follows:

Try adding in the Microsoft Office 16.0 Access databases engine Object Library in lieu of the DAO reference.
In a test form load

    Debug.Print ("TypeName(Me.Recordset) = " & TypeName(Me.Recordset))
    TypeName(Me.Recordset) = Recordset2

I never noticed this Recordset2 before.

In my intellisense when dimming a recordset I now notice a Recordset and Recordset2.

If all else fails, there's the old decompile, compact and repair. The equivalent of IT help desk saying, turn your computer off and on and that should fix things.

Hi Steve. Thanks. You've pretty much explained in more detail what I was trying to say in my first post. Recordset2 came with the ACE engine (2007 and later), which replaced the JET engine. The ACE engine includes DAO, so a separate reference to another DAO library (3.6) is not allowed. Cheers!

 

[The_Doc_Man]

Just for snorts and giggles: Leave both MS Office DB Engine Object AND DAO checked in the references, but move DAO below DBEO. See if it works correctly.

 

[theDBguy]

Just for snorts and giggles: Leave both MS Office DB Engine Object AND DAO checked in the references, but move DAO below DBEO. See if it works correctly.

Hi. If you’re saying to put a check on both DAO and ACE, I’m pretty sure you won’t be able to.

 

[The_Doc_Man]

I don't have that version of Access so cannot tell you with any certainty. However, I have run into situations where conflicting definitions can be resolved by re-ordering references that contain the same options - which would therefore appear to be in conflict. But in fact, they are NOT in conflict until you use them in an open search. The fact that they have common elements is immaterial until you try to use them in a way that leads to detection of the conflict. Changing the order changes the nature of the conflict.

The REAL resolution in such cases is to always quantify which library you wanted. For example, where you have both DAO and ADO recordsets to consider, ALWAYS create the recordsets with the proper prefix. However, for testing, it MIGHT be enough if you actually CAN check both.

BTW, when I looked in Object Browser and tried to examine Recordset and Recordset2 for DAO, they looked identical. Unless I missed some fine detail, I don't see why Recordset2 would even exist.

 

[theDBguy]

BTW, when I looked in Object Browser and tried to examine Recordset and Recordset2 for DAO, they looked identical. Unless I missed some fine detail, I don't see why Recordset2 would even exist.

According to this article, they are exactly the same except Recordset2 has an additional property to handle MVFs.

 

[The_Doc_Man]

Thanks, theDBguy. Yet ANOTHER reason to not like multi-valued fields because of what they force on people. An idea whose time has come - and gone.

 

[Aeristan]

Good evening!

I have been working on my Recordset2 problem this evening, and I have implemented most of the advice I have received. Unfortunately, I am still experiencing the error intermittently. I can't recognize any pattern as to which records it fails on, because all of the Recordsets are coming back as Recordset2.

Another strange occurence that I am seeing is this: the Recordset.FindFirst method is being called in the Change event of combo_ID. However, the Form_Current event fires, and completes execution (relative to the new record), before the error in question occurs! Instead of allowing the unhandled error to crash my code, I threw in a quick handler. All that it does is dump some text to the Debug window and resume. And -- after the code continues -- the desired record is displayed and all of the controls populated! So whatever else may be going on, the code IS changing the current record.

...which makes it very tempting to just leave it and ignore the error, but, now I'm perplexed and I want to know what's up with this.

I've checked my references for a sample db and they are a follows... Try adding in the Microsoft Office 16.0 Access databases engine Object Library in lieu of the DAO reference... In my Intellisense when dimming a recordset I now notice a Recordset and Recordset2.

You should be able to discover whether Recordset2 is available by going into Object Browser and searching for that name. It will tell you which libraries actually use that kind of object, so that way you know what kind of reference to set.

Thank you! I made the changes to my references first, and I am now seeing Recordset2 displayed in Intellisense as well as the Object Browser. The latter shows that this is part of the DAO library.

The REAL resolution in such cases is to always quantify which library you wanted. For example, where you have both DAO and ADO recordsets to consider, ALWAYS create the recordsets with the proper prefix.

I'm sorry, but I'm afraid I don't understand any of this.

To begin with -- what is the difference between DAO and ADO? Why are there two different types? Some of you keep referring to "ACE" - is that the same thing as ADO? Most of the Recordset objects I see in the online docs are listed as members of the DAO library, which is why I thought I needed to use that type. Which is the default for Access? How could I determine if a Form's Recordset property refers to a DAO or an ADO Recordset, and, can I change that if I want to?

And this really doesn't clear up the broader question of why the Form's Recordset property is type Recordset2 in the first place. There is no MVF in the table that this Form is bound to. (There is such a field in a different table in the database, however.) What I am attempting to do is exceedingly simple -- find a record in a Recordset based on the value of one of the fields, and make that record current. I must be overlooking something obvious; it couldn't really be this difficult.

If all else fails, there's the old decompile, compact and repair. The equivalent of IT help desk saying, turn your computer off and on and that should fix things.

...decompile?

According to this article, they are exactly the same except Recordset2 has a [ParentRecordset] property to handle MVFs.

Correct. The fields of a Recordset2 are type Field2. If a field is a multi-valued field, then its IsComplex property is true, and the Value of that field is itself a Recordset of type Recordset2.

Thanks, theDBguy. Yet ANOTHER reason to not like multi-valued fields because of what they force on people. An idea whose time has come - and gone.

«smiles» This is a topic that I want to talk more about. It's one which seems to elicit... strong emotional attachments, on either side. Is there an ongoing chat thread on this? If not, may I start one?

 

[essaytee]

Good evening!

...decompile?

This link explains it better than I can. Consider it a sister function to the Compact and Repair routine where it attempts to fix corrupted VBA code, if present.

 

[isladogs]

This is a topic that I want to talk more about. It's one which seems to elicit... strong emotional attachments, on either side. Is there an ongoing chat thread on this? If not, may I start one?

Have a look at this article to understand why almost all experienced developers avoid the use of MVFs: http://www.mendipdatasystems.co.uk/multivalued-fields/4594468763

 

[essaytee]

Just out of curiosity, and to recap so far:
Post #1

  Me.Recordset.FindFirst ("[Name] = ""Raven"" ")

Post #3 Advised to change to the following

Me.Recordset.FindFirst "[Name] = ""Raven"" "

There is a slight error in the Post #3 syntax, it should be:

Me.Recordset.FindFirst "[Name] = " & " " "Raven" & " " "

Please note, I spaced out the quotes for clarity and readability.
Another way, as I always get confused with quoting quotes:

Me.Recordset.FindFirst "[Name] = " & chr(34) & "Raven" & chr(34)

To test the last, from my immediate window, the result as follows:

? "[Name] = " & chr(34) & "Raven" & chr(34)
[Name] = "Raven"

I believe the original question posed was that the code was failing on the FindFirst line.

 

[The_Doc_Man]

The REAL resolution in such cases is to always quantify which library you wanted. For example, where you have both DAO and ADO recordsets to consider, ALWAYS create the recordsets with the proper prefix.

You said you did not understand this. OK, here is an example.

Dim RS1 as DAO.Recordset
Dim RS2 as ADO.Recordset
Dim RS3 as Recordset

The thing that you need to do when there is a possible conflict is to understand how Access resolves references in your code. Look at my example and ask yourself which type of Recordset is represented for each of those three variables.

RS1 can only be a DAO Recordset. If you didn't have a DAO library referenced, you would get an error. That is because this is a reference that has been "qualified" by a DAO prefix. The prefix "qualifies" which possible reference you meant.

RS2 can only be an ADO Recordset. If you didn't have an ADO library referenced, you would get an error. This is because it has been "qualified" by a reference to ADO.

But what is RS3? It is an example of an "unqualified" reference. It has no prefix.

For RS3, the result of that reference to Recordset depends on two factors. First, which libraries have you referenced, and second, in which order. If EITHER RS1 or RS2 pops an error (but not both) then RS3 will be the type of recordset that DIDN'T pop an error for the previous declarations. But if BOTH RS1 and RS2 resolve without error, then RS3 will resolve to the one from the appropriate library that is earliest in the list of references.

If you use the VB page's Tools>>References path to get to that complex references dialog list, you will see the window with a list of references that are checked followed by the list of unchecked references. The checked references appear in an order that is more or less the order in which you originally checked them (but the ones nearest the top might have been automatically checked by Access itself.) The unchecked references are more or less alphabetic in order.

But there is also a pair of arrows - one UP and one DOWN - that you use to re-order the checked references. You can highlight a checked reference in that list and then use the arrows to move it up or down in that list. In essence, you assign a priority to how that list is referenced. And there, the rule is "earliest match wins."