Solved

Cursor Moves to First Record on Datasheet Subform after Click Event Routine Runs

Posted on 2006-07-01
21
718 Views
Last Modified: 2012-05-05
I've go a main form for my contacts that shows all the contact fields.  And I have a subform, datasheet view, that shows all contacts.  I have a click event in the contact's name that calls a function on the main form so the main form moves to that record.  During this event, the subform cursor always moves to the first record.

I should state that this behavior only started recently after adding some other code and functions to the form.  I've moved the other functions, deleted them, etc. and the behavior hasn't quit.  When I first enter the form, everything is OK, but at some point, something changes.  I've checked the cycle propery on my subform and it is set to 0 all the time, even during the bad behavior (all records?).  No errors are being raised.  Here's the code:


'This is a click event in the company name on the subform

Private Sub txtCompany_Click()
If Me.NewRecord = False Then
strBusinessPhone = Me.txtBusinessPhone
Forms("frmContacts2").fncGoToID (strBusinessPhone)
End If
End Sub





'This function is on my main form and gets called by the above click event

Public Function fncGoToID(strBusinessPhone As String)
If strBusinessPhone = Me.txtBusinessPhone Then Exit Function
Set rst = Me.RecordsetClone
rst.FindFirst "[BusinessPhone] = '" & strBusinessPhone & "'"
If Not rst.EOF Then

'Here is where the move on the subform occurs

Me.Bookmark = rst.Bookmark
End If
Set rst = Nothing

End Function
0
Comment
Question by:HKComputer
  • 9
  • 6
  • 4
  • +1
21 Comments
 
LVL 84
Comment Utility
This is expected behavior - anytime you move a Parent record, the child (i.e. subform) records will requery and move back to the first record. You could, I suppose, write code which would "jump" you back to the original subform record, but without knowing more about your subform, and how it's related to the parent form (and how the data is related) it's kinda hard to advise you.
0
 
LVL 4

Author Comment

by:HKComputer
Comment Utility
You might be on to something.  But, these forms are not related.  I don't have the master/child properties set to anything.  The subform is a search form to find contacts.  The top form is the same recordsource all over again - contacts.  They both use basically the same SQL statements as their record source.

0
 
LVL 84
Comment Utility
Do you have any code in the subform's Current event?
0
 
LVL 4

Author Comment

by:HKComputer
Comment Utility
No, not the subform.  The main form yes.  And I've tried disabeling that code and the same behavior continues.  
0
 
LVL 8

Expert Comment

by:Emil_Gray
Comment Utility
You say that the main and subforms are not related but also say they have the same record source.

You might try code similar to the below if the main and subform are not synchronized. The code would go in the On_Current event on your subform.

If Me.ID <> Me.Parent.ID Then
' The forms aren't synchronized.

Me.Parent.RecordsetClone.FindFirst "ID = " & Me.ID
Me.Parent.Bookmark = Me.Parent.RecordsetClone.Bookmark
End If
0
 
LVL 4

Author Comment

by:HKComputer
Comment Utility
That might work.  The whole idea is that the user can scroll through all the contacts on the subform and then move the top half of the screen to that record by clicking inside one of the textboxes.  This all worked excellent until something ruined it today.  As near as I can tell, it's just a bizarre bug.  I have a copy of the database that is a couple days old and the behavior is fine.  I'm going to tryin to move all my changes in the "bad behaviour" database over to my older one and see at which point this bad behaviour starts, or hopefully it doesn't.
0
 
LVL 8

Expert Comment

by:Emil_Gray
Comment Utility
Oops, you want the subform syncronized with the main form. Gotta twist the code a little to do that. Try the following. The code would still go in the On_Current event on your subform.

If Me.ID <> Me.Parent.ID Then
' The forms aren't synchronized.

Me.RecordsetClone.FindFirst "ID = " & Me.Parent.ID
Me.Bookmark = Me.RecordsetClone.Bookmark
End If
0
 
LVL 4

Author Comment

