Hi , novice using Access 2002.
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 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"
Sorry ignore , sorted and opened will now see if I can understand it ! Thanks
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"
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 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
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
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.
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?
Keith ( Kenneth = name my daughter calls me because she thinks it sounds funny !)