Cursor Moves to First Record on Datasheet Subform after Click Event Routine Runs
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").fncG
End If
End Sub
'This function is on my main form and gets called by the above click event
Public Function fncGoToID(strBusinessPhone
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
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").fncG
End If
End Sub
'This function is on my main form and gets called by the above click event
Public Function fncGoToID(strBusinessPhone
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
Scott McDaniel (EE MVE )
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.
ASKER
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.
Do you have any code in the subform's Current event?
ASKER
No, not the subform. The main form yes. And I've tried disabeling that code and the same behavior continues.
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.F
Me.Parent.Bookmark = Me.Parent.RecordsetClone.B
End If
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.F
Me.Parent.Bookmark = Me.Parent.RecordsetClone.B
End If
ASKER
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.
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.FindFirs
Me.Bookmark = Me.RecordsetClone.Bookmark
End If
If Me.ID <> Me.Parent.ID Then
' The forms aren't synchronized.
Me.RecordsetClone.FindFirs
Me.Bookmark = Me.RecordsetClone.Bookmark
End If
ASKER
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.*************
Me.Parent.RecordsetClone.F
Me.Parent.Bookmark = Me.Parent.RecordsetClone.B
End If
End If
End Sub
This line of code is the offender:
>Me.Parent.Bookmark = Me.Parent.RecordsetClone.B
Is there any reason that line of code could run a requery or refresh?
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.*************
Me.Parent.RecordsetClone.F
Me.Parent.Bookmark = Me.Parent.RecordsetClone.B
End If
End If
End Sub
This line of code is the offender:
>Me.Parent.Bookmark = Me.Parent.RecordsetClone.B
Is there any reason that line of code could run a requery or refresh?
ASKER
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.Filt
Me.fsubContacts2.Form.Filt
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.Filt
Me.fsubContacts2.Form.Filt
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.Filt
Me.fsubContacts2.Form.Filt
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?
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.Filt
Me.fsubContacts2.Form.Filt
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.Filt
Me.fsubContacts2.Form.Filt
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.Filt
Me.fsubContacts2.Form.Filt
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?
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.*************
Me.Parent.RecordsetClone.F
Me.Parent.Bookmark = Me.Parent.RecordsetClone.B
End If
End If
End Sub
The line in your code that says:
Me.Parent.Bookmark = Me.Parent.RecordsetClone.B
should say:
Me.Bookmark = Me.Parent.RecordsetClone.B
i.e.
Private Sub txtCompany_Click()
If Me.NewRecord = False Then
If Me!BusinessPhone <> Me.Parent!BusinessPhone Then
'****** The forms aren't synchronized.*************
Me.Parent.RecordsetClone.F
Me.Bookmark = Me.Parent.RecordsetClone.B
End If
End If
End Sub
Private Sub txtCompany_Click()
If Me.NewRecord = False Then
If Me!BusinessPhone <> Me.Parent!BusinessPhone Then
'****** The forms aren't synchronized.*************
Me.Parent.RecordsetClone.F
Me.Parent.Bookmark = Me.Parent.RecordsetClone.B
End If
End If
End Sub
The line in your code that says:
Me.Parent.Bookmark = Me.Parent.RecordsetClone.B
should say:
Me.Bookmark = Me.Parent.RecordsetClone.B
i.e.
Private Sub txtCompany_Click()
If Me.NewRecord = False Then
If Me!BusinessPhone <> Me.Parent!BusinessPhone Then
'****** The forms aren't synchronized.*************
Me.Parent.RecordsetClone.F
Me.Bookmark = Me.Parent.RecordsetClone.B
End If
End If
End Sub
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.*************
Me.Parent.RecordsetClone.F
Me.Bookmark = Me.Parent.RecordsetClone.B
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
Private Sub txtCompany_Click()
If Me.NewRecord = False Then
If Me!BusinessPhone <> Me.Parent!BusinessPhone Then
'****** The forms aren't synchronized.*************
Me.Parent.RecordsetClone.F
Me.Bookmark = Me.Parent.RecordsetClone.B
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
ASKER
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
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
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\nameofd
'-------------------------
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).Ope
' 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(That
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(ThatFi
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
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\nameofd
'-------------------------
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).Ope
' 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(That
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(ThatFi
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
ASKER
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
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
ASKER
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)
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)
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.
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.
ASKER
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.
The company I'm developing this app for is very accustomed to using those datasheet views and a listbox would probably not be accepted.
<<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 ...
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 ...
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!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.