by:HKComputer
Comment Utility
Emil_Gray,

I've now implemented your code and the same unexpected behavior continues.  Here is the click event I'm using on the subform in txtCompany.  BusinessPhone is the primary key(and don't ask why, I didn't do it).

Private Sub txtCompany_Click()
If Me.NewRecord = False Then
If Me!BusinessPhone <> Me.Parent!BusinessPhone Then
'****** The forms aren't synchronized.*********************8
Me.Parent.RecordsetClone.FindFirst "BusinessPhone = '" & Me!BusinessPhone & "'"
Me.Parent.Bookmark = Me.Parent.RecordsetClone.Bookmark
End If
End If
End Sub



This line of code is the offender:
>Me.Parent.Bookmark = Me.Parent.RecordsetClone.Bookmark

Is there any reason that line of code could run a requery or refresh?  
0
 
LVL 4

Author Comment

by:HKComputer
Comment Utility
I've narrowed down to the problem but I don't have a fix yet.  It is a very bizarre problem.

I have made great efforts on this form to give the user full search powers.  First I set the forms record source to an SQL statement that includes all Contact records.  When the user selects any of several radio buttons, it calls a function called "UpdateView" that puts a filter statement into a text box and then sets the filter to the value of that textbox and turns the filter on.  The filter filters according to the value of Search1.  Search1 is the name of a textbox, and only the name of a textbox.  I don't use that name anywhere else in the program.  After this code has been executed one time, the strange bookmark behavior starts and continues until I close the form.  When the form is first opened, optSearch is set to 0 and the bad behavior is NOT present.  No filter is turned on at all.


Function UpdateView()

Select Case optSearch
    Case 0 'View All Contacts
           'Uses default settings with no filter; no need to do anything
           Exit Function

    Case 1 'User configured custom filter
        If Nz(Me.txtFilter, "") = "" Then Exit Function
        'No need to do anything except set the filter to the value of Me.txtFilter which is done at the end of this function
   
    Case 2 'Company, FirstName, LastName
        'This is the line of code that seems to affect my bookmark.  I don't know why.
        Me.txtFilter = "[Company] Like Search1 or [LastName] Like Search1 or [FirstName] Like Search1"
       
End Select

Me.fsubContacts2.Form.Filter = Me.txtFilter
Me.fsubContacts2.Form.FilterOn = True

End Function




**/\**/\**/\**/\**/\**/\**/\**/\**/\**/\**/\**/\**/\**/\**/\**/\**/\**/\**/\**




Now, the bad behavior does not exist in this next chunk of sample code unless the user defines a custom filter.

Function UpdateView()

Dim strFilter as String

Select Case optSearch
    Case 0 'View All Contacts
           'Uses default settings with no filter; no need to do anything
           Exit Function

    Case 1 'User configured custom filter
        'A typical custom filter would look just like the text in the previous sample code        
        If Nz(Me.txtFilter, "") = "" Then Exit Function
        Me.fsubContacts2.Form.Filter = Me.txtFilter
        Me.fsubContacts2.Form.FilterOn = True
        Exit Function


    Case 2 'Company, FirstName, LastName
       'Notice that the way I build the filter here is different than I do for a user defined filter
        strFilter = "[Company] Like '" & Me.Search1 & "' or [LastName] Like '" & Me.Search1 & "' or [FirstName] Like '" & Me.Search1 & "'"
       
End Select

Me.fsubContacts2.Form.Filter = strFilter
Me.fsubContacts2.Form.FilterOn = True

End Function







So now my question is, how can I let users define custom filters without the bad behavior?  Is there a different or better way of referencing the value of Search1?


0
 
LVL 8

Expert Comment

by:Emil_Gray
Comment Utility
Whoo! The last code I provided was did not have include

Private Sub txtCompany_Click()
If Me.NewRecord = False Then
If Me!BusinessPhone <> Me.Parent!BusinessPhone Then
'****** The forms aren't synchronized.*********************8
Me.Parent.RecordsetClone.FindFirst "BusinessPhone = '" & Me!BusinessPhone & "'"
Me.Parent.Bookmark = Me.Parent.RecordsetClone.Bookmark
End If
End If
End Sub

