Results 1 to 9 of 9
  1. #1
    Star Lounger
    Join Date
    Nov 2001
    Location
    Texas, USA
    Posts
    93
    Thanks
    0
    Thanked 0 Times in 0 Posts

    filter / search a form (2003)

    Hi. I've been asked to build a small db for work, and I can't remember how to do some things. I want only the main form to be visible, but as we add entries and need to find old ones that need to be updated, we have to go through them one by one. Is there a way to do either or both of these? Each entry will have the name of the person entering the information, type of entry, and brief description (text field) as well as entry dates.
    Filter against field 1 (name of person entering the information, or type) to reduce the potential entries and/or
    set it up so that the descriptions are displayed, in alpha order, so the viewer can choose the entry?
    And if so, what would be the best way to exclude old entries (more than 12 months) from the description list?

    Is this even possible?

    Thanks so much for your help.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: filter / search a form (2003)

    You could put combo boxes in the form header to filter the form.
    The first combo box could list the persons.
    The second combo box could list the descriptions entered by the person selected in the first combo box within the last twelve months.
    You can do this by creating a query based on the table with the following criteria:

    Person: [Forms]![NameOfYourForm]![FirstCombo]
    EntryDate: >=DateAdd("yyyy",-1,Date())

    This query is the row source of the second combo box

    The After Update event of the first combo box requeries the second one:

    Private Sub FirstCombo_AfterUpdate()
    Me.SecondCombo.Requery
    End Sub

    The After Update event of the second combo jumps to the selected record:

    Private Sub SecondCombo_AfterUpdate()
    Dim rst As Object
    Dim strWhere As String
    If Not IsNull(Me.SecondCombo) Then
    Set rst = Me.Recordset.Clone
    strWhere = "Person = " & Chr(34) & Me.FirstCombo & Chr(34) & _
    " And Description = " & Chr(34) & Me.SecondCombo & Chr(34)
    rst.FindFirst strWhere
    Me.Bookmark = rst.Bookmark
    End If
    End Sub

    You'll have to substitute the appropriate names, of course.

  3. #3
    Star Lounger
    Join Date
    Nov 2001
    Location
    Texas, USA
    Posts
    93
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: filter / search a form (2003)

    I've tried to follow your instructions, but I don't get the desired result.

    I added a new table with all the staff names, called Staffname. I set up 2 fields, one named NameFilter and the other DescripFilter. I set NameFilter row source to the column in the Staffname table, and made the After Update
    Private Sub NameFilter_AfterUpdate()
    Me.DescripFilter.Requery
    End Sub

    For the DescripFilter, I made a query alled NameFilter, with column 3 having the project brief description. And the After Update to
    Private Sub DescripFilter_AfterUpdate()
    Dim rst As Object
    Dim strWhere As String
    If Not IsNull(MainFrm.DescripFilter) Then
    Set rst = Me.Recordset.Clone
    strWhere = "Person = " & Chr(34) & Me.NameFilter & Chr(34) & _
    " And Description = " & Chr(34) & Me.DescripFilter & Chr(34)
    rst.FindFirst strWhere
    Me.Bookmark = rst.Bookmark
    End If
    End Sub

    When I choose a name from the NameFilter combo box, nothing happens. Do you have any idea what I did wrong?

    Many thanks

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: filter / search a form (2003)

    Could you post a stripped down copy of your database? See <post#=401925>post 401925</post#> for instructions.

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: filter / search a form (2003)

    This is a reply to <post:=730,626>post 730,626</post:> which you posted in another thread.

    You should use the actual names of your fields in the code. For example, the "person" field in your table is "OMD staff name".
    However, I'd use autonumber fields to identify a staff member and a type.
    I have modified your database to reflect this, and adjusted the code.
    See attachment. Please look at the design of the tables, queries etc., and at tha VBA code.
    Attached Files Attached Files

  6. #6
    Star Lounger
    Join Date
    Nov 2001
    Location
    Texas, USA
    Posts
    93
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: filter / search a form (2003)

    Thank you, Hans. I can see how the filters work. You changed the 'type' table, adding a primary key. But that now shows only the Autoid # in the form. How can I get the form to show the text rather than the autoid?

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: filter / search a form (2003)

    Take a look at the combo boxes to see how it's done:
    - Set the column count to 2
    - Set the column widths to 0";1"
    - Set the control source to TypeID

  8. #8
    Star Lounger
    Join Date
    Nov 2001
    Location
    Texas, USA
    Posts
    93
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: filter / search a form (2003)

    Thank you, that works. However, the field in the MainTbl is now TypeID and is a numeric field, but all the entries (inlcuding a trial entry to see how it works) are text. Is that how it should be?

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: filter / search a form (2003)

    The TypeID field displays the corresponding text value (as does the StaffID field).
    To see how it's done:
    - Open Main Tbl in design view.
    - Click in the TypeID field (or in the StaffID field).
    - Activate the Lookup tab in the lower half of the window.
    - You'll see the display control, row source, column count, and column widths properties - they make the field display a combo box with the text value.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •