Filtering a subform base on record selected in another subform.

P

Paterson10987

I want to filter a subform ('RecordSearch_subSupplier') when you change the
record selected from another subform ('RecordSearch_Sub'). My main form is
'RecordSearch.'

heres what I've tried:

Private Sub Form_SelectionChange()
Me.Parent.RecordSearch_subSupplier.Form.Filter = "PartID = " & [ID]
Me.Parent.RecordSearch_subSupplier.Form.FilterOn = True
End Sub
 
D

dymondjack

I pulled this from a db I've had running for abtou two years now. It may not
be the best way, but it works, and I've never really experienced any
performance issues. This goes on the Current event of the first subform, and
updates the recordsource of the second one via sql:


strSQL = "SELECT tblPrograms.Program, " _
& "tblPrograms.Part, " _
& "tblPrograms.Class, " _
& "tblPrograms.Operations, " _
& "tblPrograms.Description " _
& "FROM tblPrograms " _
& "WHERE (([Part] = '" & Me.ctlPart & "') " _
& "And (tblPrograms.Archived = False)) " _
& "ORDER BY tblPrograms.Class, tblPrograms.Operations"

Forms!frmMain!ctlsfrmMainPrograms.Form.RecordSource = strSQL
Forms!frmMain!ctlsfrmMainPrograms.Form.Requery

hth
--
Jack Leach
www.tristatemachine.com

- "First, get your information. Then, you can distort it at your leisure."
- Mark Twain
 
P

Paterson10987

Thanks its perfect.

dymondjack said:
I pulled this from a db I've had running for abtou two years now. It may not
be the best way, but it works, and I've never really experienced any
performance issues. This goes on the Current event of the first subform, and
updates the recordsource of the second one via sql:


strSQL = "SELECT tblPrograms.Program, " _
& "tblPrograms.Part, " _
& "tblPrograms.Class, " _
& "tblPrograms.Operations, " _
& "tblPrograms.Description " _
& "FROM tblPrograms " _
& "WHERE (([Part] = '" & Me.ctlPart & "') " _
& "And (tblPrograms.Archived = False)) " _
& "ORDER BY tblPrograms.Class, tblPrograms.Operations"

Forms!frmMain!ctlsfrmMainPrograms.Form.RecordSource = strSQL
Forms!frmMain!ctlsfrmMainPrograms.Form.Requery

hth
--
Jack Leach
www.tristatemachine.com

- "First, get your information. Then, you can distort it at your leisure."
- Mark Twain


Paterson10987 said:
I want to filter a subform ('RecordSearch_subSupplier') when you change the
record selected from another subform ('RecordSearch_Sub'). My main form is
'RecordSearch.'

heres what I've tried:

Private Sub Form_SelectionChange()
Me.Parent.RecordSearch_subSupplier.Form.Filter = "PartID = " & [ID]
Me.Parent.RecordSearch_subSupplier.Form.FilterOn = True
End Sub
Click to expand...
Click to expand...
 
P

Paterson10987

Ok i lied a little. Everytime the Form loads it sends me and error saying
'invalid reference to the property form/report' But then If I just hit end,
it finishes loading the form and works fine.

here's what i have now:

Private Sub Form_Current()
Dim strSQL As String

strSQL = "SELECT PartSuppliers.* FROM PartSuppliers WHERE
(PartSuppliers.PartID = " & [ID] & ")"
Forms!RecordSearch!RecordSearch_subSupplier.Form.RecordSource = strSQL
Forms!RecordSearch!RecordSearch_subSupplier.Form.Requery

strSQL = "SELECT PartLocations.* FROM PartLocations WHERE
(PartLocations.PartID = " & [ID] & ")"
Forms!RecordSearch!RecordSearch_subLocation.Form.RecordSource = strSQL
Forms!RecordSearch!RecordSearch_subLocation.Form.Requery
End Sub

dymondjack said:
I pulled this from a db I've had running for abtou two years now. It may not
be the best way, but it works, and I've never really experienced any
performance issues. This goes on the Current event of the first subform, and
updates the recordsource of the second one via sql:


strSQL = "SELECT tblPrograms.Program, " _
& "tblPrograms.Part, " _
& "tblPrograms.Class, " _
& "tblPrograms.Operations, " _
& "tblPrograms.Description " _
& "FROM tblPrograms " _
& "WHERE (([Part] = '" & Me.ctlPart & "') " _
& "And (tblPrograms.Archived = False)) " _
& "ORDER BY tblPrograms.Class, tblPrograms.Operations"

