Recordset2 user-defined type errors (1 Viewer)

[Aeristan]

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

Advised to change to the following

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

Access will accept either syntax and the error occurs either way.

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

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

That is the result that I get as well.

Maybe this will shed more light. Each one of my event handlers contains a Debug.Print statement, to help me manage the flow of the code. Here is what I see in my Immediate window when I enter Form view:

=== BEGIN AccessMUD ==================================================
    subform_INVENTORY: form Open event      context: subform_INVENTORY

    subform_INVENTORY: form Current event   ( Item.Name = Periwinkle Princess Dress, Item.ID = 1 )  context: subform_INVENTORY

form_CHARACTER: form Open event   ( Character = 4 "Sarjenka" )
    TypeName(Me.Recordset) = Recordset2     Me.Recordset.Name = CHARACTER
    calling Me.Recordset.FindFirst ( Name = "Raven" ) from context: form_CHARACTER

form_CHARACTER: form Load event   ( Character = 5 "Raven" )

form_CHARACTER: form Current event   ( Character = 5 "Raven" )

    subform_INVENTORY: form Current event   ( Item.Name = Panther-hide armor, Item.ID = 3 )  context: subform_INVENTORY

form_CHARACTER: combo_ID Change event   ( Character = 5 'Raven' )   Now = 4/20/2019 12:47:23 PM
    TypeName(Me.Recordset) = Recordset2     Me.Recordset.Name = CHARACTER
    calling Me.Recordset.FindFirst ( [ID] = 4 ) from context: form_CHARACTER

form_CHARACTER: form Current event   ( Character = 4 "Sarjenka" )

    subform_INVENTORY: form Current event   ( Item.Name = Periwinkle Princess Dress, Item.ID = 1 )  context: subform_INVENTORY
--> Error in Sub combo_ID_Change: Method 'FindFirst' of object 'Recordset2' failed (number -2147417848)
    TypeName(Me.Recordset) = Recordset2     param = "[ID] = 4"

So what you are seeing happen is this: the Form_Open event is called, the FindFirst method is invoked, the error in question does not occur, and the desired record is displayed. Then, I use the combo_ID control, which also uses the FindFirst method (except that it finds based on ID rather than Name). This time, the error does appear.

But it doesn't always happen like this. Sometimes the error occurs immediately in the Form_Open event, and other times, I can navigate records several times before it fires.

Yet even when the error does occur, the FindFirst method apparently still does its thing, because the new record is displayed on the Form afterwards. With an error handler that ignores it, the only way I would know it was going on would be the Immediate window.

 

[Aeristan]

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

Right, I understand that much so far, but...

1. What is the default Recordset type created by Access when a Recordset is opened through a bound Form?
2. What exactly is the difference between DAO and ADO anyways?
3. It kinda seems like the Microsoft Office 16.0 Access database engine Object Library allows for both ADO and DAO type Recordsets. Am I reading that right?

Here are my current references, in order:

• Visual Basic for Applications
• Microsoft Access 16.0 Object Library
• OLE Automation
• Microsoft Office 16.0 Access database engine Object Library

