Help with row movement/filtering

[Rob]

Hi all,

I have a problem that I was hoping that I might be able to get some help
with. I currently have a form that displays a list of tasks for my
organisation. This form has two combo boxes that act as filters on the
underlying data and allow a manager to look at tasks belonging to a
particular user and/or the status of tasks belonging to their section.
I also have a text box that allows a task number to be entered and we
move to that row. When the data is filtered, I can present the manager
with a "Task does not belong to your section" error due to the reduced
data set even though the task does exist.

I would like to let a manager search the entire recordset even if the
data is filtered. I have tried removing the filter in the text box
After_Update event, however code after the filter removal does not
execute. Should I query the entire recordset in VBA, and then if the
task exists remove the filters and move to the available task or is this
additional overhead given that I would have to make this call twice? For
example:

text box After_Update

Set rst = DBEngine(0)(0).OpenRecordset("qryTasksBySection")
With rst
.FindFirst "[taskID] = " & Me!txtGotoTaskID.Value
If Not .NoMatch Then
Me.RecordSource = "qryTasksBySection"
Me.Bookmark = .Bookmark
Else
MsgBox "Unable to find task", vbInformation + vbOKOnly
Me!txtGotoTaskID.Value = Me![taskID]
End If
End With
Set rst = Nothing

I have recently returned to doing some database development (he;ping out
at work) after a LONG lay off and as a result I am a bit rusty!

TIA

Cheers
Rob

P.S Access2003

 

[Klatuu]

If you are saying the Form's Filter and FilterOn properties are being set
based on the combo box values, then I would do this a bit differently.
Notice I use a DLookup to search for the task. Why take the time to create
and open a recordset when you are not going to use it?

If Not IsNull(DLookup("[TaskID]", "qryTasksBySection", "[TaskID] = "
& _
Me!txtGotoTaskId)) 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
MsgBox "Unable to find task", vbInformation + vbOKOnly
Me!txtGotoTaskID = Me![taskID]
End If

 

[Rob]

Thanks heaps Klatuu,

I felt that I was creating additional overhead by using a recordset - an
elegant solution to my problem!

Cheers
Rob