Public funtion to filter many forms

[Fev]

Hi
I have tried by creating a public function in a module to retrieve and
retain a Personal ID value. I would then need to then use this
Personal ID to filter a number of different forms that will be
activated by buttons in the header of a Main form. The initial filter
is created by clicking in a subform of this main form. My problem is
to trigger the function to store this value, and then to use the
Personal ID in each button's click procedure. My code is as follows:

'In the Module called myButtons:

Private myFilter
Public Function setmyPID(PID)
myFilter = getmyPID(PID)
End Function
Public Function getmyPID(PID)
PID = DLookup("[PersonalID]", "qryRelationships", "[Member Name]")
End Function

The following code creates the initial filter in the form code:

Private Sub Family_Member_Click()
If Not IsLoaded("Church Member") Then
DoCmd.OpenForm "Church Member", acNormal
DoCmd.MoveSize 11897, 2170

End If
Forms![Church Member].Form.Filter = _
"[PersonalID]=" & Me.PersonalID
Forms![Church Member].Form.FilterOn = True
Forms![Church Member].Form.Requery
Set myControl = Forms![Church Member].Controls(Title)

End Sub

I have with many variations got the PID value in the function, but as
soon as the function ends, the PID becomes empty.
Thanks

 

[Klatuu]

Where is PID Dimmed?
The function returns nothing.
This line doesn't appear to be correct:
PID = DLookup("[PersonalID]", "qryRelationships", "[Member Name]")
The 3rd argument is a criteria argument that should be like an Where clause
in an SQL statement. Something like:
PID = DLookup("[PersonalID]", "qryRelationships", "[Member Name] = " & some
value to compare on goes here)

An easier way would be to get the DLookup correct and use it as the control
source of a text box on your main form, then filter the record source queries
of the other forms on this control. That assumes the form it is on is always
open.

 

[Fev]

Where is PID Dimmed?
The function returns nothing.
This line doesn't appear to be correct:
PID = DLookup("[PersonalID]", "qryRelationships", "[Member Name]")
The 3rd argument is a criteria argument that should be like an Where clause
in an SQL statement. Something like:
PID = DLookup("[PersonalID]", "qryRelationships", "[Member Name] = " &some
value to compare on goes here)

An easier way would be to get the DLookup correct and use it as the control
source of a text box on your main form, then filter the record source queries
of the other forms on this control.  That assumes the form it is on is always
open.
--
Dave Hargis, Microsoft Access MVP

Hi
I have tried by creating a public function in a module to retrieve and
retain a Personal ID value.  I would then need to then use this
Personal ID to filter a number of different forms that will be
activated by buttons in the header of a Main form.  The initial filter
is created by clicking in a subform of this main form.  My problem is
to trigger the function to store this value, and then to use the
Personal ID in each button's click procedure.  My code is as follows:

'In the Module called myButtons:

Private myFilter
Public Function setmyPID(PID)
    myFilter = getmyPID(PID)
End Function
Public Function getmyPID(PID)
    PID = DLookup("[PersonalID]", "qryRelationships", "[Member Name]")
End Function

The following code creates the initial filter in the form code:

Private Sub Family_Member_Click()
If Not IsLoaded("Church Member") Then
  DoCmd.OpenForm "Church Member", acNormal
  DoCmd.MoveSize 11897, 2170

End If
Forms![Church Member].Form.Filter = _
  "[PersonalID]=" & Me.PersonalID
Forms![Church Member].Form.FilterOn = True
Forms![Church Member].Form.Requery
Set myControl = Forms![Church Member].Controls(Title)

End Sub

I have with many variations got the PID value in the function, but as
soon as the function ends, the PID becomes empty.
Thanks- Hide quoted text -

- Show quoted text -

Thank you - it works like a dream!