(( I haven't tried the decompile option yet. I plan to do this next but I have to go adult for a couple hours... ))

 

[isladogs]

@essaytee

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.

….

The post #3 syntax is correct. Both of the additional " & " as added by Access are also valid but superfluous

Personally, I prefer using single quotes and omitting unnecessary [] as I think the sql is more legible

Me.Recordset.FindFirst "Name = 'Raven'"

@Aeristan
I would recommend you move your recordset code from the Form_Open event to the Form_Load event which follows that.

 

[Aeristan]

I would recommend you move your recordset code from the Form_Open event to the Form_Load event which follows that.

I have found that, when the Recordset.FindFirst method is used in the Form_Load event, the Form_Current event fires twice. When used in Form_Open, it fires only once.

And also I want to know why it is that you would recommend that.

 

[isladogs]

I have found that, when the Recordset.FindFirst method is used in the Form_Load event, the Form_Current event fires twice. When used in Form_Open, it fires only once.

And also I want to know why it is that you would recommend that.

I thought you had to

go adult for a couple of hours

Not that I understand that

Form_current will trigger when something changes. Perhaps move all the form current code into form load as well.
The form is still loading during the form open event. Code that is run during that event can be unreliable and work erratically which is exactly what you described s happening.

Did you read my article on MVFs? See link in post #18

 

[The_Doc_Man]

1. What is the default Recordset type created by Access when a Recordset is opened through a bound Form?

MS Access usually tries DAO first because "CurrentDB" is a DAO database. Modern Access using the default list of references will have DAO included in the library.

2. What exactly is the difference between DAO and ADO anyways?

I found a few articles that might help.

First one is a thread from Microsoft itself which includes a discussion of how "traditional" DAO is being deprecated but a NEW version of DAO is in the library mentioned earlier in the thread that referenced the Database Engine library. For the record, I learned something from that article because the version of Access I use most didn't require me to diddle the DAO references. They just worked. But older versions of DAO had to be explicitly referenced. I had missed the fact that DAO got moved into the engine's library.

https://answers.microsoft.com/en-us...r-access/41bdd1fc-924e-4aad-984a-cc2f766179ef

This is an older article that defines DAO but then says it is being deprecated. However, the article is old because in 2003, that was true - but isn't true any more. I think what happened is that for a while, Microsoft diddled with the idea of Active Data Pages where ADO was to be used, but it didn't work so well. See my earlier comments stating that DAO has been replaced but has not gone obsolete.

https://searchsqlserver.techtarget.com/definition/DAO

Here is a discussion of ADO (a.k.a. ADODB). The article mentions that ADO is often more useful in a web-based HTML environment.

https://searchsqlserver.techtarget.com/definition/ActiveX-Data-Objects

3. It kinda seems like the Microsoft Office 16.0 Access database engine Object Library allows for both ADO and DAO type Recordsets. Am I reading that right?

I believe that the ADO recordsets require explicit ADO library references to be added. In the first linked article under your 2nd question, there is a discussion on that topic as a sub-theme in the thread. DAO is subsumed into the Database Engine library and is now the default method for ACE database interaction. At this time, ADO is being either implicitly or explicitly deprecated. However, the database ENGINE will still respond to an ADO recordset if you used one.

To be clear, the ENGINE allows either reference. The LIBRARY only supports DAO.

 

[The_Doc_Man]

I have found that, when the Recordset.FindFirst method is used in the Form_Load event, the Form_Current event fires twice. When used in Form_Open, it fires only once.

The correct order of event firing is: Form_Open, Form_Load, Form_Resize, Form_Activate, Form_Current.

https://support.office.com/en-ie/ar...-objects-e76fbbfe-6180-4a52-8787-ce86553682f9

IF you fire Form_Current twice, what is ACTUALLY happening is that in the Form_Open routine, which can be cancelled, Access hasn't yet queued up the other events. (It is easier to wait and see what you do.) If you return from Form_Open without cancelling the Open, the other events get queued up as part of the event's return process. I.e. when you get past Form_Open without returning the Cancel flag, you are GOING to open that form whether you wanted to do so or not.

So... when you trigger the FindFirst from the _Open, there is nothing on the event queue (yet). This works because Access has opened the Form (as an object) and has dealt with the Form properties of the object. Since the .Recordsource is a form property, it has been handled during the _Open event. However, Access has NOT yet loaded up the collection of controls on that form. (That is what Form_Load is for.)

When you trigger the FindFirst from the _Load event, those other four events (_Load, _Resize, _Activate, _Current) are already on the event queue and cannot be canceled. Those events WILL happen and nothing will intervene. In fact, you are running from the event code for _Load at the time. Note also that most users do not diddle with the _Resize and _Activate events, but they DO fire.

Suppose you issue the FindFirst from the _Load routine. What happens is ANOTHER _Current occurs because you have changed the record to where the form will be open. BUT an event CANNOT interrupt another event, so that new _Current has to be queued for later execution, not executed (because you are IN an event routine at the time.)

Access knows you have diddled the current record so queues up another _Current event. It does so because this 2nd _Current event is the exact same event you would experience using the form's navigation buttons at the bottom of that form. That kind of navigation doesn't involve the other events - but it DOES require a revisitation of the bound controls on the form (and that is what the _Current event REALLY means). A new record has become current.

I do not believe the _Resize or _Activate events occur after navigation and I KNOW that the _Open and _Load events do NOT occur in that case. But if someone told me that the _Activate event fired, I wouldn't be shocked.

 

[essaytee]

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

Apologies everyone if I side-tracked the issue somewhat. I misread the visuals AND didn't test it.

Is there an emoji thingie for tail between legs and moving on?

 

[Aeristan]

...decompile?

This link explains it better than I can.

Well look at that. I did exactly this and the inexplicable error went away. THANK YOU! I have never heard of anything like this before and I certainly would not have found out about it, if you hadn't shown this to me.

So I guess this Problem is officially resolved! But I do have one last question: why would Access not re-compile all of the code every time it is changed? All other programming languages I worked with in the past did this automatically.

 

[Micron]

There is a compile button in the editor. Does it completely decompile and compile every time you use it? I don't know for sure, but I do know that the code you see isn't guaranteed to be what's in the compile version, which of course you cannot see. The only way to be 99.9879% sure it is is to use the decompile switch, but it is not recommended to do that for no good reason. It's something that should be reserved for only when there are issues you can't otherwise seem to resolve. It would even be good planning to ensure you have an up to date backup before doing so as I've read that decompiling can backfire, although it's never happened to me. Then again I've rarely needed to use it.

 

[The_Doc_Man]

why would Access not re-compile all of the code every time it is changed?

You know, that's a damned good question. From the way I understand it, Access "knows" when a module has been changed and will save itself time by only compiling what has been changed. Since it is actually a pseudo-compilation, it is hard to say what is stored in the DB. There is no Access internals reference I could find that does not involve the forbidden method of reverse engineering. However, I believe that if the date/time on the module source is newer than the time tag on the compiled seqment, you automatically compile when you try to run the app. The question is, for pseudo-code, what does DECOMPILE actually mean. I think it means just get rid of the last round of compilation.

I wonder if the problem is that if you somehow corrupt the compiled code but the date of that code blob (binary large object) still looks like it is newer than the source, does the compile action not actually do anything? (Which if so, would be why DECOMPILE is necessary.) Since Microsoft does not publish anything about its internals, this is speculative at best, but it fits known behavior.