Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 332
  • Last Modified:

MsgBox statement changes results

I have a Form with a subform.  The subform is a continuous form.  Upon clicking the subform area, I highlight the selected row.  When a Sort option is selected, I want to keep the current line highlighted.  To accomplish this I use the following code:  (Access 2002 'adp'  SQL Server)

    Me.Form.InputParameters = "@XSortOrder nvarchar = '" & XSQLOrder & "'"
    Me.Form.Requery
    MsgBox strSave & " Before FIND"
    Me.Form.Recordset.Find "[EmpKey] = '" & strSave & "'"

If I leave the MsgBox line in the code, it works.  If I remove the line, it defaults to the 1st row displayed in the subform.
Why would the MsgBox change the results?    
0
inkineu
Asked:
inkineu
1 Solution
 
shanesuebsahakarnCommented:
Probably because it is giving the requery time to complete. Try putting a DoEvents command in there instead of the msgbox. Does that give the right result?
0
 
Steve BinkCommented:
It should not change the results at all.  Perhaps if you explain more about the process, or post more of the relevent code, we can find out why the behavior is occuring.
0
 
inkineuAuthor Commented:
I tried the DoEvents before the MsgBox line and it did not change anything.  Here's the code for the subform:

Private Sub Form_Current()
    Me.Parent.Form.ZCurrentRec = Me.EmpKey    Me.ctlCurrentRecord = Me.SelTop   ' causes Form refresh
    Call Parent.InitFields
    Me.Parent.Form.Controls("ZDesc").SetFocus
End Sub

Private Function fSortFld(pstrSortSeq As String, pstrSortChar As String, pstrSortFld As String)
    Call fSortCtl("F", Form, "", pstrSortSeq, pstrSortChar, pstrSortFld)
    Call SetPosn
End Function

Private Sub SetPosn()
    Dim strSave As String
    strSave = Me.Parent.Form.ZCurrentRec
    Me.Form.InputParameters = "@XSortOrder nvarchar = '" & XSQLOrder & "'"
    Me.Form.Requery
'    DoEvents                    .... added here
'    MsgBox strSave & " Before FIND"
    Me.Form.Recordset.Find "[EmpKey] = '" & strSave & "'"
    Me.Parent.Form.ZCurrentRec = Me.EmpKey
    Me.ctlCurrentRecord = Me.SelTop
End Sub
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
inkineuAuthor Commented:
if I add the following code it works (PauseTime = 1 does not):
    PauseTime = 2
    Start = Timer    
    Do While Timer < Start + PauseTime
        DoEvents    
    Loop

But, the elapsed time is not acceptable.  Any other ideas?
0
 
Steve BinkCommented:
Perhaps you can check the status of the form's recordset for a delay?

Do While Me.Recordset.StillExecuting
    DoEvents
Loop
0
 
inkineuAuthor Commented:
When adding the DoEvents code, I receive the following message:

Object doesn't support this property or method.
0
 
inkineuAuthor Commented:
How do you set the current record in a continuous form if the recordsource is a SQL stored procedure?  I found a site that suggested that ADO only returns 50 records at a time, so the Find command may not return a match.  What's the workaround?
0
 
Steve BinkCommented:
When a recordset is returned, Access gets the entire recordset.  The current record will be set in the normal way.  As far as ADO only returning 50 records at a time, that COULD be that it is sending only pages of information as it is needed.  You should be able to populate the entire recordset forcibly by navigating to the last record.
0
 
inkineuAuthor Commented:
Okay, maybe I'm looking in the wrong direction.  Could it be that the FIND command expects the key to be in Ascending sequence?  It seems that when the Recordset is sorted in Descending order that the FIND comes up with an EOF condition.  Still not sure why the Msgbox line causes it to work.
0
 
Steve BinkCommented:
While I don't think ordering alters the behavior of the .Find method (other than optimization from alpha-sorting), you should keep in mind that .Find begins from the current record.  If you want to search the entire recordset, you should use .MoveFirst or .Move 1 before attempting your Find.

