Go to a record using value from a control (bookmark not working)

[tweetyangel00]

Can anyone tell me why the code below is not working? I am not getting any errors when I step through it. I have a button that removes all of the filters on the form & subforms and I want the current record to have the focus after the filters are removed. Based on the help files I looked at this should work.
Any help is greatly appreciated! Ty!
*******************************
Private Sub cmdRemoveFilter_Click()
'removes filter from form and subforms and returns you to your current record
'Groves 23-Feb-2011, 16-Mar-2011
On Error Resume Next
Dim sfrm As SubForm
Dim rst As DAO.Recordset
Dim strCriteria As String

'find record to bookmark
strCriteria = "[TextField] = '" & Me.TextField & "'"
Set rst = Me.RecordsetClone
rst.MoveFirst
rst.FindFirst strCriteria

'removes filter from main form whick moves focus to the first record in the form
DoCmd.ShowAllRecords

'removes filter from 1st subform
Set sfrm = Forms!frm_IssueTrackingData!subfrm_IssueTracking_Data_Studies_v2
With sfrm
sfrm.SetFocus
DoCmd.ShowAllRecords
End With

'removes filter from 2nd subform
Set sfrm = Forms!frm_IssueTrackingData!subfrm_IssueTrackingData_Lots_v2
With sfrm
sfrm.SetFocus
DoCmd.ShowAllRecords
End With

'return control to the main form and return to the bookmarked record
Me.TextField.SetFocus
DoCmd.GoToControl "TextField"
Me.Bookmark = rst.Bookmark
Set rst = Nothing

End Sub
*************************************************

 

[dhookom]

The recordsetclone isn't a clone if you mess with the recordsource after cloning. It's like having a line of 2nd graders at a school. You dibs your place in line between a couple kids and then change the line to all grades. Your place is then lost.

Duane
Hook'D on Access
MS Access MVP

 

[TheAceMan1]

How are ya tweetyangel00 . . .

Try this ([blue]you![/blue] substitute proper names/values in [purple]purple[/purple]):

[blue]   Dim sfrm1 As Form, sfrm2 As Form, hldID, Cri As String
   
   Set sfrm1 = [subfrm_IssueTracking_Data_Studies_v2].Form
   Set sfrm2 = [subfrm_IssueTrackingData_Lots_v2].Form
   
   hldID = Me.[purple][b]PrimaryKeyName[/b][/purple]
   
   [green]'Remove Filters[/green]
   Me.FilterOn = False
   sfrm1.FilterOn = False
   sfrm2.FilterOn = False
   
   [green]'Use the following line only if PrimaryKey is Numeric[/green]
   Cri = "[[purple][b]PrimaryKeyName[/b][/purple]] = " & hldID
   
   [green]'Use the following line only if PrimaryKey is Text[/green]
   Cri = "[[purple][b]PrimaryKeyName[/b][/purple]] = '" & hldID & "'"
   
   [green]'Goto Record on MainForm[/green]
   Me.Recordset.FindFirst Cri
   
   Set sfrm1 = Nothing
   Set sfrm2 = Nothing[/blue]

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[tweetyangel00]

That worked! Here is my working code.
btw. was there a reason you used two variables to set the
subforms? I still just used one and it worked. I'm not much
of a programmer though so if there is a reason please let
me know.
Thanks again!
Angela

*******************************************************
Private Sub cmdRemoveFilter_Click()
'removes filter from form and subforms and returns you to your current record
'Angela Groves 23-Feb-2011, 16-Mar-2011
On Error Resume Next
Dim sfrm As SubForm
Dim so As String
Dim strCriteria As String

'find record to hold study order number in current record
strCriteria = "[StudyOrderNumber] = '" & Me.StudyOrderNumber & "'"

'removes filter from main form whick moves focus to the first record in the form
DoCmd.ShowAllRecords
'removes filter from 1st subform
Set sfrm = Forms!frm_IssueTrackingData!subfrm_IssueTracking_Data_Studies_v2
With sfrm
sfrm.SetFocus
DoCmd.ShowAllRecords
End With
'removes filter from 2nd subform
Set sfrm = Forms!frm_IssueTrackingData!subfrm_IssueTrackingData_Lots_v2
With sfrm
sfrm.SetFocus
DoCmd.ShowAllRecords
End With
'return control to the main form and return to the held record
Me.StudyOrderNumber.SetFocus
Me.Recordset.FindFirst strCriteria

Set sfrm = Nothing 'Deassign all objects.
End Sub
*******************************************************

 

[TheAceMan1]

[blue]btw. was there a reason you used two variables to set the subforms?[/blue]

Indeed! Purely a matter of code readability! Where [blue]Long Names[/blue] are involved, I always assign a much shorter name. This is the advent of programming in VBA.

[blue][purple]Short Names[/purple] make for easier reading and detection of processes, logic flow and such.[/blue]

You should always design you code to be readily readable to you! Which means ... you need to get away from the [blue]long naming convention[/blue] your currently using. The idea is not to impress with lenght, but make it readible on sight ... at least for you ... as the programmer.

[blue]Try to imagine both subform names repeated 15 to 20 times or more in a routine and you'll see what I mean.[/blue]

Forget about impressing and make your code readible to you ... first and foremost!

Getting back to your code ... if you notice ... your code requires setting focus to the subform ... [blue]whereas mine doesn't![/blue]

The choice is yours. I only hope I've made my point!

[blue]Your Thoughts? . . .[/blue]


See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[tweetyangel00]

For me actually the long naming makes it easier for me to read/understand where I'm at in this database.
When I don't set the focus on the subform, even with seperate variables, it does not remove the filter for me.
Thank you for the tips. I really appreciate it!