Which Form Event would be generating the "No current record" error?

[mdlueck]

I have a search form, which itself is a multiple items form. I have a search field - unbound field - which is used to enter a part number to search the DB for, and the result set is returned to the same form in the multiple items area.

I have recently adjusted the forms to not display the new record row, so when the form initially opens it has absolutely no records to display. (Rightfully so.)

I have started receiving random "No current record" popup errors.

I suspect that some event is firing which I have no VBA code wired to, thus my custom error handler does not handle it and the default error message comes up.

I came across this post ( http://www.utteraccess.com/forum/Cu...1.html&pid=1942596&mode=threaded#entry1942596 ), which does successfully lead to my masking the error with the following code:

Private Sub Form_Error(DataErr As Integer, Response As Integer)

  Select Case DataErr
    Case 3021
      '3021 is a "No current record" error
      Response = acDataErrContinue
    Case Else
      Response = acDataErrDisplay
  End Select

End Sub

1) Is the default behavior for Access / VBA to consider firing certain events when certain things happens, and if no VBA code is wrired to the event then obviously it has no code to run for that event?

If so, then any guesses what event I should write code to so I do not have to wire this code to the Form_Error event?

2) Is there some sort of event watcher that I have not come across yet in Access / VBA that would give a port-hole view into what events are firing when even if they have no code wired to them? Sort of like a SysInternals ProcessMonitor for Access / VBA.

 

[Rx_]

There is the Call Stack that might cause something like this.
I have a really bad network lag at my location, I mean really bad network lag.
Hope that someone has a tool to recommend.
Wish there was something like your describing too.

 

[mdlueck]

There is the Call Stack that might cause something like this.

Call stack meaning I could find out what event was processing when the error occurred / was raised? Please elaborate further.

 

[gemma-the-husky]

I presume it is something in your open or load events.

if you have no records, the current even won't fire.

Can you not trace the execution from the load event?

 

[mdlueck]

No, the form is fully open and I am torture testing it.

If I enter a not found search string, and search for it, that results in a totally empty result set. Somehow just clicking around on a form in such a state is firing an event I have no code attached to, and that results in the error 3021 "No current record".

I would prefer to handle the error in the event it really comes from and not have to handle it in the generic Form_Error() event. I am just having trouble finding the event that actually generates the error in the first place.

 

[mdlueck]

Very odd... I added to the Form_Error event:

Debug.Print Me.ActiveControl

and I am getting an error message that ActiveControl is not set / raising yet a further error.