I also do not know why the MsgBox causes it to work, but I'm of the same mind as Shane...something in the delay caused by the MsgBox.  Here's a rework of the code I posted earlier for a stateful delay.  My previous version was for a DAO recordset, which was the cause of the error:

Do While ((Me.Recordset.State AND adStillExecuting) = adStillExecuting)
    DoEvents
Loop

But that brings up yet ANOTHER question.  Form recordsets are DAO in Access.  I've never used an ADP, so perhaps they are converted to ADO in that scenario.  Have you verified the recordset to be part of the ADO class?
0
 
inkineuAuthor Commented:
I added the Do While Loop and it seems to cause some looping issues.  Sometimes for over 1 minute.  I am going to try the Filter method.
0
 
Steve BinkCommented:
>>> Sometimes for over 1 minute

That may be the source of your problem!  That loop should cause a delay only while the recordset is still executing (assuming it IS an ADO recordset).  If you think that loop is creating too long of a delay, perhaps you should take a look at connectivity or network issues.  
0
 
inkineuAuthor Commented:
The 'AND adStillExecuting' causes the loop.  I'm not aware of being able to use adStillExecuting in ADO when a recordsource is a stored procedure.  I've heard of using adStateExecuting for the connection, but I don't believe that would help in this case.  Anyway, I only have 450 records on file so am not sure why there is such a delay in processing.  I have tried the Filter method, and although it takes approx. 3 seconds before the screen displays, it does work, I will have to live with that, even though I find this Access programming very frustrating.  
0
 
Steve BinkCommented:
>>> The 'AND adStillExecuting' causes the loop

Yes it does.  That was the entire point of it.

>>> I'm not aware of being able to use adStillExecuting in ADO when a recordsource is a stored procedure

Checking the State property of a connection OR recordset is a valid method for determining if the operation is still ongoing.  Since the operation in this case was the refresh of the recordset, that is the state I tested.  Have you tried it on the connection at all?

I think the problem is either Access is losing something in the communication with the back-end, or you are have some erratic network issues.  It's hard to pin down.  But if you've found a method that works and you can live with it, you're good to go.  :)

Good luck with the rest of your project.
0
 
inkineuAuthor Commented:
How would I check the State of the connection when the Forms recordsource is a stored procedure?  
0
 
Steve BinkCommented:
Compare <connobject>.State, just as shown in the recordset example.

My initial recommendation was the recordset, because that is where the work is actually being done.  The form's recordset needs its source, so it calls the SP.  When it does, it sets it State to reflect the fact it is doing something.  Until the recordset receives the return, its (.State AND adStillExecuting) should equal adStillExecuting.  Since the recordset will not receive the return until the SP finishes, it reasonably follows that while the SP is running, the recordset should indicate this.

For the connection, that would be if you decided to execute an SQL statement (or perhaps a command object?) through the connection versus using a recordset.  In this case, the same reasoning applies, but in the context of the Connection object.  I don't think the Connection.State is going to show you anything, since you did not appear to initiate the work through the connection itself, but it never hurts to try and Access has been known to not work EXACTLY as advertised in the past.  It may be that any activity on the connection sets the State, but I doubt it.
0
 
inkineuAuthor Commented:
The Filter method is what I stuck with.
0
 
Steve BinkCommented:
Filter method?  Which filter method?  How did you resolve the issue of the delay vs. message box?

TheLearnedOne:  This is most definitely not a D/NR question.  inkineu responded to all of our requests for information, and unless something I said led to a solution, the question should be a PAQ/Refund.  If nothing else, the explanation of .State and its uses will be of benefit to other questioners.
0
 
moduloCommented:
PAQed with points refunded (125)

modulo
Community Support Moderator
0
 
inkineuAuthor Commented:
This is what I replaced the FIND with:

Set rs = Me.RecordsetClone
rs.Filter = "[EmpKey] = '" & strSave & "'"
0

Featured Post

Become an Android App Developer

Ready to kick start your career in 2018? Learn how to build an Android app in January’s Course of the Month and open the door to new opportunities.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now