?
Solved

MsgBox statement changes results

Posted on 2005-03-18
22
Medium Priority
?
323 Views
Last Modified: 2012-08-13
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
Comment
Question by:inkineu
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
22 Comments
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 13578392
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
 
LVL 51

Expert Comment

by:Steve Bink
ID: 13578397
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
 
LVL 1

Author Comment

by:inkineu
ID: 13578698
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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
LVL 1

Author Comment

by:inkineu
ID: 13580808
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
 
LVL 51

Expert Comment

by:Steve Bink
ID: 13591763
Perhaps you can check the status of the form's recordset for a delay?

Do While Me.Recordset.StillExecuting
    DoEvents
Loop
0
 
LVL 1

Author Comment

by:inkineu
ID: 13632295
When adding the DoEvents code, I receive the following message:

Object doesn't support this property or method.
0
 
LVL 1

Author Comment

by:inkineu
ID: 13637420
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
 
LVL 51

Expert Comment

by:Steve Bink
ID: 13643842
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
 
LVL 1

Author Comment

by:inkineu
ID: 13645977
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
 
LVL 51

Expert Comment

by:Steve Bink
ID: 13646352
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
 
LVL 1

Author Comment

by:inkineu
ID: 13649583
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
 
LVL 51

Expert Comment

by:Steve Bink
ID: 13653540
>>> 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
 
LVL 1

Author Comment

by:inkineu
ID: 13654448
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
 
LVL 51

Expert Comment

by:Steve Bink
ID: 13654596
>>> 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
 
LVL 1

Author Comment

by:inkineu
ID: 13655312
How would I check the State of the connection when the Forms recordsource is a stored procedure?  
0
 
LVL 51

Expert Comment

by:Steve Bink
ID: 13655726
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
 
LVL 1

Author Comment

by:inkineu
ID: 14096342
The Filter method is what I stuck with.
0
 
LVL 51

Expert Comment

by:Steve Bink
ID: 14114058
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
 

Accepted Solution

by:
modulo earned 0 total points
ID: 14114210
PAQed with points refunded (125)

modulo
Community Support Moderator
0
 
LVL 1

Author Comment

by:inkineu
ID: 14141999
This is what I replaced the FIND with:

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

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Suggested Courses

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question