Forms!frmMain!ctlsfrmMainPrograms.Form.RecordSource = strSQL
Forms!frmMain!ctlsfrmMainPrograms.Form.Requery

hth
--
Jack Leach
www.tristatemachine.com

- "First, get your information. Then, you can distort it at your leisure."
- Mark Twain


Paterson10987 said:
I want to filter a subform ('RecordSearch_subSupplier') when you change the
record selected from another subform ('RecordSearch_Sub'). My main form is
'RecordSearch.'

heres what I've tried:

Private Sub Form_SelectionChange()
Me.Parent.RecordSearch_subSupplier.Form.Filter = "PartID = " & [ID]
Me.Parent.RecordSearch_subSupplier.Form.FilterOn = True
End Sub
Click to expand...
Click to expand...
 
D

dymondjack

That's probably because the first subform is loaded (which runs the Current
event) before the second sub is loaded, hence it cant find the sub yet...

I think what I did was use a variable private to the form's module in the
forms Open event, as a flag for when the form is first opened. Then run that
code conditionally.

Option Explicit

Private pFormOpen As Boolean

Private Sub Form_Open()
pFormOpen = True
End Sub

Private SubForm_Current()
If (pFormOpen = False) And (Me.NewRecord = False) Then
'set your sql.recordsource here
Else
pFormOpen = False
End If
End Sub

Something like that anyway... there's a little bit of playing around with it

--
Jack Leach
www.tristatemachine.com

- "First, get your information. Then, you can distort it at your leisure."
- Mark Twain


Paterson10987 said:
Ok i lied a little. Everytime the Form loads it sends me and error saying
'invalid reference to the property form/report' But then If I just hit end,
it finishes loading the form and works fine.

here's what i have now:

Private Sub Form_Current()
Dim strSQL As String

strSQL = "SELECT PartSuppliers.* FROM PartSuppliers WHERE
(PartSuppliers.PartID = " & [ID] & ")"
Forms!RecordSearch!RecordSearch_subSupplier.Form.RecordSource = strSQL
Forms!RecordSearch!RecordSearch_subSupplier.Form.Requery

strSQL = "SELECT PartLocations.* FROM PartLocations WHERE
(PartLocations.PartID = " & [ID] & ")"
Forms!RecordSearch!RecordSearch_subLocation.Form.RecordSource = strSQL
Forms!RecordSearch!RecordSearch_subLocation.Form.Requery
End Sub

dymondjack said:
I pulled this from a db I've had running for abtou two years now. It may not
be the best way, but it works, and I've never really experienced any
performance issues. This goes on the Current event of the first subform, and
updates the recordsource of the second one via sql:


strSQL = "SELECT tblPrograms.Program, " _
& "tblPrograms.Part, " _
& "tblPrograms.Class, " _
& "tblPrograms.Operations, " _
& "tblPrograms.Description " _
& "FROM tblPrograms " _
& "WHERE (([Part] = '" & Me.ctlPart & "') " _
& "And (tblPrograms.Archived = False)) " _
& "ORDER BY tblPrograms.Class, tblPrograms.Operations"

Forms!frmMain!ctlsfrmMainPrograms.Form.RecordSource = strSQL
Forms!frmMain!ctlsfrmMainPrograms.Form.Requery

hth
--
Jack Leach
www.tristatemachine.com

- "First, get your information. Then, you can distort it at your leisure."
- Mark Twain


Paterson10987 said:
I want to filter a subform ('RecordSearch_subSupplier') when you change the
record selected from another subform ('RecordSearch_Sub'). My main form is
'RecordSearch.'

heres what I've tried:

Private Sub Form_SelectionChange()
Me.Parent.RecordSearch_subSupplier.Form.Filter = "PartID = " & [ID]
Me.Parent.RecordSearch_subSupplier.Form.FilterOn = True
End Sub
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Subform Combo Record Source Issue 1
mainform/subform another user is editing... 4
New Record in a subform 5
Move to Record in subform 5
can you change the record source of a subform with a check box 2
VBA Syntax for subform record source change 6
Auto populate subform in new row 2
How to add record in Subform which is in another Subform 1

Top