Help with row movement/filtering, and function calls

[Rob]

Hi all,

With help from Klatuu, I was able to refine some code that I have previously
written to remove a filter and move to a specific record. I then needed to check
the status of the record before allowing the move to the new record. I did this
using a function (see below). The problem I am now having is that the filter is
being removed, but I remain at the first record. I have confirmed that the
problem is in the CheckTaskMovement area as when I comment it out is works as
expected. Any help would be greatly appreciated,

TIA and Cheers

Rob
--------------------------------
txtGotoTaskID After Update
' If the task exists remove filter and move to the record
If Not IsNull(ELookup("[taskID]", Me.RecordSource, "[taskID] = " &
Me!txtGotoTaskID)) Then
If CheckTaskMovement Then
Me.FilterOn = False
Me.Filter = vbNullString
With Me.RecordsetClone
.FindFirst "[TaskID] = " & Me!txtGotoTaskID
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With
Else
Me!txtGotoTaskID.Value = Me![taskID]
End If

---------------------------------
Private Function CheckTaskMovement() As Boolean
On Error GoTo CheckTaskMovement_ErrorHandler

Dim bRetValue As Boolean
bRetValue = False

If Me.Dirty Then
Select Case MsgBox(strSave, vbQuestion + vbYesNoCancel, "Save Task?")
Case vbYes
Me.Dirty = False
bRetValue = True
Case vbNo
Me.Undo
bRetValue = True
Case vbCancel
bRetValue = False
End Select
Else
bRetValue = True
End If

CheckTaskMovement_ExitHandler:
CheckTaskMovement = bRetValue
Exit Function

CheckTaskMovement_ErrorHandler:
ErrMsg Me, "CheckTaskMovement", err
Resume CheckTaskMovement_ExitHandler

End Function

 

[Graham Mandeno]

Hi Rob

I assume txtGotoTaskID is unbound, right?

Do you, by any chance, have code in your Form_Current event procedure that
says:
Me!txtGotoTaskID = Me!TaskID
?

If so, then what is happening is this:
Me.FilterOn = False is removing the filter, which requeries the form,
setting the current record to the first in the RecordSource. The
Form_Current code runs, which changes txtGotoTaskID to now have the first
TaskID, forgetting the one you actually want to go to. The .FindFirst then
goes to the record that currently has the focus - the first one.

To fix it, you need to have a variable in your code to "remember" the
desired TaskID:

Dim lngGotoTaskID as Long
lngGotoTaskID = txtGotoTaskID
...
.FindFirst "[TaskID] = " & lngGotoTaskID

 

[Rob T]

Hi Graham,

Thanks for your reply. Your response was spot on (in all aspects) and the
problem is now resolved. FWIW I was close as I was saving the value to a
module level variable, however I was referring to it in the wrong part of my
code. I had it in the OnCurrent Event of the form as per the code below (I
had previously experimented with OpenArgs as well).

If lngGotoTaskID <> 0 Then
With Me.RecordsetClone
.FindFirst "[taskID] = " & lngGotoTaskID
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With
lngGotoTaskID = 0
End If

Thanks heaps. As always the responses on this NG are timely, well written
and from my part always appreciated,

Cheers
Rob