Filter on form with subforms problems :(

[mattkbishop]

Filter on form alternatives..... new problems

Basically, I search, and the subform data doesn't show up. Or, I search on it, and it only ever shows up a certain category. I have several subforms

Any ideas?

 

[Rich]

Search where and for what, how are the subforms linked?

 

[mattkbishop]

Well the main form as say ID, Name, Address 1 etc etc

And it can have many things linked in the sub forms. All linked in tables - by ID to ID.

So we got MainForm1. And then Sub1 with ID (linked) and category.And maybe Sub2 with ID and age ranges. And a few others.

Basically I click filter by form - and I enter what I want to search on.

So say I type "Town A" in the town field, aiming to bring up all the records in Town A. But, I want those in town A , with say a certain category (in the subform from a linked table), and a two certain age ranges (in the subform from a linked table).

But it only brings up those records in town A - regardless of what I wanted to search on in the subforms

 

[mattkbishop]

Anyone?

 

[WayneRyan]

matt,

Your description is very vague.

If you could post a sample db then maybe we can
look at it and help.

Wayne

 

[mattkbishop]

OK mate - how do I do that? It's quite large.

On closer inspection this morning it appears that just the subforms aren't searching - linked from ID in the main form to an ID in the subform. I will post one though - but how?

Matt

 

[mattkbishop]

Ok this is the database - zipped up. I’ve taken out some macros and reports to make the file smaller so they won’t work.

When it opens, click OPEN DATABASE to get to the main form. Then click the search button. This opens a new form to allow manipulation to the form since Filter on Form locks it up for search condition entry. Four buttons: The first clears any previous search info, the second runs the search, the third cancels the pop up and deselects the filtered form, and the final one, removes the filter and closes the pop up.

Now, say we want special needs provision in Bedford. Enter BEDFORD in Postal Town. Now click the Search Info tab, and select Special Needs from category. See - it only brings up those provisions in Bedford - and only visually displays in the category subform the category "Special Needs" for those records that have it.

Obviously you can see the problem when staff are looking for special needs or activities provision in Bedford.

(and it's made worse by the fact that you can use the OR function in filter on form - which we need)

I would appreciate any help as this is killing my mind. Thanks.

 

[mattkbishop]

Anyone?

 

[mattkbishop]

Bump as this is ruining my mind.

 

[WayneRyan]

Matt,

I'm all wrapped up in things at work. Will try to look at it in a
few hours.

Wayne

 

[mattkbishop]

Matt,

I'm all wrapped up in things at work. Will try to look at it in a
few hours.

Wayne

Thanks Wayne

 

[mattkbishop]

Any luck mate? Anyone?

 

[mattkbishop]

Another bump.... please anyone?

 

[neileg]

I've had a look.

I have to say it's not obvious what the search is doing. Using filters does my head in. I much prefer using queries.

Your Categories filter is being applied after the Town filter so it's behaving the way I would expect, since this is in a subform. If you had based this on a query you would have selected records based on both criteria.

I'll have another look when I have a minute but I don't have any insights, since this is a million miles away from how I would have designed it.

 

[WayneRyan]

Matt,

I agree with Neil, I didn't readily see how to operate your
search window.

What I did was base your main form on a new query and
added a simple little search tab to your form.

Basically, it lets you query on one of the three fields. If you add
a new search field to the form and query you can extend it.

It might give you something to experiment with.

I guess it could also have automatically set focus back to the
main page too.

By the way, nice looking application!

Wayne

 

[mattkbishop]

Matt,

I agree with Neil, I didn't readily see how to operate your
search window.

What I did was base your main form on a new query and
added a simple little search tab to your form.

Basically, it lets you query on one of the three fields. If you add
a new search field to the form and query you can extend it.

It might give you something to experiment with.

I guess it could also have automatically set focus back to the
main page too.

By the way, nice looking application!

Wayne

Hey Wayne that's brilliant!!!

But I have some questions.....

I don't suppose you could explain in simple steps exactly how you did that? If I could get that for each searchable field that could work, then a button directing it towards the first tab that would be good.

Also, would I be able to set it up so I can select multiple categories and age ranges like that? How?

And also, (sorry), why is there only some records showing (107) - and many of those that are, are duplicated?


I thought I had this whole thing sussed, until I discovered that filter problem which has ruined things, and I have to have it finished by Friday afternoon, or Monday morning at the VERY latest.

I figured if I could get the filters to only show those records that have something in the subforms showing after applying the filter, then it would work. But I don't know how so I may have to use your way which I have no clue about.

Any help would be so greatly appreciated.

 

[neileg]

Wayne's query returns a different number of records because:

1) It returns a record for each valid entry that the organisation has in tblCategories and tblAgeList, so if the org has two entries in each it will appear 4 times in the query.

2) Your data is sparse in some areas. Wayne's query searches for any entry if you leave the search box blank, but not a null. Many of your SettingVillageTown and ContactName fields are empty.

You can probably change the search to give you what you want by taking out the entries in the query and the boxes on the form for these two fields and substituting the fields such as category and age range.

Sorry I can't post a working version of this, but I use Access 2000 and I get a compile error when I convert your database so I can't resave as Access 97.

 

[mattkbishop]

"1) It returns a record for each valid entry that the organisation has in tblCategories and tblAgeList, so if the org has two entries in each it will appear 4 times in the query."

I see...

But this has really got me confused now. I just need to be able to search on that info, plus the info in the subtables for each record, and not have duplicates like that appear.

And quickish too.... I thought I was so close.

 

[mattkbishop]

Just to clear it up for anyone who still doesn't get me and hasn't downloaded...

Basically I have a database (i can send to people if they want) that stores organisation information. One main table, with a Contact ID (making it unique), and other things like Town etc.

Now, there are 4 or so tables linked by ContactID. 2 of which are important.

Categories: ContactID, Category [one organisation could be in many categories]

Age Range: ContactID, Age Range [one organisation could have many age ranges]

Then I played around wit autoforming those, etc to get them as datasheet subforms, bound to ContactID.

So we go to record 1, we see it's info, and only the categories and age ranges for record one. Fine.

Now, I need for stupid people to be able to search on this. So, they can't design their own queries each time.

I figured, use filter on form.

So I have a button, it runs the filter on form, opens a new form with 4 buttons - one to clear any info so we can filter from scratch, one to apply the filter, and two to basically get rid of the filter (to avoid debug errors, complicated, but not important now).

So I search on a town. Say "Bedford". Fine it works. But, when I go to filter on "Bedford", and then in the category subform, type "Special Needs", it still only shows all the Bedford records, but the category subform is blank, except for those records that are in the "Special Needs" category - it just shows "Special Needs".

So the users have to scroll through all the records in the "Bedford" filter to find the special needs one. Not only does this defeat the whole object of searching, but then I can't run a report to print all the info on them (since ALL Bedford records are shown, not just the Special Needs, Bedford ones.)

So, my question....

How do I make it, so after running the filter (the control in which, is in a button - code editor[on click]) it ONLY shows those records which I've filtered from the main table (which is does), but also those with anything in the subforms which I am searching on?

Way I see it - either I find a way to only display te records with anything showing in the subforms, or find a new way (easy for the user) of searching, bearing in mind I am perplexed with Access at the best of times, and I am now on a tighter deadline than you can imagine (I thought I had it all sussed )

Any help, would be most awesome.

 

[neileg]

Matt I explained how to do it but you don't understand, I think.

Use Wayne's example. Change the boxes to include your category

Ammend the query to take out the criteria in the boxes you removed. Add the criteria to Category.

This will, providing you fill in the search boxes, Orgs in say Bedford with special needs capability.