Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Filter Source Object

Discussion in 'Information Technology' started by doodle, Jul 28, 2006.

  1. doodle

    doodle
    Expand Collapse
    Guest

    Greetings all.
    windows xp, access 97

    I have created a main "switchboard" type menu with command buttons on
    the left and an unbound subform on the right. If they click on the
    "Enter Order" button, the source object for the subform is changed to
    the Order Entry form.

    All works well.

    The problem is that I have created a search command at the top of the
    switchboard menu where the user can enter a customer number to search
    for and, in a perfect world, the subform would become visible with the
    record for that customer number. I coded it so that it works to open
    the form, but I don't want to open the form as a separate object, I
    want it to be the source object of the unbound subform.

    Search command button code:

    Private Sub cmdSearch_Click()
    Dim stLinkCriteria As String

    stLinkCriteria = "[CustNum]=" & "'" & Me![optSearch] & "'"
    DoCmd.OpenForm "frmOrderEntrySearch", , , stLinkCriteria

    End Sub

    Code to show source object, not sure how to modify to show the above:
    Me!sbfMain.Visible = True
    Me!sbfMain.SourceObject = "frmOrderEntrySearch"


    Thanks in advance.

    -doodle
     
  2. Loading...

    Similar Threads Forum Date
    General Filter Test Hard Talk May 20, 2008
    Gurus Significance of Contemporary Persian Sources Relating to Guru Gobind Singh History of Sikhism Dec 21, 2013
    Gurus The Original Sources of Sikh History History of Sikhism Jan 2, 2013
    Gurbani is Source of the Five Symbols of the Khalsa - Dr. Nikky Guninder Singh Sikh Sikhi Sikhism Jul 21, 2011
    21 Sources of Protein for Vegetarians Health & Nutrition Oct 25, 2010

  3. Ken Sheridan

    Ken Sheridan
    Expand Collapse
    Guest

    Try this:

    Dim stLinkCriteria As String

    stLinkCriteria = "[CustNum]=" & "'" & Me![optSearch] & "'"

    Me!sbfMain.Visible = True
    Me!sbfMain.SourceObject = "frmOrderEntrySearch"
    Me!sbfMain.Form.Filter = strLinkCriteria
    Me!sbfMain.FilerOn = True

    or you could base the frmOrderEntrySearch on a query with a parameter which
    references the optSearch control on the parent form, e.g. to show orders by
    the selected customer in descending order date order.

    SELECT *
    FROM Orders
    WHERE CustNum = Forms!YourParentFormNameGoesHere!optSearch
    ORDER BY OrderDate DESC;

    in which case you simply assign the form name to the subform control's
    SourceObject property with no need to filter it.

    Ken Sheridan
    Stafford, England

    "doodle" wrote:

    > Greetings all.
    > windows xp, access 97
    >
    > I have created a main "switchboard" type menu with command buttons on
    > the left and an unbound subform on the right. If they click on the
    > "Enter Order" button, the source object for the subform is changed to
    > the Order Entry form.
    >
    > All works well.
    >
    > The problem is that I have created a search command at the top of the
    > switchboard menu where the user can enter a customer number to search
    > for and, in a perfect world, the subform would become visible with the
    > record for that customer number. I coded it so that it works to open
    > the form, but I don't want to open the form as a separate object, I
    > want it to be the source object of the unbound subform.
    >
    > Search command button code:
    >
    > Private Sub cmdSearch_Click()
    > Dim stLinkCriteria As String
    >
    > stLinkCriteria = "[CustNum]=" & "'" & Me![optSearch] & "'"
    > DoCmd.OpenForm "frmOrderEntrySearch", , , stLinkCriteria
    >
    > End Sub
    >
    > Code to show source object, not sure how to modify to show the above:
    > Me!sbfMain.Visible = True
    > Me!sbfMain.SourceObject = "frmOrderEntrySearch"
    >
    >
    > Thanks in advance.
    >
    > -doodle
    >
    >
     
  4. doodle

    doodle
    Expand Collapse
    Guest

    Ken,

    Thanks for your response. I tried Option #1 so far, and am getting the
    "Option doesn't support this property or method" error on this line:
    Me!sbfMain.FilterOn = True

    Here is my code:

    Private Sub cmdSearch_Click()
    If IsNull(optSearch.Value) Then _
    MsgBox Prompt:="You must enter a value to search for.",
    Buttons:=vbOKOnly
    If IsNull(optSearch.Value) Then _
    optSearch.SetFocus
    If IsNull(optSearch.Value) Then _
    Exit Sub
    Select Case frmOptions.Value
    Case Is = 1 'Customer Number
    cntCust = DCount("[CustNum]", "tblOrders", "[CustNum] Like ""*"
    & [optSearch] & "*""")
    Select Case cntCust
    'If there are matching records for search value, form
    opens filtered to records
    Case Is > 0
    Select Case cntCust
    Case Is > 1
    MsgBox Prompt:="There are " & cntCust & "
    records that match your search." & _
    "Use the 'Next Record' button to see
    additional records.", _
    Buttons:=vbOKOnly, Title:="Message From
    Adria"
    Me!sbfDemoMain.Visible = True
    Me!sbfDemoMain.SourceObject =
    "frmOrderEntrySearch"
    Me!sbfDemoMain.Form.Filter = "[CustNum]=" &
    "'" & Me![optSearch] & "'"
    Me!sbfDemoMain.FilterOn = True
    Case Else
    ' DoCmd.OpenForm "frmOrderEntrySearch", , ,
    "[CustNum]=" & "'" & Me![optSearch] & "'"
    End Select
    Case Else
    MsgBox Prompt:="There are no records that match
    your search.", Buttons:=vbOKOnly
    End Select
    Case Is = 3 'Reference #
    'code for ref # option
    Case Is = 4 'Order/Quote #
    'code for order/quote # option
    Case Else
    MsgBox "You must select a search option", Buttons:=vbOKOnly
    frmOptions.SetFocus
    End Select

    End Sub


    -doodle
     
  5. doodle

    doodle
    Expand Collapse
    Guest

    Ken,

    I don't think that Option #2 is going to work because there are
    actually 3 different search options with the possibility of adding
    more. Unless I came up with some kid of dynamic SQL statement, I would
    have to have a separate form for each search option.

    -doodle
     
  6. Ken Sheridan

    Ken Sheridan
    Expand Collapse
    Guest

    Doh! Mea Culpa I think; try:

    Me!sbfMain.Form.FilterOn = True

    "doodle" wrote:

    > Ken,
    >
    > Thanks for your response. I tried Option #1 so far, and am getting the
    > "Option doesn't support this property or method" error on this line:
    > Me!sbfMain.FilterOn = True
    >
    > Here is my code:
    >
    > Private Sub cmdSearch_Click()
    > If IsNull(optSearch.Value) Then _
    > MsgBox Prompt:="You must enter a value to search for.",
    > Buttons:=vbOKOnly
    > If IsNull(optSearch.Value) Then _
    > optSearch.SetFocus
    > If IsNull(optSearch.Value) Then _
    > Exit Sub
    > Select Case frmOptions.Value
    > Case Is = 1 'Customer Number
    > cntCust = DCount("[CustNum]", "tblOrders", "[CustNum] Like ""*"
    > & [optSearch] & "*""")
    > Select Case cntCust
    > 'If there are matching records for search value, form
    > opens filtered to records
    > Case Is > 0
    > Select Case cntCust
    > Case Is > 1
    > MsgBox Prompt:="There are " & cntCust & "
    > records that match your search." & _
    > "Use the 'Next Record' button to see
    > additional records.", _
    > Buttons:=vbOKOnly, Title:="Message From
    > Adria"
    > Me!sbfDemoMain.Visible = True
    > Me!sbfDemoMain.SourceObject =
    > "frmOrderEntrySearch"
    > Me!sbfDemoMain.Form.Filter = "[CustNum]=" &
    > "'" & Me![optSearch] & "'"
    > Me!sbfDemoMain.FilterOn = True
    > Case Else
    > ' DoCmd.OpenForm "frmOrderEntrySearch", , ,
    > "[CustNum]=" & "'" & Me![optSearch] & "'"
    > End Select
    > Case Else
    > MsgBox Prompt:="There are no records that match
    > your search.", Buttons:=vbOKOnly
    > End Select
    > Case Is = 3 'Reference #
    > 'code for ref # option
    > Case Is = 4 'Order/Quote #
    > 'code for order/quote # option
    > Case Else
    > MsgBox "You must select a search option", Buttons:=vbOKOnly
    > frmOptions.SetFocus
    > End Select
    >
    > End Sub
    >
    >
    > -doodle
    >
    >
     

Share This Page