That would lead me to believe that the code is off in some other Module code rather than code on the Form itself. Correct? Or is there ever times that no Form control is set as ActiveControl yet the code execution is still in the context of the Form? (And trouble is, all Module Functions / Subroutines have error handling of their own! :banghead:

 

[mdlueck]

Well, seems the control name was hiding here:

Screen.PreviousControl.Name

Test code now as follows:

Private Sub Form_Error(DataErr As Integer, Response As Integer)

Debug.Print DataErr & " - " & [COLOR=Blue][B]Screen.PreviousControl.Name[/B][/COLOR]

  Select Case DataErr
    Case 3021
      '3021 is a "No current record" error
      Response = acDataErrContinue
    Case Else
      Response = acDataErrDisplay
  End Select

End Sub

Immediate window output cleaking various controls after a search which returned an empty result set:

3021 - fldqtyper
3021 - fldeau
3021 - fldqtyper
3021 - fldPartNumberSearch
3021 - fldeau
3021 - fldPartNumberSearch
3021 - fldqtyper
3021 - fldPartNumberSearch
3021 - fldeau
3021 - fldPartNumberSearch
3021 - fldeau

And here is the form in the state that is producing these 3021 errors as I click around the form:

Any ideas why a 3021 would occur just clicking between unbound field controls? And what Form event is firing that would cause these 3021 errors?

The Form's data source is a DAO.QueryDef querying an FE temp table. I would think that queries are able to return an empty result set. Perhaps the Form does not like being told to display "empty"? Why would the Form keep realizing that it has "empty" to display? Maybe perhaps as the form is bound to the DAO.QueryDef object, so clicking controls is having the form check "do I have data yet? No! Then 3021"

 

[Rx_]

The Call stack is worth looking at if the code can be halted. Just to see what process(s) are in place. My gripe about the call stack is that it doesn't have conditions to halt so we mere humans can view it at its worse case. So, we have to basically Run To a point in code.
Way back in the Access 2.0 training days, we were told, and were able to demonstrate the Stack doesn't always stack (and unstack) in the order we expect. So, an event during a challenging torque (or slow network response, sendkey, processor peak) might be un-stacking in the wrong order. Have often wonders how quad-core processing affects that anyway?

I am sure you are no stranger to DoEvents. Wouldn't be surprised if you already tried it.

On reading the new the Access 2010 Help fo rDoEvents, it offers a new Caution about this function that I see was not in Access 2007.
The Access 2007 Help
The Access 2010 Help ' new Caution about DoEvents
When placing Debug.Print "doevents " & DoEvents in my code - it returns a 0

' Create a variable to hold number of Visual Basic forms loaded 
' and visible.
Dim I, OpenForms
For I = 1 To 150000    ' Start loop.
    If I Mod 1000 = 0 Then     ' If loop has repeated 1000 times.
        OpenForms = DoEvents    ' in VB6 - returns number of open forms
         DoEvents  ' in VBA always returns 0 
    End If
Next I    ' Increment loop counter.

http://www.fmsinc.com/MicrosoftAccess/modules/examples/AvoidDoEvents.asp
This link was something new for me. Look at the Sleep Command in conjunction with a DoEvents

When I develop from my desktop - the lag for linked tables to SQL Server two states away can generate an error. For example, if I click on a list box item too fast. The Form update generates an error.
That error never happens on my Citrix server. Either the tiny network delay of running in the same rackmount or the increased processing power prevents that.
Sometimes, just add a pinch of DoEvents - and the problem seems to go away.

http://access.mvps.org/access/api/api0021.htm
Here is the API call for sleep

Does this fit your situation?
http://bytes.com/topic/access/answers/942835-does-vba-complete-tasks-asynchronously
Scroll down to the first post by ZMBD
No, this isn't a case of someone who can belabor the "old days" more than me; although he is a close 2nd in case someone is keeping track.
It describes how Asynchronously might cause a problem at runtime that is not a problem to step through. For those of us who work with Pass-Through Queries and Excel Object for reports - it might justify my use of DoEvents.

Myself, I probably abuse the DoEvents like a greasy spoon cafe's salt shaker. Sprinkle plenty on everything before even tasting it.
Have been planning on cutting back... someday.

 

[Rx_]

Did you happen to copy this form and modify it?
In Access 2007, this kind of thing happened to me.
Could not track it down. Compact Repair had no effect.
Migrated everything into a new blank DB and the problem was gone.
Don't know if it was the PC, network or what. It happened several more times over several months. But, it always seemed to be a copied form.
Got an older, slower PC with a clean install and never had that happen again.

Let us know what you find. These kind of things can be very ... stressful. My bet is that you will figure it out.

 

[gemma-the-husky]

could it be that the search retrieves no matches, and you then get a problem by assigning the null set to the form?

 

[mdlueck]

could it be that the search retrieves no matches, and you then get a problem by assigning the null set to the form?

This is a bound form.

FE Temp Table <--- DAO.QueryDef <--- Form's Data Source

So yes, the form needs to be able handling showing no results if a search came back blank. But it is not VBA code that will be assigning anything, this is a form bound to the FE temp table, as portrayed above.

This afternoon I tried to see if there is a way to turn off / deactivate the data connection if the result set should come back empty. I can query the FE temp table easy enough and safely determine if it is empty. I could not find a way to turn off / deactivte the data connection to the table. Perhaps I am remembering that in Paradox for Windows it was possible to activate / deactivate the connection between a form and the table? Or did I just not see it this afternoon. I develop on A2007. TIA!

 

[gemma-the-husky]

yeah, i wondered if in the code after you requeried the data set, you run some code. if you had an empty recordset, then you would have no current record

 

[mdlueck]

Perhaps I am remembering that in Paradox for Windows it was possible to activate / deactivate the connection between a form and the table?

No, I was remembering the grandchild of Paradox for Windows... Lazarus. http://www.lazarus.freepascal.org/ That has capability to activate / deactivate the connection even at run time, and in dev time you can see live data while you build the app.

Is there anyway to do this sort of thing with Access bound forms? I was thinking when the result set came back empty from the SQL BE DB, then simply deactivate the connection between FE temp table and the form. (On the search button, activate the connection, perform another search which gets a result set, and then leave the connection up / no result set, then deactivate the connection.)

I am concerned that I need to sprinkle the Error 3021 in every multiple items bound form.

Anyway, thank you Rx_ for the trace info. I shall dig into that this morning.

 

[mdlueck]

The Call stack is worth looking at if the code can be halted. Just to see what process(s) are in place.

Since I do not know what event is raising the error, does it work to only operate with "view call stack code" in the Form_Error() event? The references I found on the Internet about such involve code in each event you desire to have include call stack info. So that is a problem since I can not tell what event is firing / causing the error. Perhaps you would be so kind as to copy/paste a suggestion which would work when just placed into the Form_Error() event?

Interesting warning in A2010 about DoEvents. Seems like MS should have provided protection for DoEvents with a Mutex Semaphore.

Myself, I probably abuse the DoEvents like a greasy spoon cafe's salt shaker. Sprinkle plenty on everything before even tasting it.
Have been planning on cutting back... someday.

And I found it necessary to stick this in all of the buttons which are the Cancel-enabled buttons...

  'Flush keystrokes out of the buffer so that close via Esc does not generate an error msg
  DoEvents
  DoEvents
  DoEvents

One call hardly had no benefit, two calls was better, third call eliminates all nonsense. Go figure!

 

[mdlueck]

Since I do not know what event is raising the error, does it work to only operate with "view call stack code" in the Form_Error() event? The references I found on the Internet about such involve code in each event you desire to have include call stack info. So that is a problem since I can not tell what event is firing / causing the error. Perhaps you would be so kind as to copy/paste a suggestion which would work when just placed into the Form_Error() event?

Solved, I came across a reference to pressing Ctrl-L in the VBA screen to view the Call Stack. I was able to reporduce the error quickly, shown below:

I suppose I should simply sprinkle this Form_Error() event code (Minus the Debug.Print LOC) on all of the other Multiple Items bound forms in the application. Thoughts?

 

[HairyArse]

I've been getting this error a lot on bound forms when I save the final currently live record. This seems to have fixed it for me. Now if only I could solve my constant SQL write conflict error messages.

 

[gemma-the-husky]

I've been getting this error a lot on bound forms when I save the final currently live record. This seems to have fixed it for me. Now if only I could solve my constant SQL write conflict error messages.

do you mean "another user has edited the record"?

if so, it could well be you, having the same record active in multiple forms. Save the record in one form before opening the second form.