Using advanced filter by form not working, so what have I done wrong?

[kimberlin]

In the attached database which I'm trying to build (try being the operative word!), I seem to have hit a problem with one of my forms. When I'm in the contact details form, go to the trades tab and try to find for example all the plumbers (there's only dummy data in there so far), using the advanced filter by form routine, it will only throw one person back at me but if I go into the trade details form it will return two different records. Is there any way that I can get the first form to bring up all those of a particular trade so that I can scroll through all their details?

I know nothing about coding etc although I have bought the Alison Balter book on Access 2007, so plain and simple explanations would be most welcome!

 

[stopher]

I don't understand your question....

I assume the form you are talking about is Worker Data Entry? When I open this form I see a tab called Trades. The Trades tab doesn't have any fields or subforms in it.

However, I see that the Trade Details has been placed on the main form and is sitting on top of your tabs control. I'm guessing it is supposed to be in the Trades tab? If so, you need to cut and paste it into the tab - otherwise it will appear regardless of which tab you choose.

Anyway, back to your problem. When I click on the Advanced - Filter by Form option and enter a value in one of the fields e.g. entering "Green" in LastName, then click the toggle filter icon, then I see 3 trade details listed. Is that not what you expect?

Chris

 

[kimberlin]

Yes, the trade details are supposed to be in the trades tab. I would expect the type of result you had if I had been trying to do a filter on the name, however I wanted to do a filter on the trade id within the trades tab and it is that which is giving me the wrong answer.

 

[stopher]

Can you give me an example of what data you are typing in which field.

 

[kimberlin]

In the trades tab of the data entry form. I'm going to the "tradeid" field of the subform which I have set to show the trade name as a lookup. I'm selecting "plumber" as I know that I have already put a couple of plumbers into the workertrade table but when I tell it to apply the filter sort it is only coming up with one worker record, instead of the two that I'm expecting to come up. I'm not putting anything else into any other field on the form.

 

[stopher]

The subform filter will only filter records related to hwoever you have selected in the main form. So if you have selected Green in the main form and then you filter for plumber, then you will only see one plumber record because green only has one plumber record.

If you want to filter plumbers then I would suggest creating a new form because your current form is Worker centric whereas you want a form that is trade centric.

hth
Chris

 

[kimberlin]

Ok, thanks. I guess I should be able to create a form which is the reverse of the one I already have.