Results 1 to 15 of 15
  1. #1
    Join Date
    Jul 2009
    Posts
    39

    Unanswered: Simple filter on a form

    Hi , novice using Access 2002.
    Staff database
    Have a 'Staff Details' table with approx 20 fields, 1 field is entitled 'Service' and is linked to a simple lookup table , 1 field with approx 15 Service types entered.
    Each staff record in the 'Staff Details' table therefore has a single entry in the 'Service' field identifying where within the organisation they work.
    I have a simple form based on all fields in the 'Staff Details' table where managers can input info on their staff. I want to add a filter to the form so each manager can 'pick' from a list the service they manage and which then filters the records so that only staff in that service will appear on the form.
    So the filter would apply to the 'Service' field on the 'Staff Details' table.
    Seems like a simple thing but try as I might and search as I have I can't get to do it.
    Seen lots of similar ?s but I either can't understand the answer or the ? isn't 'the same 'enough.
    A problem I have is answers often lay out some code, the logic of which I can just about follow and amend with the specifics of my tables and queries but then I'm not sure where the code is entered ?
    Any help appreciated, have searched through similar post in this forum but see above.
    Thanks
    Keith

  2. #2
    Join Date
    Apr 2005
    Location
    Zagreb - Croatia
    Posts
    372

    Hello Keith!

    As I can understand you, you need something like
    "DemoManagersFilterA2000.mdb" (attachment, zip).
    Try it and adapt it in your mdb.
    Attached Files Attached Files

  3. #3
    Join Date
    Jul 2009
    Posts
    39
    Thanks unzipa ok but can't open the db file error message " Microsoft Access cannot open this file. The file is located outside your intranet or on an untrusted site. Microsoft Access will not open the file due to potential security problems. To open the file copy it to your machine or an accessible network location"
    Help !

    Sorry ignore , sorted and opened will now see if I can understand it ! Thanks
    Last edited by kedunc; 07-15-09 at 05:52.

  4. #4
    Join Date
    Apr 2005
    Location
    Zagreb - Croatia
    Posts
    372
    Copy it to your PC, and you'll can open it.

  5. #5
    Join Date
    Jul 2009
    Posts
    39

    Getting there

    Hi Mstef , getting there I think.
    I've replicated the frmServiceFilter in my database and amended the code for teh comboxo to reference the relevant table/field and then open teh data entry form. So far so good but the form comes up unfiltered

    For the combo box on my ServiceFilter form

    row source =
    SELECT [Service].[Service] FROM [Service]

    event procedure =

    Private Sub Combo0_AfterUpdate()
    DoCmd.OpenForm "Staff Details3"
    End Sub

    What am I missing please
    Thanks
    Keith

  6. #6
    Join Date
    Apr 2005
    Location
    Zagreb - Croatia
    Posts
    372
    In My "Demo" there are "frmManagerFilter" and "frmStaffDetails",
    what is "ServiceFilter" form ???

  7. #7
    Join Date
    Jul 2009
    Posts
    39
    Quote Originally Posted by MStef-ZG
    In My "Demo" there are "frmManagerFilter" and "frmStaffDetails",
    what is "ServiceFilter" form ???
    "ServiceFilter" form ( exact name = frmService Filter) in my db is replica of your "frmManagerFilter" with changes made as per my earlier post to reflect actual names of relevant table ,field and form in my db

    Thanks
    Keith

  8. #8
    Join Date
    Apr 2005
    Location
    Zagreb - Croatia
    Posts
    372
    Ok, In that case change RECORD SOURCE (in your form frmStafDetails),
    Criteria in SERVICE field.
    Look at my frmStaffDetails, "Record source" properties.

  9. #9
    Join Date
    Jul 2009
    Posts
    39
    Thanks for your patience
    On my frmStaff Details the Service field is a Combo Box ( whole form created by a wizard) the underlying Service field in the table is a lookup with the Service table. Available options on Data tab are

    Control Source = Service
    Row Source Type = Table/Query
    Row Source =SELECT Service.Service FROM Service;
    Default Value = blank

    The corresponding field on your form is a text box

    data tab shows
    Control Source = f4Service
    Default Value =[Forms]![frmManagerFilter]![Combo0]

    Is there a problem with me having a combo box fro this field and you having a text box ?
    As the field is a lookup I presume the wizard correctly chose a combo box ?

    Which element on my combo box data tab should I amend
    Thanks

    Keith

  10. #10
    Join Date
    Apr 2005
    Location
    Zagreb - Croatia
    Posts
    372
    The SERVICE field in your form put as in my form. Not combo box.
    (Combo box is in first form).
    Enabled = No, Default vaule (look at may form) .
    See you tomorrow.

  11. #11
    Join Date
    Jul 2009
    Posts
    39

    still trying

    Hi , I've followed all taht you suggest as best I can but alll the frmService Filter does is to open the main from with all records. I'm missing something.
    Anyway I created a fresh simple db like yours to exclude the possibility that I've done something fundamentally wrong in my main db.
    Same thing happens , when I pick a value in teh frmServiceFilter it opens the main form but with all records ie the filter hasn't been applied.
    The combo box on my frmServiceFilter isn't doing what your combo box does on yours!

    Have attached zip with db if you could please look over and see what I've missed I would be very grateful
    Thanks
    Keith
    Attached Files Attached Files

  12. #12
    Join Date
    Apr 2005
    Location
    Zagreb - Croatia
    Posts
    372

    Hello Kenneth!

    Here it is (attachment, zip).
    It was a mistake in "RECORD SOURCE", properties (frmStaffDetails). Look it.
    I put Required = yes in tblStaffDetails, fields "cname" and "service".
    I made a "frmStaffDetailsNew", it can be done on this way, too.
    Open tihs form and try.
    I think it is OK now.
    Salute.
    Attached Files Attached Files

  13. #13
    Join Date
    Jul 2009
    Posts
    39
    Brilliant thank you so much, I've now replicated what you have shown me on the full db I am working on. Main confusion on my part was that I was looking at the record source for the specific control on the form when you were advising me about the record source for the form itself, lesson learned !
    One last thing please, with the frmServiceFilter I can now only open the frmStaffDetails form with the records filtered , if I try to open the form directly it demands a parameter , if I leave it empty the form opens with a blank.
    How can I open the form to see all records which I will need to do from time to time?
    Thanks
    Keith ( Kenneth = name my daughter calls me because she thinks it sounds funny !)

  14. #14
    Join Date
    Apr 2005
    Location
    Zagreb - Croatia
    Posts
    372
    In "frmStaffDetails" RECORD SOURCE properties put tblStaffDetails, and open form without frmServiceFilter.

  15. #15
    Join Date
    Jul 2009
    Posts
    39
    thanks again !
    Keith

Posting Permissions

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