The line in your code that says:

Me.Parent.Bookmark = Me.Parent.RecordsetClone.Bookmark

should say:

Me.Bookmark = Me.Parent.RecordsetClone.Bookmark

i.e.

Private Sub txtCompany_Click()
If Me.NewRecord = False Then
If Me!BusinessPhone <> Me.Parent!BusinessPhone Then
'****** The forms aren't synchronized.*********************8
Me.Parent.RecordsetClone.FindFirst "BusinessPhone = '" & Me!BusinessPhone & "'"
Me.Bookmark = Me.Parent.RecordsetClone.Bookmark
End If
End If
End Sub
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 8

Expert Comment

by:Emil_Gray
Comment Utility
The last obviously was extremely incoherant. My apologies. Should you choose to try the following you need not provide points. I frankly feel I have confused you enough.

Private Sub txtCompany_Click()
If Me.NewRecord = False Then
If Me!BusinessPhone <> Me.Parent!BusinessPhone Then
'****** The forms aren't synchronized.*********************8
Me.Parent.RecordsetClone.FindFirst "BusinessPhone = '" & Me!BusinessPhone & "'"
Me.Bookmark = Me.Parent.RecordsetClone.Bookmark
End If
End If
End Sub

There are nested If statemens which can be a bit confusing. To make it a bit plainer remember Me.Bookmark is the subform Bookmark and Me.Parent.Bookmark is the Main forms Bookmark.

I sincerely hope I haven't confused you.

Emil
0
 
LVL 4

Author Comment

by:HKComputer
Comment Utility
I think I'm finally catching on what your code is for, Emil_Gray.  The idea is that after my existing code runs, and resets the subform back to record #1 (even though it shouldn't), we'll run your code to resync the subform with the main form.  I'm now using code to do this and it works well, though I didn't choose exactly your code.  But I think yours would work for what I want.

This seems like kind of a bandaid for the problem but it does work.  Still wish I could figure out how to reference my search boxes in my custom filter so the bad behavior does not exist. -HK
0
 
LVL 8

Expert Comment

by:Emil_Gray
Comment Utility
Sometimes Access will do very strange things internally. Somes (not always) you can correct the problem by creating a blank database and importing all of the objects from the old database into the new one. That used to be a pain because then you would have to recreate your relationships all over again. Now Microsoft has this dandy little code that will do it for you. Here it is if you want to try.

Function ImportRelations(DbName As String) As Integer
'------------------------------------------------------------------
' PURPOSE: Imports relationships where table names and field names
'          match.
' ACCEPTS: The name of the external database as a string.
' RETURNS: The number of relationships imported as an integer.
'DbName in Function is the full path of the existing database
'i.e. C:\windows\desktop\nameofdatabase.mdb
'------------------------------------------------------------------

Dim ThisDb As DAO.Database, ThatDB As DAO.Database
Dim ThisRel As DAO.Relation, ThatRel As DAO.Relation
Dim ThisField As DAO.Field, ThatField As DAO.Field
Dim Cr As String, i As Integer, cnt As Integer, RCount As Integer
Dim j As Integer
Dim ErrBadField As Integer
Cr$ = Chr$(13)
RCount = 0

Set ThisDb = CurrentDb()
Set ThatDB = DBEngine.Workspaces(0).OpenDatabase(DbName$)

' Loop through all existing relationships in the external database.
For i = 0 To ThatDB.Relations.Count - 1
   Set ThatRel = ThatDB.Relations(i)

   ' Create 'ThisRel' using values from 'ThatRel'.
   Set ThisRel = ThisDb.CreateRelation(ThatRel.Name, _
      ThatRel.Table, ThatRel.ForeignTable, ThatRel.Attributes)

   ' Set bad field flag to false.
   ErrBadField = False

   ' Loop through all fields in that relation.
   For j = 0 To ThatRel.Fields.Count - 1
      Set ThatField = ThatRel.Fields(j)

      ' Create 'ThisField' using values from 'ThatField'.
      Set ThisField = ThisRel.CreateField(ThatField.Name)
      ThisField.ForeignName = ThatField.ForeignName

      ' Check for bad fields.
      On Error Resume Next
      ThisRel.Fields.Append ThisField
      If Err <> False Then ErrBadField = True
      On Error GoTo 0
   Next j

   ' If any field of this relationship caused an error,
   ' do not add this relationship.
   If ErrBadField = True Then
      ' Something went wrong with the fields.
      ' Do not do anything.
   Else
      ' Try to append the relation.
      On Error Resume Next
      ThisDb.Relations.Append ThisRel
      If Err <> False Then
         ' Something went wrong with the relationship.
         ' Skip it.
      Else
         ' Keep count of successful imports.
         RCount = RCount + 1
      End If
      On Error GoTo 0
   End If
Next i

' Close databases.
ThisDb.Close
ThatDB.Close

' Return number of successful imports.
ImportRelations = RCount

End Function

0
 
LVL 4

Author Comment

by:HKComputer
Comment Utility
I've simply concluded that this is a bug.  I don't know what else to say.

There are some "band-aid" solutions here that I'm currently using, but I'm still having troubles with it.

Another strange side effect is that when you click in a field on the datasheet view subform, the double click event runs in the that textbox, but on the topmost record.  In this case, it is somewhat catastrophic since my double click for some textboxes contains code to autodial phone numbers.

I'm going to request a close a refund for this one, unless there are any objections.

I sure do thank you, Emil_Gray, for trying so hard on this one.  -HK
0
 
LVL 4

Author Comment

by:HKComputer
Comment Utility
I'm still hoping for a solution to this problem.
I've made a demo database and a demo movie for this one.  Please download them and take a look.

http://www.hkcomputerservices.com/downloads/bugtestdb.mdb  (340K)

http://www.hkcomputerservices.com/downloads/accessbugdemo.zip  (11 Megabytes)
0
 
LVL 84
Comment Utility
It's default behaviour of Access - when you move OFF the subform, and back ON to the subform via VBA code, the first record is selected.

You're basically (a) filling in search params on the mainform, (b) clicking Filter on mainform which then "filters" your subform and then (c) moving to your subform and then (d) using VBA to move from subform to mainform and back ... this causes Access to refresh, and you cannot get around this behaviour. It's not a bug, it's well documented and expected behaviour (and has been since Access introduced subforms).

You're using subforms in a non-standard way - do users EVER need to update anything on the subform? If not, switch to a listbox and present the info there, on the Main form ... you can still manipulate data, and you can Requery the listbox and update the correct record.
0
 
LVL 4

Author Comment

by:HKComputer
Comment Utility
I don't mean to be knot-headed, but why does the (what I call odd) behavior start only after using form filters that refer to the textbox the way I show in the movie?

The company I'm developing this app for is very accustomed to using those datasheet views and a listbox would probably not be accepted.
0
 
LVL 84
Comment Utility
<<but why does the (what I call odd) behavior start only after using form filters that refer to the textbox the way I show in the movie?>>

I'm not sure ... the simplest solution is to do this unbound, thereby removing the automatic requery feature ... at least I'm assuming that would stop the behaviour

Mod: I have no objection to the close

Poster: Good luck, sorry we couldn't be more help ...
0
 
LVL 8

Expert Comment

by:Emil_Gray
Comment Utility
This is an interesting little problem. I have downloaded the bugtestdb and will look at it. Like I always say. It's a computer. It will do what you tell it to do, but sometimes you have to ask very nicely. Otherwise, it will be very obstinate and refuse your request. I have found this especially true when dealing with MS Access!
0
 
LVL 1

Accepted Solution

by:
DarthMod earned 0 total points
Comment Utility
Closed, 500 points refunded.
DarthMod
Community Support Moderator
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

763 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

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now