Access Runtime (Filter By Form)

Discussion in 'Microsoft Access' started by Guest, Dec 28, 2005.

  1. Guest

    Guest Guest

    I'm testing my Access 2003 DB in runtime mode, I'm recieving an error on a
    macro that filters to a record on a form.
    I''ve also tried converting it to to a VB Code Module, I still recieve an
    error.

    Here's the generated VB Module code it generated

    Function Find_Applicant()
    On Error GoTo Find_Applicant_Err

    DoCmd.Echo False, "Please Stand By"
    DoCmd.Hourglass True
    DoCmd.RunCommand acCmdFilterByForm
    DoCmd.RunCommand acCmdClearGrid


    Find_Applicant_Exit:
    Exit Function

    Find_Applicant_Err:
    MsgBox Error$
    Resume Find_Applicant_Exit

    End Function

    Here are the error messages I receive,

    It brings up a little box that says the (runcommand action was cancelled) I
    click OK and it brings up another small box that says (OK) so i click Ok
    and it brings up another little box that says (Resume with out error) I click
    OK and it goes back to the same blank little box again, it does this forever
    from (Resume with out error) to Blank Box with OK.


    Thanks--Any help will be greatly appreciated.
     
    Guest, Dec 28, 2005
    #1
    1. Advertisements

  2. Guest

    Allen Browne Guest

    Filter By Form is not available in the runtime.

    You will need to code another way to let the user filter the form.

    --
    Allen Browne - Microsoft MVP. Perth, Western Australia.
    Tips for Access users - http://allenbrowne.com/tips.html
    Reply to group, rather than allenbrowne at mvps dot org.

    "patentinv" <> wrote in message
    news:...
    > I'm testing my Access 2003 DB in runtime mode, I'm recieving an error on a
    > macro that filters to a record on a form.
    > I''ve also tried converting it to to a VB Code Module, I still recieve an
    > error.
    >
    > Here's the generated VB Module code it generated
    >
    > Function Find_Applicant()
    > On Error GoTo Find_Applicant_Err
    >
    > DoCmd.Echo False, "Please Stand By"
    > DoCmd.Hourglass True
    > DoCmd.RunCommand acCmdFilterByForm
    > DoCmd.RunCommand acCmdClearGrid
    >
    >
    > Find_Applicant_Exit:
    > Exit Function
    >
    > Find_Applicant_Err:
    > MsgBox Error$
    > Resume Find_Applicant_Exit
    >
    > End Function
    >
    > Here are the error messages I receive,
    >
    > It brings up a little box that says the (runcommand action was cancelled)
    > I
    > click OK and it brings up another small box that says (OK) so i click Ok
    > and it brings up another little box that says (Resume with out error) I
    > click
    > OK and it goes back to the same blank little box again, it does this
    > forever
    > from (Resume with out error) to Blank Box with OK.
    >
    >
    > Thanks--Any help will be greatly appreciated.
     
    Allen Browne, Dec 28, 2005
    #2
    1. Advertisements

  3. Guest

    Guest Guest

    Do you have any suggestions on how I can do this? or links to places for
    similiar questions,
    Thanks ,
    "Allen Browne" wrote:

    > Filter By Form is not available in the runtime.
    >
    > You will need to code another way to let the user filter the form.
    >
    > --
    > Allen Browne - Microsoft MVP. Perth, Western Australia.
    > Tips for Access users - http://allenbrowne.com/tips.html
    > Reply to group, rather than allenbrowne at mvps dot org.
    >
    > "patentinv" <> wrote in message
    > news:...
    > > I'm testing my Access 2003 DB in runtime mode, I'm recieving an error on a
    > > macro that filters to a record on a form.
    > > I''ve also tried converting it to to a VB Code Module, I still recieve an
    > > error.
    > >
    > > Here's the generated VB Module code it generated
    > >
    > > Function Find_Applicant()
    > > On Error GoTo Find_Applicant_Err
    > >
    > > DoCmd.Echo False, "Please Stand By"
    > > DoCmd.Hourglass True
    > > DoCmd.RunCommand acCmdFilterByForm
    > > DoCmd.RunCommand acCmdClearGrid
    > >
    > >
    > > Find_Applicant_Exit:
    > > Exit Function
    > >
    > > Find_Applicant_Err:
    > > MsgBox Error$
    > > Resume Find_Applicant_Exit
    > >
    > > End Function
    > >
    > > Here are the error messages I receive,
    > >
    > > It brings up a little box that says the (runcommand action was cancelled)
    > > I
    > > click OK and it brings up another small box that says (OK) so i click Ok
    > > and it brings up another little box that says (Resume with out error) I
    > > click
    > > OK and it goes back to the same blank little box again, it does this
    > > forever
    > > from (Resume with out error) to Blank Box with OK.
    > >
    > >
    > > Thanks--Any help will be greatly appreciated.

    >
    >
    >
     
    Guest, Dec 28, 2005
    #3
  4. Guest

    Allen Browne Guest

    In any form, there are usually only a few fields that the user is likely to
    filter by. Some of these could be a range (such as a month or quarter.) What
    I do is to place unbound controls of a different color in the Form Header
    section, so the user can enter any combination of controls, and then click
    the command button to filter them.

    The button's Click event procedure builds a string from the non-null boxes,
    and applies it to the Filter of the form. The filter string looks exactly
    like the WHERE clause of a query, so you can mock up a query with any old
    values and then switch it to SQL View (View menu) to see an example of what
    you need to create.

    The example below is structured so that it is very easy to add as many
    filter controls as you need, tacking the AND onto the end of each one, and
    removing the trailing AND at the end. It illustrates how to use the
    delimiters for text and date fields, and to ensure the date is interpreted
    correctly regardless of the user's regional settings.

    Private Sub cmdFilter_Click()
    Dim strWhere As String
    Dim lngLen As Long
    Const strcJetDate = "\#mm\/dd\/yyyy\#"

    If Me.Dirty Then Me.Dirty = False 'Save first

    If Not IsNull(Me.cboFilterCompanyID) Then 'Number field example.
    strWhere = strWhere & "([CompanyID] = " & Me.cboFilterCompanyID & ")
    AND "
    End If

    If Not IsNull(Me.txtFilterCity) Then 'Text field example
    strWhere = strWhere "([City] = """ & Me.txtFilterCity & """) AND "
    End If

    If Not IsNull(Me.txtFilterInvoiceDate) Then 'Date field example
    strWhere = strWhere & "([InvoiceDate] = " & _
    Format(Me.txtFilterInvoiceDate, strcJetDate) & ") AND "
    End If

    lngLen = Len(strWhere) - 5 'without trailing " AND ".
    If lngLen > 0 Then
    Me.Filter = Left(strWhere, lngLen)
    Me.FilterOn = True
    Else
    MsgBox "No criteria"
    End If
    End Sub


    For an example of how to handle a date range, see Method 2 in this article:
    Limiting a Report to a Date Range
    at:
    http://allenbrowne.com/casu-08.html

    If you want something more powerful than that, search for "Query by Form".

    --
    Allen Browne - Microsoft MVP. Perth, Western Australia.
    Tips for Access users - http://allenbrowne.com/tips.html
    Reply to group, rather than allenbrowne at mvps dot org.

    "patentinv" <> wrote in message
    news:...
    > Do you have any suggestions on how I can do this? or links to places for
    > similiar questions,
    > Thanks ,
    > "Allen Browne" wrote:
    >
    >> Filter By Form is not available in the runtime.
    >>
    >> You will need to code another way to let the user filter the form.
    >>
    >> "patentinv" <> wrote in message
    >> news:...
    >> > I'm testing my Access 2003 DB in runtime mode, I'm recieving an error
    >> > on a
    >> > macro that filters to a record on a form.
    >> > I''ve also tried converting it to to a VB Code Module, I still recieve
    >> > an
    >> > error.
    >> >
    >> > Here's the generated VB Module code it generated
    >> >
    >> > Function Find_Applicant()
    >> > On Error GoTo Find_Applicant_Err
    >> >
    >> > DoCmd.Echo False, "Please Stand By"
    >> > DoCmd.Hourglass True
    >> > DoCmd.RunCommand acCmdFilterByForm
    >> > DoCmd.RunCommand acCmdClearGrid
    >> >
    >> >
    >> > Find_Applicant_Exit:
    >> > Exit Function
    >> >
    >> > Find_Applicant_Err:
    >> > MsgBox Error$
    >> > Resume Find_Applicant_Exit
    >> >
    >> > End Function
    >> >
    >> > Here are the error messages I receive,
    >> >
    >> > It brings up a little box that says the (runcommand action was
    >> > cancelled)
    >> > I
    >> > click OK and it brings up another small box that says (OK) so i click
    >> > Ok
    >> > and it brings up another little box that says (Resume with out error) I
    >> > click
    >> > OK and it goes back to the same blank little box again, it does this
    >> > forever
    >> > from (Resume with out error) to Blank Box with OK.
    >> >
    >> >
    >> > Thanks--Any help will be greatly appreciated.
     
    Allen Browne, Dec 28, 2005
    #4
  5. Guest

    Guest Guest

    Let's say I had the filter set on customer name and address like you stated.
    And the user selected customer as there filtering option and entered there
    customer name and clicked the command button filter will the filter bring
    back the whole record set customer address, ph #'s, email address and all
    data in that record set in all of the tables the data resides in?

    Thanks--Allen
    "Allen Browne" wrote:

    > In any form, there are usually only a few fields that the user is likely to
    > filter by. Some of these could be a range (such as a month or quarter.) What
    > I do is to place unbound controls of a different color in the Form Header
    > section, so the user can enter any combination of controls, and then click
    > the command button to filter them.
    >
    > The button's Click event procedure builds a string from the non-null boxes,
    > and applies it to the Filter of the form. The filter string looks exactly
    > like the WHERE clause of a query, so you can mock up a query with any old
    > values and then switch it to SQL View (View menu) to see an example of what
    > you need to create.
    >
    > The example below is structured so that it is very easy to add as many
    > filter controls as you need, tacking the AND onto the end of each one, and
    > removing the trailing AND at the end. It illustrates how to use the
    > delimiters for text and date fields, and to ensure the date is interpreted
    > correctly regardless of the user's regional settings.
    >
    > Private Sub cmdFilter_Click()
    > Dim strWhere As String
    > Dim lngLen As Long
    > Const strcJetDate = "\#mm\/dd\/yyyy\#"
    >
    > If Me.Dirty Then Me.Dirty = False 'Save first
    >
    > If Not IsNull(Me.cboFilterCompanyID) Then 'Number field example.
    > strWhere = strWhere & "([CompanyID] = " & Me.cboFilterCompanyID & ")
    > AND "
    > End If
    >
    > If Not IsNull(Me.txtFilterCity) Then 'Text field example
    > strWhere = strWhere "([City] = """ & Me.txtFilterCity & """) AND "
    > End If
    >
    > If Not IsNull(Me.txtFilterInvoiceDate) Then 'Date field example
    > strWhere = strWhere & "([InvoiceDate] = " & _
    > Format(Me.txtFilterInvoiceDate, strcJetDate) & ") AND "
    > End If
    >
    > lngLen = Len(strWhere) - 5 'without trailing " AND ".
    > If lngLen > 0 Then
    > Me.Filter = Left(strWhere, lngLen)
    > Me.FilterOn = True
    > Else
    > MsgBox "No criteria"
    > End If
    > End Sub
    >
    >
    > For an example of how to handle a date range, see Method 2 in this article:
    > Limiting a Report to a Date Range
    > at:
    > http://allenbrowne.com/casu-08.html
    >
    > If you want something more powerful than that, search for "Query by Form".
    >
    > --
    > Allen Browne - Microsoft MVP. Perth, Western Australia.
    > Tips for Access users - http://allenbrowne.com/tips.html
    > Reply to group, rather than allenbrowne at mvps dot org.
    >
    > "patentinv" <> wrote in message
    > news:...
    > > Do you have any suggestions on how I can do this? or links to places for
    > > similiar questions,
    > > Thanks ,
    > > "Allen Browne" wrote:
    > >
    > >> Filter By Form is not available in the runtime.
    > >>
    > >> You will need to code another way to let the user filter the form.
    > >>
    > >> "patentinv" <> wrote in message
    > >> news:...
    > >> > I'm testing my Access 2003 DB in runtime mode, I'm recieving an error
    > >> > on a
    > >> > macro that filters to a record on a form.
    > >> > I''ve also tried converting it to to a VB Code Module, I still recieve
    > >> > an
    > >> > error.
    > >> >
    > >> > Here's the generated VB Module code it generated
    > >> >
    > >> > Function Find_Applicant()
    > >> > On Error GoTo Find_Applicant_Err
    > >> >
    > >> > DoCmd.Echo False, "Please Stand By"
    > >> > DoCmd.Hourglass True
    > >> > DoCmd.RunCommand acCmdFilterByForm
    > >> > DoCmd.RunCommand acCmdClearGrid
    > >> >
    > >> >
    > >> > Find_Applicant_Exit:
    > >> > Exit Function
    > >> >
    > >> > Find_Applicant_Err:
    > >> > MsgBox Error$
    > >> > Resume Find_Applicant_Exit
    > >> >
    > >> > End Function
    > >> >
    > >> > Here are the error messages I receive,
    > >> >
    > >> > It brings up a little box that says the (runcommand action was
    > >> > cancelled)
    > >> > I
    > >> > click OK and it brings up another small box that says (OK) so i click
    > >> > Ok
    > >> > and it brings up another little box that says (Resume with out error) I
    > >> > click
    > >> > OK and it goes back to the same blank little box again, it does this
    > >> > forever
    > >> > from (Resume with out error) to Blank Box with OK.
    > >> >
    > >> >
    > >> > Thanks--Any help will be greatly appreciated.

    >
    >
    >
     
    Guest, Dec 28, 2005
    #5
  6. Guest

    Allen Browne Guest

    The filter is just the criteria. It does not affect which fields are
    returned.

    --
    Allen Browne - Microsoft MVP. Perth, Western Australia.
    Tips for Access users - http://allenbrowne.com/tips.html
    Reply to group, rather than allenbrowne at mvps dot org.

    "patentinv" <> wrote in message
    news:...
    > Let's say I had the filter set on customer name and address like you
    > stated.
    > And the user selected customer as there filtering option and entered there
    > customer name and clicked the command button filter will the filter bring
    > back the whole record set customer address, ph #'s, email address and all
    > data in that record set in all of the tables the data resides in?
     
    Allen Browne, Dec 28, 2005
    #6
    1. Advertisements

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

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Alan

    Filter by Selection/Filter by Form Report

    Alan, Jul 22, 2003, in forum: Microsoft Access
    Replies:
    1
    Views:
    1,514
    Allen Browne
    Jul 22, 2003
  2. Henrootje

    Filter report based on filter on form

    Henrootje, Dec 19, 2005, in forum: Microsoft Access
    Replies:
    1
    Views:
    700
    Van T. Dinh
    Dec 19, 2005
  3. Guest

    Include filter toolbar in Access Runtime

    Guest, Jan 20, 2006, in forum: Microsoft Access
    Replies:
    1
    Views:
    284
    Rick Brandt
    Jan 20, 2006
  4. Replies:
    3
    Views:
    801
    John Vinson
    Oct 17, 2006
  5. Philippe
    Replies:
    1
    Views:
    637
    Gina Whipp
    Feb 17, 2009
Loading...

Share This Page