Filtering Form based upon the Selected record of another form

[CharlieT302]

Hi All,

I have a main form that contains an "unbound" subform. Both use datasources that reference the same staff people. The subform displays a continuous list of staff, while the Main form displays only a single record at a time.

What I Want:
I would like to filter the main form's datasource by the record currently selected in the subform, using the ID field.

In other words, if a user selects a record in the subform, the main form displays all records relating to the staff person. Conceptually, this is sort of a reverse main form/sub form.

Thoughts?

 

[MajP]

use the subforms on current event. This happens everytime a different record is selected. Then you can modify the recordsource of the main or just its filter. Something like

with me.parent
.filteron = false
.filter = "ID = " & nz(me.ID,0)
.filterOn = true
end with

If modifying the recorsourse it would be something like

dim strSql as string
strSql = "Select ....... from .... where ID = " & nz(me.id,0) & " Order by..."
me.parent.recordsource = strSql

 

[CharlieT302]

Thanks MajP

I will try it. By the way, With...End With ?

Why those keywords?

 

[MajP]

it is just a shorthand notation to set a bunch of properties in a row for the same object. Same as

me.parent.filteron = false
me.parent.filter = "ID = " & nz(me.ID,0)
me.parent.filterOn = true